JavaScript within SQLcl

In recent versions of SQLcl one can use JavaScript to ease some SQL coding. I used the following script to create a table with 100 NUMBER columns, and then to populate it with 250,000 records for a test which I wished to perform.

The reason for using JavaScript here, was that a simple loop could handle the generation of the 100 field names quicker than I thought I could do it using awk from vi.

Note if you want to run the script below as is, you will first need to create the table T1000000, which can be obtained here.

DROP TABLE many_column_table PURGE;

script
  var table_ddl = "CREATE TABLE many_column_table(";
  for (var i=1; i<100; i++) table_ddl += "num"+i + " NUMBER,";
  table_ddl += "num100 NUMBER)";
  sqlcl.setStmt(table_ddl);
  sqlcl.run();

  var table_dml = "INSERT INTO many_column_table SELECT ";
  for (var i=1; i<100; i++) table_dml += "num,";
  table_dml += "num FROM t1000000 WHERE rownum <= 250000";
  sqlcl.setStmt(table_dml);
  sqlcl.run();
/
COMMIT;

exec dbms_stats.gather_table_stats(user, 'many_column_table')

set sqlformat ansiconsole
SELECT table_name, num_rows, blocks
FROM   tabs
WHERE  table_name = UPPER('many_column_table');

The output from the above script is shown below. Note that as in Oracle 12 the TABLE_NAME column has become a VARCHAR2(128), to set a tidy output one needs to define a sensible display width using the COLUMN command, or using set sqlformat ansiconsole allows SQLcl to pick a column width which fits the data.

SQL> DROP TABLE many_column_table PURGE;

Table MANY_COLUMN_TABLE dropped.

SQL> 
SQL> script
  2    var table_ddl = "CREATE TABLE many_column_table(";
  3    for (var i=1; i<100; i++) table_ddl += "num"+i + " NUMBER,";
  4    table_ddl += "num100 NUMBER)";
  5    sqlcl.setStmt(table_ddl);
  6    sqlcl.run();
  7  
  8    var table_dml = "INSERT INTO many_column_table SELECT ";
  9    for (var i=1; i<100; i++) table_dml += "num,";
 10    table_dml += "num FROM t1000000 WHERE rownum <= 250000";
 11    sqlcl.setStmt(table_dml);
 12    sqlcl.run();
 13  /

Table MANY_COLUMN_TABLE created.


250,000 rows inserted.

SQL> COMMIT;

Commit complete.

SQL> 
SQL> exec dbms_stats.gather_table_stats(user, 'many_column_table')

PL/SQL procedure successfully completed.

SQL> 
SQL> set sqlformat ansiconsole
SQL> SELECT table_name, num_rows, blocks
  2  FROM   tabs
  3  WHERE  table_name = UPPER('many_column_table');
TABLE_NAME         NUM_ROWS  BLOCKS  
MANY_COLUMN_TABLE  250000    18257   


SQL>