Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
763 views
in Technique[技术] by (71.8m points)

sql - ORACLE How to use spool with dynamic spool location

Ok, so i'm a complete newb with oracle. Now that that's out of the way;

I think you can get an understand of what i'm trying to do below. For each stored procedure found, output the DDL to a filename with it's name.

The problem is i can't figure out how to get the spool target to pick up the value of FileName which is being 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 as to where i'm going wrong? and if anyone has an example of this i would appreciate it greatly.

I feel i must be dancing around it because if i create a column initially then

spool &ColName

i get a result, i just can't seem to dynmically change that &colname

Thanks for your help.

See Question&Answers more detail:os

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Reply

0 votes
by (71.8m points)

I found a better solution without the need to declare/begin/end code blocks or query statements.

A sample spool filename with the 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 the file extension if you need to (eg .txt). Just create another variable.

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


与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
OGeek|极客中国-欢迎来到极客的世界,一个免费开放的程序员编程交流平台!开放,进步,分享!让技术改变生活,让极客改变未来! Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

...