Oracle Bind variable - query requested

Question

I would like to create a bind parameter based results for my Oracle SQL Query.

like for example.

Select * from emp where ename = :xyz;

it should ask me for the xyz value at runtime.

what should i do?

it keeps me givig error - Bind variable not declared...

please help...

Thanks.

Answer 1

If you run your statement as a script (F5) then you'll get this in the 'script output' pane:

Bind Variable "xyz" is NOT DECLARED

If you run it as a statement (control-enter) instead then you're prompted for the bind value, and the result appears in the 'query result' pane.

If you do need to run as a script you can use a substitution variable in your query as AlexisSTDM showed, or you can keep it as a bind variable but declare it with the variable command and assign a value to it from a substitution variable - which means your actual query doesn't need to be hard-parsed for each new value:

variable xyz varchar2(1)
exec :xyz := '&abc';
select * from dual where dummy = :xyz;

When run as a script you'll be prompted for the substitution variable abc, and that value is then assigned to the bind variable xyz, which is used by the query. With a slightly more generic query and supplying the value X when prompted, the 'script output' pane shows:

old:exec :xyz := '&abc'
new:exec :xyz := 'X'
anonymous block completed
DUMMY
-----
X     

You can add set verify off to hide the old/new lines, and can set feedback off before the exec to hide the anonymous block completed line, optionally turning it back on afterwards if you want feedback from your real queries. You can set a fixed value with exec if you prefer, but as you asked for prompts that isn't really relevant in this case; and if you have multiple bind values in the script, you can set them all from an anonymous PL/SQL block instead of using the exec shortcut multiple times.

Answer 2

If you are using bind variable in procedures, you can use the bind variable in the following manner

Create your query as dynamic means stored in local variable for eg.

create procedure PROC ( output_result out SYS_REFCURSOR) as l_query varchar2(1000) := Null; begin l_query := 'Select * from emp where ename = :xyz'; OPEN output_result FOR l_query using xyz; End PROC ;