Can we use a bind variable in oracle inside a procedure or function ?
I'm trying to update a bind variable inside my procedure. Can I do so in any case?
if (condition) then
:v_bind:=10;
end if;
Can I do the above thing inside a procedure or function..?
variable v_bind number;
create procedure abc as v_one
BEGIN
select count(a) into v_one from ab;
if(v_one<>0) then
:v_bind:=10;
end if;
Will I able to do this? It is showing me bad variable v_bind
Answer
You can't create a procedure with a bind variable in it because stored procedures are server-side objects and bind variables only exist on the client side.
Suppose I'm using SQL*Plus, and that I've created some bind variables. Once I exit SQL*Plus, any bind variables I created don't exist any more. However, stored procedures have to persist in the database, and hence they can't have any reference to anything that was created and then destroyed on the client.
Here's an example showing that you can't create a procedure that references a bind variable:
SQL> variable i number
SQL> exec :i := 0;
PL/SQL procedure successfully completed.
SQL> print :i
I
----------
0
SQL> create or replace procedure test_proc
2 as
3 begin
4 :i := 9;
5 end;
6 /
Warning: Procedure created with compilation errors.
SQL> show errors procedure test_proc;
Errors for PROCEDURE TEST_PROC:
LINE/COL ERROR
-------- -----------------------------------------------------------------
4/3 PLS-00049: bad bind variable 'I'
You can, however, pass a bind variable as an OUT parameter for a procedure. The procedure can then assign a value to the OUT parameter, and this value will then be stored in your bind variable.
Suppose we have the following procedure:
CREATE OR REPLACE PROCEDURE do_stuff (
p_output OUT INTEGER
)
AS
BEGIN
p_output := 6;
END;
We can use this to set a bind variable as follows:
SQL> variable i number
SQL> exec :i := 0;
PL/SQL procedure successfully completed.
SQL> print :i
I
----------
0
SQL> exec do_stuff(:i);
PL/SQL procedure successfully completed.
SQL> print :i
I
----------
6