Using variable binding

Question

Is it possible to use a binding variable in oracle inside a procedure or function?

I am trying to update a bind variable inside my procedure. Can I do it anyway?

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 be able to do this? It shows me a bad v_bind variable

Answer 1

You cannot create a procedure with a bind variable because stored procedures are objects on the server side and bind variables exist only on the client side.

Suppose I am using SQL * Plus, and that I have created some bind variables. As soon as I exit SQL * Plus, any bind variables that I create no longer exist. However, stored procedures must be stored in a database and, therefore, they cannot refer to anything that was created and then destroyed on the client.

Here is an example showing that you cannot 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'

However, you can pass the bind variable as an OUT parameter to the procedure. The procedure can then assign a value to the OUT parameter, and that value will then be stored in your binding variable.

Suppose we perform 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 the 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

Answer 2

No, you cannot do what you ask. Variable variables in plsql are handled transparently. You do not explicitly encode binding variables unless you intend to use "execute immediately" to run code outside plsql as follows:

 declare v_bind number := 1; begin execute immediate 'select * from table where x = :v_bind'; end;` 

The following code also uses bind variables, but it is processed transparently with plsql:

 declare v_bind number := 1 y number; begin select count(*) into y from table where x = v_bind; end; 

Answer 3

You cannot bind a sqlplus variable in a session to a function / procedure. This will give you the "Bad bind variable" error. In fact, you can simply pass the bind variable from an oracle session to any procedure.

Let's see an example

  variable v1 NUMBER; begin select salary into :v1 from employees where employee_id = 100; dbms_output.put_line(:v1); end; / 

And if you run the above example by including in the procedure / function, it will show you an error.

  create or replace procedure proc is begin select salary into :v1 from employees where employee_id = 100; dbms_output.put_line(:v1); end; / 

Mistake -

 PROCEDURE proc compiled Warning: execution completed with warning 3/20 PLS-00049: bad bind variable 'V1' 4/22 PLS-00049: bad bind variable 'V1' 

Thus, it is not possible to use session-level bind variables in procedures / functions. In the example below, t2 is a variable binding

 create or replace procedure proc is t2 NUMBER; begin select salary into t2 from employees where employee_id = 100; dbms_output.put_line(t2); end; / 

You can call this procedure from sqlplus as

 exec proc;