SQL ORACLE 如何使用带有动态线轴位置的线轴
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1246360/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
ORACLE How to use spool with dynamic spool location
提问by Beta033
Ok, so i'm a complete newb with oracle. Now that that's out of the way;
好的，所以我是一个完整的 oracle 新手。现在那已经不碍事了；
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.
我想你可以了解我在下面要做什么。对于找到的每个存储过程，将 DDL 输出到带有它的名称的文件名。
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.
问题是我不知道如何让假脱机目标获取由光标设置的 FileName 的值。
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
我得到了一个结果，我似乎无法动态地改变那个 &colname
Thanks for your help.
谢谢你的帮助。
采纳答案by dpbradley
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.
SPOOL 是 SQL Plus 指令，您不能将它混合到 PL/SQL 匿名块中。如果您打算纯粹在 SQLPlus 中执行此操作，我认为一般的想法是分两次处理，即使用第一个脚本动态生成假脱机文件名引用到实际进行 dbms_metadata 调用的第二个脚本中。
[Edit]
[编辑]
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
回答by sanc
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
27JUN2011_1727.lst
27JUN2011_1727.lst
You can even specify the file extension if you need to (eg .txt). Just create another variable.
如果需要，您甚至可以指定文件扩展名（例如 .txt）。只需创建另一个变量。
来源：http: //oracle.ittoolbox.com/groups/technical-functional/oracle-apps-l/variable-file-name-with-spool-1508529
回答by David
I think UTL_FILE would be much better suited for your needs here. SPOOL is really supposed to be a command to instruct sqlplus to write the output to a file. Typically I use this for stuff like ... "hey DBA run my script and send me the output".
我认为 UTL_FILE 会更适合您的需求。SPOOL 实际上应该是一个命令，用于指示 sqlplus 将输出写入文件。通常我将它用于诸如“嘿，DBA 运行我的脚本并将输出发送给我”之类的东西。
First you need to define a directory. Syntax is easy:
首先你需要定义一个目录。语法很简单：
CREATE DIRECTORY SQLOUTPUT AS 'c:\temp\';
Now you can use that 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;
