How to store database columns as variables in SQL*Plus   Donald K. Burleson

Oracle SQL*Plus has a very useful new sub-parameter to the column parameter called new_value.  The new_value directive allows data that has been retrieved from an Oracle table to be stored as a variable inside the SQL*Plus script.

By using the new_value parameter you can make your SQL*Plus script behave like a real programming language, storing and addressing program variables, just like in PL/SQL.

The ability to store SQL*Plus variables and fill them with Oracle data is a very powerful feature and makes SQL*Plus scripts more efficient because database access is reduced.

col log_mode_val new_value log_mode noprint

select

   value     log_mode_val

from

   v$parameter

where

   name = 'archive_log_mode';

select

   'The current archivelog mode is '||'&&log_mode' from dual;

column today  new_value  today;

select

   to_char(sysdate,'mm/dd/yyyy hh24:mi') today

from

   dual;

Now that we understand how SQL*Plus variables are stored, let's examine a real-world example.   Here is an example from a STATSPACK report that reports on table growth as a function of the block size of the database.  Because the db_block_size is a constant for the whole database, we can use the new_value parameter to capture this value once and then re-display it as part of our output.

In this example, we define a variable called &&blksz, and then use it inside the main query to determine the percentage of free space inside the table.

This calculation is done this way, and knowing the blocksize allows us to quickly estimate the amount of free space inside the table:
 

          (num_rows*avg_row_len)

          ---------------------    * 100

             (blocks*&blksz)

Here is the entire query.

column c1  heading "TABLE NAME"      format a15;

column c2  heading "EXTS"            format 999;

column c3  heading "FL"              format 99;

column c4  heading "# OF ROWS"       format 99,999,999;

column c5  heading "#_rows*row_len"  format 9,999,999,999;

column c6  heading "SPACE ALLOCATED" format 9,999,999,999;

column c7  heading "PCT USED"        format 999;

column db_block_size new_value blksz noprint

select value db_block_size from v$parameter where name = 'db_block_size';

set pages 999;

set lines 80;

spool tab_rpt.lst

select

        table_name            c1,

        b.extents             c2,

        b.freelists           c3,

        num_rows              c4,

        num_rows*avg_row_len  c5,

        blocks*&blksz          c6,

        ((num_rows*avg_row_len)/(blocks*&blksz))*100 c7

from

   perfstat.stats$tab_stats a,

   dba_segments b

where

 b.segment_name = a.table_name

and

   to_char(snap_time,'yyyy-mm-dd') =

      (select max(to_char(snap_time,'yyyy-mm-dd')) from perfstat.stats$tab_stats)

and

   avg_row_len > 500

order by c5 desc

;

If you like Oracle tuning, you might enjoy my latest book "Oracle Tuning: The Definitive Reference" by Rampant TechPress.  It's only $41.95 (I don't think it is right to charge a fortune for books!) and you can buy it right now at this link:

http://www.rampant-books.com/book_2003_1_Oracle_sga.htm