Dumping the spool..from the pits of SQLPlus..

When spooling some large lines from the database with the command line (ie: sqlplus) in order to do some manual editing with say, vi.. it would be nice to have some structure to the output of the database.

After a lot of setting and un-setting (and, ok, ok, cursing)..I came up with the following settings to get some decent output from SQLPLUS.

— Get rid of the headers
set heading off;
set echo off;
set pagesize 0;

— don’t truncate the line output and trim extra spaces when spooling to file
set long 99999;
set linesize 32767; –> this is the max sqplus can place on one line..
set trimspool on;

— name/cq format the column:
col object_ddl format A32000;

spool /tmp/spoolMe.sql;

SELECT dbms_metadata.get_ddl('INDEX','SYS_C00016$$','USER')||';' AS object_ddl
spool off;

This should provide more editable output where a bit of AWK,GREP and VI can easily take care of a lot of code.

Theses settings are mostly a combination of settings I’ve found on the Internet with Google, but I thought it nice to have this on my blog, were I could easily find it again for another time.


About GemsOfProgramming

Beeing a previously enthusiastic Java programmer, I rolled into the Oracle Database Administration world. It turned out I got a knack for this, and since approx. 2000 I'm a full time DBA. My experiences touches lot of Oracle products like Forms and Reports 9/10, JDAPI, Application Server, Weblogic Fusion and of course: Oracle Enterprise Databases, JavaFX, Swing and other Java components.
This entry was posted in Databases, Technical Stuff. Bookmark the permalink.

One Response to Dumping the spool..from the pits of SQLPlus..

  1. hello there get this from Google great blog

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s