CODEWITHSUNDEEP
sqloracle-databaseoracle-sqldevelopersqlplus
joseyluis
joseyluis

Reputation: 585

How to define a bind variable called "1" in sqlplus

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.

Upvotes: 2

Views: 934

Answers (2)

Rui S
Rui S

Reputation: 1

Have you tried doing this

VARIABLE in VARCHAR2(10)
EXEC :in := 'X';
EXEC EXECUTE IMMEDIATE 'select * from dual where dummy=:1' USING :in;

If you want to get a result use this

VARIABLE in VARCHAR2(10)
VARIABLE out VARCHAR2(10)
EXEC :in := 'X';
EXEC EXECUTE IMMEDIATE 'select ''hello ''||dummy from dual where dummy=:1' INTO :out USING :in;
PRINT out

Upvotes: 0

Sayan Malakshinov
Sayan Malakshinov

Reputation: 8655

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.

enter image description here

Btw, SqlCl has another much easier workaround: use alias for it:

SQL> alias sel1=select :1 from dual;
SQL> sel1 123

:1
----------------------------------------------------
123

Upvotes: 2

Related Questions