use of bind variable

Question

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 1

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

Answer 2

No, you cannot do what you are asking. Bind variables in plsql are handled transparently. You do not explicitly code bind variables unless you are going to use 'execute immediate' to run the code outside of plsql like this:

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

The following code uses bind variables as well, but it is handled transparently by plsql:

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

Answer 3

You can't bind a sqlplus variable in a session to a function/procedure. It will give you error of "Bad bind variable". You can actually just pass bind variable from your oracle session to any procedure.

Let's see a 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 enclosing in procedure/function it will show you error.

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

Error -

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 below example t2 is a bind variable

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;