You may know that some applications generate queries with bind variables’ names like :1 or :”1″, and neither SQL*Plus nor SQLCl support such variables:
SQLPlus:
SQL> var 1 number;
SP2-0553: Illegal variable name "1".
SQLCL:
SQL> var 1 number;
ILLEGAL Variable Name "1"
So we can’t run such queries as-is, but, obviously, we can wrap them into anonymous PL/SQL blocks and even create a special script for that:
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.
But it’s even easier in SQLCl with alias command:
SQL> alias sel1=select :1 from dual;
SQL> sel1 123
:1
--------------------------------------
123
1 row selected.