2016/11/18

Including files in SQL*Plus script

Do you know that @ (at sign) can also work as an include directive?
Suppose you have a script for getting your session statistics from v$mystat. But you need a particular list of statistics. Furthermore, you want to use the same list in several places of your script without writing it multiple times.
It is easy to do using @ (at sign).
Here is an example of a script which needs to include another script:

select m.statistic#,n.name,value from v$mystat m, v$statname n
where n.statistic#=m.statistic# and
n.name in (
@mystat_list
);

And here is content of mystat_list.sql
'session logical reads',
'CPU used by this session',
'session connect time',
'user I/O wait time',
'session pga memory max',
'consistent gets',
'physical reads',
'physical writes',
'physical reads direct temporary tablespace',
'physical writes direct temporary tablespace',
'lob reads',
'lob writes',
'parse time cpu',
'parse time elapsed',
'sorts (memory)',
'sorts (disk)',
'sorts (rows)'

The result is:
SQL> select m.statistic#,n.name,value from v$mystat m, v$statname n
  2  where n.statistic#=m.statistic# and
  3  n.name in (
  4  @mystat_list
 21  );

 STATISTIC# NAME                                                VALUE
----------- --------------------------------------------- -----------
         14 session logical reads                                 203
         19 CPU used by this session                                8
         24 user I/O wait time                                      6
         29 session connect time                           1479453315
         38 session pga memory max                            3395896
        132 consistent gets                                       200
        141 physical reads                                         20
        152 physical writes                                         0
        157 physical reads direct temporary tablespace              0
        158 physical writes direct temporary tablespace             0
        743 lob reads                                               0
        744 lob writes                                              0
       1082 parse time cpu                                          2
       1083 parse time elapsed                                      8
       1100 sorts (memory)                                         23
       1101 sorts (disk)                                            0
       1102 sorts (rows)                                           23

17 rows selected.

So, you can include whatever you need wherever you need. It can be part of PL/SQL anonymous block, part of a query or something else. Just make sure that resulted text will be syntactically correct. 

No comments: