Creating a CSV file per Loop | PLSQL Oracle SQL Developer

Question

Updated

Rad-folks!

TL;DR

Need working code to loop through an existing list of all my tables that will create CSV files selecting the top 100 rows of each table. There are variables set up to capture the table names which will be used to dynamically, call the table for the select and name the file. Must be done through PLSQL and SQLDeveloper. Do u kno da wae?

Here is the situation:

• Must gather all tables (base tables non temporary) and row count (row count>0)
• Create a loop to select(*) top 100 rows from the list of tables
• Take the result of the query and place it on a CSV file

Problems:

• Declaring the Variables
• Using Begin and End
• Using a dynamic name to produce unique CSV files

Here is my Code:

CREATE GLOBAL TEMPORARY TABLE NameRow (nom VARCHAR2(100), rowc INTEGER)
  on commit delete ROWS;
  insert into NameRow(nom, rowc) select table_name, num_rows from user_tables where temporary = 'N' and num_rows > 0;
    --select * from namerow;
    --select count(nom) from namerow;
    --drop table namerow;
    --no need for the row count > 0 because that was already done above
 declare
  counter number := 0;
  totalrecords number := 0;
  nmbre varchar2(100);
 BEGIN

    Select count(nom) into totalrecords from namerow;
    WHILE counter <= totalrecords LOOP
      select nom into nmbre from NameRow where rownum =1;
    SET SPOOL ON
    SPOOL c:\Users\l.r.enchaustegui\Documents\reporepo\||nmbre||.csv
    select /*csv*/ * from HR.nmbre;
    SET SPOOL OFF 
        delete from namerow where rownum=1;
        counter := counter + 1;
    End loop;
 END;

Code Explained:

• A temporary table is created with a varchare and integer column to record all tables in DB with their RowCount
• Table Names inserted in the temp table must be non temporary and have more than 0 row count

Next segment

• Declaring 3 variables: 2 integers and a varchar
• 2 Integers: 1 is a counter for the loop. 1 records the total rows in the Temp table which will serve as the max iterations in the loop.
• Varchar: Nmbre will record the name of the 1st table name within the temp table.

Next Segment

• Spool into the following path, using the variable Nmbre to dynamically name the CSV file
• Spool Query using the variable Nmbre to dynamically select table
• Delete 1st row from temp table [serves to rotate into the next tablename]
• Spool Off
• Loop
• End loop; End;

Where am I wrong? Also, I am getting this error:

Bonus Round: Constrained to SQL Developer

Answer 1

Here's an option using SQLcl. SQLcl is the guts of SQLDEV but wrappered into a cmd line. Also being java the scripting abilities of core java is available. This is using JavaScript as the scripting engine.

We have some doc and lots of example of how this all works out on github here: https://github.com/oracle/oracle-db-tools/tree/master/sqlcl

script
 var binds = {};

// get complete list of tables
 var tables = util.executeReturnList("select table_name from user_tables", binds);

 for (i = 0; i < tables.length; i++) {
   // get count of rows
    var rows = util.executeReturnOneCol('select count(1)  from ' +  tables[i].TABLE_NAME );
    ctx.write( tables[i].TABLE_NAME + ">>"  + rows + " \n" ) ;

    // if more than zero dump to a csv file
    if ( rows > 0 ){
        sqlcl.setStmt("set sqlformat csv ")
        sqlcl.run();
        sqlcl.setStmt("spool " + tables[i].TABLE_NAME + ".csv")
        sqlcl.run();

        sqlcl.setStmt("select * from  " + tables[i].TABLE_NAME )
        sqlcl.run();
        sqlcl.setStmt("spool off")
        sqlcl.run();

    }
 }
/