Creating a SQL * Plus script using SQL * Plus

Question

I want to generate many SQL * Plus scripts by querying a data dictionary, but I run into some problems and suspect that I am 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 in 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 is -after DUAL;? I do not want this in the generated script.

, , - CHR() ; , , , , ]'||CHR(59)||CHR(10)||q'[, , .

( SQL * Plus Release 11.2.0.1.0 Production, 11gR2.)

Answer 1

, SQL * Plus ; . , , ( SQL * Plus), .

, ( , !), SET SQLTERMINATOR off. , SQL * Plus, , /, ; .

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.

, - PL/SQL dbms_output , , , .

Answer 2

script , UTL_FILE SQL * Plus. , , , , SQL-, .

Answer 3

getddl dbms_metada my: http://github.com/xtender/XT_SVN

Answer 4

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 (;).

Answer 5

In the future, for themselves, instead of messing around with SET SQLTERMINATOR offusing sql plus , use the below below, so you need not worry about any special characters within the symbol sql string literal body.

BEGIN
INSERT INTO SOME_TABLE (q'[ 

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

]');
END;
/