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.

1 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

This site uses Akismet to reduce spam. Learn how your comment data is processed.