PL / SQL: variable binding does not exist

Question

How to modify this procedure to use bind variables

PROCEDURE KILL(user IN VARCHAR2) AS BEGIN FOR REC IN (SELECT sid,serial# serial FROM V$SESSION WHERE username = user) LOOP execute immediate 'alter system kill session '' :1 , :2 '' immediate' using rec.sid, rec.serial; END LOOP; END; 

He gives:

variable binding does not exist

Answer 1

Bind variables in your expression are treated as literal strings, not placeholders. If you output the statement you are creating:

 BEGIN FOR REC IN (SELECT sid,serial# serial FROM V$SESSION WHERE username = user) LOOP dbms_output.put_line('alter system kill session '':1,:2'' immediate'); END LOOP; END; / 

... you see lines like:

 alter system kill session ':1,:2' immediate 

':1,:2' considered as a static value, and not as two bind variables. You cannot use bind variables in dynamic DDL, and I'm not sure if this applies to alter commands, so this may not be possible anyway.

The easiest way to achieve this may be to generate the entire statement in the cursor:

 BEGIN FOR REC IN ( SELECT 'alter system kill session ''' || sid ||','|| serial# ||''' immediate' stmt FROM V$SESSION WHERE username = user) LOOP dbms_output.put_line(rec.stmt); --execute immediate rec.stmt; END LOOP; END; / 

With the execute comment (I really don't want to kill my sessions just now) you can just see the commands that it will run, for example:

 alter system kill session '58,47157' immediate 

Your approach may still be wrong, as it will kill the session executing the block, and may or may not kill it last. I think this is in the scope of undefined behavior, and I really don't want to try to figure out what happens ... I doubt you really want anyway.

Edit: the "erroneous" comment is based on the use of user , which in my anonymous block will be an executable user; in your proc this will be the user from the parameter. However, using the keyword as the parameter name is confusing, so I would recommend changing the name to something like p_user in the arguments and statement.

Answer 2

I believe this might work

 PROCEDURE KILL(user IN VARCHAR2) AS BEGIN FOR REC IN (SELECT sid,serial# serial FROM V$SESSION WHERE username = user) LOOP execute immediate 'alter system kill session :1 '||','|| ':2 immediate' using rec.sid, rec.serial; END LOOP; END;