How to save a database column in a variable form in SQL *plus

Oracle SQL *plus has a very useful sub-parameter (subparameter), which is subordinate to a data bar parameter called New_value. New_value indicates that you are allowed to save data from an Oracle form query by using variables in the SQL *plus script.

Using the New_value parameter, you can use SQL *plus scripts like real programming languages to save and address program variables, as Pl/sql does.

Saving the SQL *plus variable and populating it with Oracle data is a very important feature, because it reduces access to the database, so it improves the effectiveness of the SQL *plus script.

Collog_mode_valnew_valuelog_modenoprint

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 the SQL *plus variable is saved, let's take a look at a real example. The following example comes from the Statspack report, which describes the functional relationship between table growth and the size of the database block. Since Db_block_size is a constant in the entire database, we use the New_value parameter to capture the value once, and then display it again as part of the output.

In this example, we define a variable called &blksz and use it in the main query to determine the remaining space in the table. The following example shows us how to do this calculation. Knowing the block size allows us to quickly estimate the amount of space left in the table.

(Num_rows*avg_row_len)
---------------------* 100
(Blocks*&blksz)

This 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_sizenew_valueblksznoprint

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