I have a application that has been written using bind variables called: 1, 2, 3.. I want to exercise the execution plans stored in sql_area, and use the same text for execute the query in sqlplus
By example:
select * from dual where dummy=:1;
How can I define "1" as a bind variable in sqlplus ?
var 1 varchar2(1); doesn't work.
It doesn't recognize "1" as a variable name.
Yep, that's annoying, but you can wrap your query into anonymous PL/SQL block (script) like this:
SQL> exec execute immediate 'select count(*) cnt from dual where :1=dummy' using 'x';
PL/SQL procedure successfully completed.
SQL> select sql_id from v$sqlarea a where sql_text like 'select count(*) cnt from dual%';
SQL_ID
-------------
8y7sav2n21055
1 row selected.
SQL> ed tests/run_sql.sql
SQL> !cat tests/run_sql.sql
declare
vsql clob;
begin
select sql_fulltext into vsql from v$sqlarea a where a.sql_id='&1';
execute immediate vsql using &2;
end;
/
SQL> @tests/run_sql.sql 8y7sav2n21055 123
PL/SQL procedure successfully completed.
Btw, SqlCl has another much easier workaround: use alias for it:
SQL> alias sel1=select :1 from dual;
SQL> sel1 123
:1
----------------------------------------------------
123