Hello gurus,
In line 9 of pln.sql below, there is a substitution variable "&1".
The first time I ran the query pln.sql, it prompted me for a value for &1.
On all subsequent executions of pln.sql, it just uses the value of &1. It doesn't prompt me for a value.
According to http://www.orafaq.com/wiki/SQL*Plus_FAQ,
>
"&" is used to create a temporary substitution variable that will prompt you for a value every time it is referenced.
>
So, what am I missing? Does the behaviour of variable substitution change when the variable in inside a script?
Code listing is below; beneath the listing you can see "def 1" shows that the variable &1 already has the string "Oracle" assigned to it.
I would like to be able to run this script and each time have it prompt me for a value for &1.
Many thanks,
Jason
HR@XE> get C:\Users\J\Documents\SQL\ProOracleSQL\chapter05_scripts\pln.sql
1 SELECT xplan.*
2 FROM
3 (
4 select max(sql_id) keep
5 (dense_rank last order by last_active_time) sql_id
6 , max(child_number) keep
7 (dense_rank last order by last_active_time) child_number
8 from v$sql
9 where upper(sql_text) like '%&1%'
10 and upper(sql_text) not like '%FROM V$SQL WHERE UPPER(SQL_TEXT) LIKE %'
11 ) sqlinfo,
12* table(DBMS_XPLAN.DISPLAY_CURSOR(sqlinfo.sql_id, sqlinfo.child_number, 'ALLSTATS LAST')) xplan
13
HR@XE> def 1
DEFINE 1 = "Oracle" (CHAR)
HR@XE> @C:\Users\J\Documents\SQL\ProOracleSQL\chapter05_scripts\pln.sql
old 9: where upper(sql_text) like '%&1%'
new 9: where upper(sql_text) like '%Oracle%'
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------
SQL_ID 6k3tn5dr0k17v, child number 0
-------------------------------------
SELECT xplan.* FROM ( select max(sql_id) keep (dense_rank
last order by last_active_time) sql_id , max(child_number) keep
(dense_rank last order by last_active_time) child_number from
v$sql where upper(sql_text) like '%Oracle%' and upper(sql_text)
not like '%FROM V$SQL WHERE UPPER(SQL_TEXT) LIKE %' ) sqlinfo,
table(DBMS_XPLAN.DISPLAY_CURSOR(sqlinfo.sql_id, sqlinfo.child_number,
'ALLSTATS LAST')) xplan
Plan hash value: 2794990177
------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 13 |00:00:00.05 | 4 |
| 1 | NESTED LOOPS | | 1 | 8168 | 13 |00:00:00.05 | 4 |
| 2 | VIEW | | 1 | 1 | 1 |00:00:00.01 | 0 |
| 3 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:00.01 | 0 |
|* 4 | FIXED TABLE FULL | X$KGLCURSOR_CHILD | 1 | 1 | 0 |00:00:00.01 | 0 |
| 5 | COLLECTION ITERATOR PICKLER FETCH| DISPLAY_CURSOR | 1 | 8168 | 13 |00:00:00.04 | 4 |
------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - filter((UPPER("KGLNAOBJ") LIKE '%Oracle%' AND UPPER("KGLNAOBJ") NOT LIKE '%FROM V$SQL WHERE
UPPER(SQL_TEXT) LIKE %' AND "INST_ID"=USERENV('INSTANCE')))
29 rows selected.