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;
SELECT dbms_metadata.get_ddl('INDEX','SYS_C00016$$','USER')||';' AS object_ddl FROM DUAL; 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.