ORACLE How to use a coil with a dynamic coil location

Question

Ok, so I'm a complete newb with oracle. Now that it’s not,

I think you can understand what I'm trying to do below. For each stored procedure found, output the DDL in the name of the file with its name.

The problem is that I cannot figure out how to get the spool target to get the FileName value set by the cursor.

DECLARE objName varchar2(50); FileName varchar2(50); cursor curProcs is select OBJECT_NAME into objName FROM ALL_PROCEDURES WHERE OWNER = 'AMS' ORDER BY OBJECT_NAME; -- get all procs in db BEGIN open curProcs; if curProcs%ISOPEN THEN LOOP FETCH curProcs into objName; EXIT WHEN curProcs%NOTFOUND; FileName := 'C:\ ' || objName || '.PRC.SQL'; spool FileName; --BREAKS DBMS_METADATA.GET_DDL('PROCEDURE',objName); spool off; END LOOP; END IF; END; 

Any ideas on where I am going wrong? and if anyone has an example of this, I would really appreciate it.

I feel like I have to dance around, because if I create the column initially then

 spool &ColName 

I get the result, I just can’t change the dynamic change of this & colname

Thank you for your help.

Answer 1

SPOOL is a SQLPlus directive, and you cannot mix it with an anonymous PL / SQL block. If you are going to do this exclusively in SQLPlus, I think the general idea is to handle two passes, i.e. Use the first script that dynamically generates links to spool names on the second script, which actually makes dbms_metadata call.

[change]

 This should be close to what you need - maybe a line termination problem, depending on your platform: set pagesize 0 set linesize 300 spool wrapper.sql select 'spool '||object_name||'.sql'||chr(10)|| 'begin dbms_metadata.get_ddl('||chr(39)||object_type||chr(39)||','||chr(39)||object_name||chr(39)||')'||' end;'||chr(10)|| '/'||chr(10)|| 'spool off' from user_objects where object_type = 'PROCEDURE' ; spool off 

Answer 2

I found a better solution without having to declare / start / end code blocks or query requests.

An example spool file name with date and time can be achieved by:

 sql> column dt new_value _dt sql> select to_char(sysdate,'ddMONyyyy_hh24mi') dt from dual; sql> spool &_dt 

My file name:

27JUN2011_1727.lst

You can even specify a file extension if you need (e.g. .txt). Just create another variable.

Source: http://oracle.ittoolbox.com/groups/technical-functional/oracle-apps-l/variable-file-name-with-spool-1508529

Answer 3

I think UTL_FILE will be much better suited to your needs here. SPOOL really needs to be a command to instruct sqlplus to write output to a file. I usually use this for things like ... "Hey DBA, run my script and send me the output."

First you need to define a directory. The syntax is simple:

 CREATE DIRECTORY SQLOUTPUT AS 'c:\temp\'; 

Now you can use this in your code:

 DECLARE -- Get all procedure from All_Objects -- You could expand this to pass in the object_type you are looking for CURSOR csr IS SELECT object_type , object_name FROM All_Objects WHERE object_type = 'PROCEDURE' AND owner = 'AMS'; -- define a file handler type outputfile UTL_FILE.file_type; BEGIN FOR c IN csr LOOP -- open your file using the procedure name from the cursor outputfile := UTL_FILE.fopen('SQLOUTPUT',c.object_name||'.prc.sql','W'); -- output the metadata results just like DBMS_OUTPUT except to a file UTL_FILE.put_line(outputfile, DBMS_METADATA.get_ddl(c.object_type, c.object_name)); -- make sure to close the file when you are done. UTL_FILE.fclose(outputfile); END LOOP; -- go home early today ... END;