Script to spool all tables in a schema to xml format in Oracle 12c

oracle-12c

I've written the following script, spooltables.sql, which I'm calling from SQLcl as $> @spooltables.sql

The aim is to loop through all tables in a given schema and output them to separate XML files. 

set term off;
set feed off;
set sqlformat xml;
begin
for table_list in (select table_name from dba_tables WHERE owner='THIS_SCHEMA') loop

   dbms_output.put_line('spool /exports/'||table_list.table_name||'.xml');
   dbms_output.put_line('select /*+ parallel */* from '||table_list.table_name||';');

end loop;
end;
spool off;

This doesn't execute, it just prompts for a new line in the SQLcl terminal.

I'm not really experienced writing these Oracle loops, can anyone tell me what I need to do to get it working as required?

So I've tried the following based on the below answer:

    spool spooltables.sql 
    set term off; 
    set feed off; 
    set sqlformat xml; 
    begin for table_list in (select table_name from dba_tables WHERE owner='THIS_SCHEMA') loop 
    dbms_output.put_line('spool /exports/'||table_list.table_name||'.xml');
dbms_output.put_line('select /*+ parallel / from '||table_list.table_name||';'); 
    end loop; 
    end; 
    / 
    spool off; 

    $> @spooltables.sql

The sql file executes but seems to execute immediately without doing anything – the file also gets emptied.

Best Answer

I don't know SQLcl, but in sqlplus a pl/sql block must be terminated by a line

/

I hope the following will work

spool sqltables.sql
set echo off
set feed off;
set serveroutput on
prompt set termout off
prompt set sqlformat xml
begin
for table_list in (select table_name from dba_tables WHERE owner='MYUSER') loop

   dbms_output.put_line('spool ./'||table_list.table_name||'.xml');
   dbms_output.put_line('select /*+ parallel */* from '||table_list.table_name||';');
   dbms_output.put_line('spool off');

end loop;
end;
/
prompt exit
spool off;
@sqltables.sql

so I added SET SERVEROUTPUT ON. This is necessary in sqlplus, otherwise you will not see the dbms_output output. Also I added the PROMPT statements that write commands to the sqltables.sql file.

Related Question