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 exe

Solution 1:

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
SELECTCOUNT(*) 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.

Solution 2:

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.

Solution 3:

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

Solution 4:

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

SETCMDS[EP] {;|c|ON|OFF}

Setsthenon-alphanumericcharacterusedtoseparatemultipleSQL*Pluscommandsenteredononelinetoc. ONorOFFcontrolswhetheryoucanentermultiplecommandsonaline. ONautomaticallysetsthecommandseparatorcharactertoasemicolon (;).

Solution 5:

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.

BEGININSERTINTO SOME_TABLE (q'[ 

Now;
You;
Can '
Do "'"';' ;;;
anycharacter? *

]');
END;
/