SQL*Plus - Spool into multiple files

Question

I've got the next spool that stores the DDL of the user_tables into a file:

set pagesize 0
set long 90000
spool C:\Users\personal\Desktop\MAIN_USR\test.txt
select DBMS_METADATA.GET_DDL('TABLE',table_name,'MAIN_USR')
FROM user_tables ut;
spool off
exit

It returns the DDL of all user_tables into a single file, but I need it to be a little more dynamic and return them in separate files with the file name of their respective table. Something like this:

set pagesize 0
set long 90000
FOR tab_nam IN (SELECT table_name FROM user_tables) LOOP
   spool C:\Users\personal\Desktop\MAIN_USR\test.txt
      select DBMS_METADATA.GET_DDL('TABLE',table_name,'MAIN_USR')
      FROM user_tables ut;
   spool off
END LOOP;
exit

I know the one above won't work, but it's kind of an idea of what I want to do.

I appreciate any kind of help

Use a better tool that allow you more control. Python cx_Oracle comes to mind. — Christopher Jones, Mar 20 '20 at 20:58

Max · Answer 1 · 2020-03-20T21:11:11.213

0

You need to read USER_TABLES for creating N calls to DBMS_METADATA.GET_DDL each of which has its own spooled file. Spool everything to a file named out.sql and run it after spooling it off

set pagesize 0
set long 90000

SET TERMOUT OFF
spool out.sql

select 'spool C:\Users\personal\Desktop\MAIN_USR\'||REPLACE(table_name, '$', '_')||'.txt'||chr(13)||chr(10)||
'SELECT DBMS_METADATA.GET_DDL(''TABLE'','''||table_name||''',''MAIN_USR'') FROM DUAL;'||chr(13)||chr(10)||
'spool off' as cmd
FROM user_tables ut;

spool off

@OUT.SQL

exit

edited Mar 20 '20 at 21:11

answered Mar 20 '20 at 20:55

Max

2,508
3
26
44

It works, but there are some tables that do not return their metadata in the file, unfortunately most of them – tefached616 Mar 24 '20 at 15:53
Can you check the "spool off" command is always present after each call to DBMS_METADATA.GET_DDL in the OUT.SQl file ? – Max Mar 24 '20 at 17:29
It is. I see that the problem is when the spool is assigning a name to each file, since the names appear incomplete – tefached616 Mar 24 '20 at 21:34

score 0 · Answer 2 · answered Mar 21 '20 at 21:07

Using python's cx_Oracle module which enables access to Oracle Database might be elegant way for your case :

import cx_Oracle
con = cx_Oracle.connect('uname/pwd@host:port/service_name')
cur = con.cursor()

def OutputTypeHandler(cursor, name, defaultType, size, precision, scale):
    if defaultType == cx_Oracle.CLOB:
        return cursor.var(cx_Oracle.LONG_STRING, arraysize = cursor.arraysize)

cur.outputtypehandler = OutputTypeHandler

cur.execute("select table_name from user_tables order by 1")
rec = cur.fetchall()

for r in rec:
    cur.execute("select dbms_metadata.get_ddl('TABLE',:tableName) from dual",tableName=r[0])
    ddl, = cur.fetchone()

    file = r'C:\\Users\\personal\\Desktop\\MAIN_USR\\'+r[0]+'.txt' 

    with open(file,"w") as f:
            f.write(ddl)
    f.close()

where the table names of the whole schema are determined through the first cur.execute, and their creation DDL are done in the second, and files are created with respective table names at the last step. Important thing to consider is related to use of OutputTypeHandler is because of getting rid of processing the CLOB result steming from Dbms_Metadata.Get_Ddl function. The compiler wouldn't want to get a CLOB value during the creation of the files, or DDL would be so long that will exceed the length 4000 chars which would prevent to_char conversion with raising error without using OutputTypeHandler.

SQL*Plus - Spool into multiple files

2 Answers2