Oracle SQL script to build SQL script using dbms_output

Question

This may be a stupid idea. I am trying to write a SQL*Plus .sql script that I can call from a Windows batch file that itself generates a new .sql script using dbms_output.put_line. This is what I have so far for my script:

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;

However, when I do this, my new_script.sql just says "PL/SQL procedure successfully completed." Any ideas how to make the dbms_out.put_line actually display their message?

And I'm not actually doing this to build insert statements - those are just a simple sample showing the gist of what I'm trying to do.

Answer 1

SPOOL sends the results to a file; but it doesn't ensure that results are printed. To enable the printing to stdout; use the SERVEROUTPUT command.

set serveroutput on

This is generally a poor way of going about things; I recognise that you said this wasn't what you're actually doing but SQL is a set based language. Do things in bulk if at all possible.

Answer 2

1. You have to connect first and then use SET command. So your three commands should be in

   this order:

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

   Otherwise, serveroutput parameter will be set to its default

   value, which is OFF and that the reason why dbms_output.put_line()

   hasn't been working as expected.

2. You should execute dbms_output.put_line(s) inside the FOR loop:

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

   Moreover it's redundant to spool BEGIN .. END block. The DML statements

   (bunch of INSERT INTO statements in this case) terminated by semicolon will be

   executed just fine without being included in the BEGIN END block.

3. As you run your script using *.bat file, It probably will be useful to use the

   following commands:

   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. Your approach could probably be boiled down to a simple SELECT statement:

   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;