使用 dbms_output 构建 SQL 脚本的 Oracle SQL 脚本

Question

这可能是个愚蠢的想法。我正在尝试编写一个 SQL*Plus .sql 脚本，我可以从 Windows 批处理文件中调用该脚本，该文件本身使用 dbms_output.put_line 生成一个新的 .sql 脚本。到目前为止，这是我的脚本:

set echo off;
set serverout on;

conn user@pass;

spool E:\new_script.sql

DECLARE

  CURSOR c_groups IS
    SELECT * FROM table;

  s varchar2(4000);

BEGIN

  dbms_output.put_line('BEGIN');

  FOR x IN c_groups LOOP
    s := 'INSERT INTO TABLE blah VALUES ('''||x.name||''','||x.id||');';
  END LOOP;

  dbms_output.put_line(s);

  dbms_output.put_line('COMMIT;');

  dbms_output.put_line('END;');
  dbms_output.put_line('/');

END;
/

spool off;
exit;

但是，当我执行此操作时，我的 new_script.sql 只是说 “PL/SQL 过程已成功完成。” 关于如何生成 dbms_out 的任何想法。 put_line 实际显示他们的消息？

实际上我这样做并不是为了构建插入语句 - 这些只是一个简单的示例，展示了我正在尝试做的事情的要点。

Answer 1

1. 您必须先连接然后使用SET 命令。所以你的三个命令应该在

   这个订单:

   conn user@pass;
   set echo off;
   set serverout on;
   

   否则，serveroutput 参数将被设置为其默认值

   值，即 OFF 也是 dbms_output.put_line()

   的原因

   没有按预期工作。

2. 您应该在 FOR 循环中执行 dbms_output.put_line(s):

   FOR x IN c_groups LOOP
      s := 'INSERT INTO TABLE blah VALUES ('''||x.name||''','||x.id||');';
      dbms_output.put_line(s);
   END LOOP;
   

   此外，spool BEGIN .. END block 是多余的。 DML 语句

   (在这种情况下是一堆 INSERT INTO 语句)以分号结尾将是

   没有包含在 BEGIN END block 中，执行得很好。

3. 当您使用 *.bat 文件运行脚本时，使用

   可能会有用

   以下命令:

   set feedback off;  -- To not spool messages like 
                      -- PL/SQL procedure successfully completed
   
   set termout off;   -- to suppress the output from a command line.
   

4. 您的方法可能可以归结为一个简单的 SELECT 语句:

   conn user@pass;
   
   set echo off;
   set serverout on;
   set feedback off;
   set termout off;
   set heading off;
   
   spool E:\new_script.sql
   
   SELECT 'INSERT INTO TABLE blah VALUES ('''||name||'''','||to_char(id)||');'
    FROM table;
   
   spool off;
   
   exit;