Generating SQL*Plus script using SQL*Plus

I want to generate a whole lot of SQL*Plus scripts by querying the data dictionary, but I'm hitting some problems and suspect I'm missing something obvious.

For example, when I execute the following in SQL*Plus I get ORA-01756: quoted string not properly terminated:

SQL> SPOOL myscript.sql
SQL> SELECT q'[SPOOL log
  2  SELECT COUNT(*) FROM DUAL;
ERROR:
ORA-01756: quoted string not properly terminated

I tried using the line continuation character to avoid this error, but it puts the continuation character into the output:

SQL> SELECT q'[SPOOL log
  2  开发者_开发百科SELECT COUNT(*) FROM DUAL; -
  3  PROMPT Done.
  4  ]' FROM DUAL;
SPOOL log
SELECT COUNT(*) FROM DUAL; -
PROMPT Done.

Notice how the output has the - after DUAL;? I don't want that in the generated script.

One way I can get around this is to concatenate a lot of CHR() function calls to generate semicolons and linefeeds; but I hope I don't have to because these scripts being generated are very long, and having bits like ]'||CHR(59)||CHR(10)||q'[ scattered throughout the code makes it look very ugly and a pain to troubleshoot.

(I'm using SQL*Plus Release 11.2.0.1.0 Production, connecting to an 11gR2 instance.)

The problem is that SQL*Plus is interpreting your first ; as the terminator for the command. You may have noticed that if you write your commands to a text file and execute that (or edit it in a text editor from with SQL*Plus) it works.

To make it work with live typing, if you really want to do that (seems unlikely if they're going to be very long!), you can turn off the automatic detection of the terminator with SET SQLTERMINATOR off. Note that you'll have to tell SQL*Plus that you're done and that it should execute with the / instruction as the second ; is ignored as well.

SQL> SPOOL myscript.sql
SQL> SET SQLTERMINATOR off
SQL> SELECT q'[SPOOL log
  2  SELECT COUNT(*) FROM DUAL;
  3  PROMPT Done.
  4  ]' FROM DUAL
  5  /
SPOOL log
SELECT COUNT(*) FROM DUAL;
PROMPT Done.

If you're building these from the data dictionary, another option is to use PL/SQL to do the queries and manipulations and dbms_output to produce the output you're gong to spool, as long as the final file size won't exceed the buffer limits.

When I want to create a script from within the DB I tend to prefer writing a file using the UTL_FILE package instead of spooling the output of SQL*Plus. It isn't exactly what you want, but I find the control to be far less troublesome than trying to write sql scripts that format properly.

You can use getddl in dbms_metada package or mine package: http://github.com/xtender/XT_SVN

You need to see http://download.oracle.com/docs/cd/A97630_01/server.920/a90842/ch13.htm

SET CMDS[EP] {;|c|ON|OFF}

Sets the non-alphanumeric character used to separate multiple SQL*Plus commands entered on one line to c. ON or OFF controls whether you can enter multiple commands on a line. ON automatically sets the command separator character to a semicolon (;).

For future reference for myself, instead of messing around with SET SQLTERMINATOR off when using sql plus use the following bellow so you don't need to worry about the any special sql terminator character inside the string literal body.

BEGIN
INSERT INTO SOME_TABLE (q'[ 

Now;
You;
Can '
Do "'"';' ;;;
any character? *

]');
END;
/