3

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

Jeffrey Kemp
  • 59,135
  • 14
  • 106
  • 158

5 Answers5

2

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.

Alex Poole
  • 183,384
  • 11
  • 179
  • 318
  • dbms_output! and I use it every day... but thanks for the `SQLTERMINATOR` option too. – Jeffrey Kemp Nov 10 '10 at 11:28
  • (btw this behaviour still occurs when I run it from a script.) – Jeffrey Kemp Nov 10 '10 at 11:29
  • Ah, yes it would do of course, SQL*Plus will be interpreting it the same as if you'd typed it. It's OK if you `edit` from within SQL*Plus, I guess that bypasses SQL*Net trying to interpret it. – Alex Poole Nov 10 '10 at 11:40
1

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.

Adam Hawkes
  • 7,218
  • 30
  • 57
  • 1
    I thought about that, but then I'd need access to a directory on the server, then I'd need to either get the file via FTP or run sql*plus via a terminal session. I'd rather live with the CHR() concatenations :) - what I'm doing now spools to a local file which is more handy. – Jeffrey Kemp Nov 10 '10 at 05:22
  • I **COMPLETELY** understand. Getting that kind of access in certain environment isn't always easy. – Adam Hawkes Nov 10 '10 at 13:30
0

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;
/
Chad
  • 2,938
  • 3
  • 27
  • 38
0

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

Sayan Malakshinov
  • 8,492
  • 1
  • 19
  • 27
  • No. `getddl` is only suitable for generating DDL that Oracle knows about. I want to generate my own scripts - complete with SET commands, PROMPTs, queries, DML, etc. – Jeffrey Kemp Nov 10 '10 at 04:53
0

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 (;).
Sayan Malakshinov
  • 8,492
  • 1
  • 19
  • 27