ORACLE如何将假脱机与动态假脱机位置一起使用 [英] ORACLE How to use spool with dynamic spool location

问题描述

好的，所以我对oracle完全满意.现在，这已经不成问题了；

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

我想您可以从下面了解我要做的事情. 对于找到的每个存储过程，将DDL输出到具有其名称的文件名.

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.

问题是我无法弄清楚如何使假脱机目标获取光标所设置的FileName的值.

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

我得到一个结果，我似乎无法动态地更改& colname

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

感谢您的帮助.

推荐答案

SPOOL是SQL Plus指令，您不能将其混入PL/SQL匿名块中.如果您仅使用SQL Plus来执行此操作，我认为总体思路是分两步进行处理，即使用第一个脚本动态地将假脱机文件名引用生成到第二个脚本中，而第二个脚本实际上使dbms_metadata调用.

SPOOL is a SQLPlus directive and you can't mix it into the PL/SQL anonymous block. If you're going to do this purely in SQLPlus, I think the general idea would be to process in two passes, i.e. use a first script that dynamically generates the spool filename references into a second script that actually makes the dbms_metadata call.

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

这篇关于ORACLE如何将假脱机与动态假脱机位置一起使用的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！