Technical Tip: Saving SQL values into SQL*Plus variables.
The SQL*Plus COLUMN command allows you to have SELECT statement results stored into a SQL*Plus variable. You can then use that variable later in your SQL*Plus script. The two scripts below are simple examples of this capability.
rem rem Get the database name and use it in the spool command to spool to a file that rem is named according to the database name. rem column db_name noprint new_value db_name select name db_name from v$database; spool &db_name..rpt rem rem Set the output linesize to the maximum length of any data in the column that you are rem selecting. rem set pagesize 0 set feedback off column line_size noprint new_value line_size select max(length(mydata)) line_size from mytable; set linesize &line_size spool mytable.rpt select mydata from mytable; spool off
Filed under: Technical Tips
Like this post? Subscribe to my RSS feed and get loads more!