0

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.

0

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.

  • Thanks, so now I'm running the following: ` 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 procedure seems to just run and exit and doesn't do anything, it also clears the spooltables.sql file and empties in leaving it blank... – Molenpad Jul 25 at 9:38
  • Updated my original question, thanks – Molenpad Jul 25 at 11:00
  • Thanks. Still not working for me. I'm a bit confused. I don't want to spool to spooltables.xml. I want to spool each table in the schema to a seperate XML file – Molenpad Jul 26 at 9:11
  • sorry, id didn't read your question and your code carefully. I sees now, what you want to achieve. You generate the spooltables.sql script by your sql commands. then you call the generated spooltables.sql script to get the table data in xml format, one file per table. I will update my post as soon as possible – miracle173 Jul 26 at 9:39
  • for analysis of your problem you should remove the "SET TERM OFF" so that you can see the output. you also should restrict your for loop to two or three tables and the the data you select per table to 3 rows (add the clause "where rownum<=3") or you can create a test schema with 3 tables, each containing 3 rows only. Then you can show us the output and we can analyze the problem – miracle173 Jul 26 at 9:44

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.