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
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.
--------------------- * 100
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;
b.segment_name = a.table_name
(select max(to_char(snap_time,'yyyy-mm-dd')) from perfstat.stats$tab_stats)
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.