Store SQL*Plus variables

Oracle SQL*Plus has a very useful subparameter to the column parameter called NEW_VALUE. The NEW_VALUE directive allows you to store data retrieved from an Oracle table as a variable inside the SQL*Plus script.

Oracle SQL*Plus has a very useful subparameter to the column parameter called NEW_VALUE. The NEW_VALUE directive allows you to store data retrieved from an Oracle table 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 it makes SQL*Plus scripts more efficient because it reduces database access.

col log_mode_val new_value log_mode noprint

select
   value     log_mode
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's 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 redisplay 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. The example below shows how we do this calculation. Knowing the block size 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
;

Donald Burleson has been a database administrator for 23 years and has written 14 database books and over 100 articles. He is editor in chief of Oracle Internals and runs Burleson Oracle Consulting.