BYS2 BYS2 - 5 months ago 41
SQL Question

Using bind variables with dynamic SELECT INTO clause in PL/SQL

I have a question regarding where bind variables can be used in a dynamic SQL statement in PL/SQL.

For example, I know that this is valid:

CREATE OR REPLACE FUNCTION get_num_of_employees (p_loc VARCHAR2, p_job VARCHAR2)
RETURN NUMBER
IS
v_query_str VARCHAR2(1000);
v_num_of_employees NUMBER;
BEGIN
v_query_str := 'SELECT COUNT(*) FROM emp_'
|| p_loc
|| ' WHERE job = :bind_job';
EXECUTE IMMEDIATE v_query_str
INTO v_num_of_employees
USING p_job;
RETURN v_num_of_employees;
END;
/


I was wondering if you could use a bind variables in a select statement like this

CREATE OR REPLACE FUNCTION get_num_of_employees (p_loc VARCHAR2, p_job VARCHAR2)
RETURN NUMBER
IS
v_query_str VARCHAR2(1000);
v_num_of_employees NUMBER;
BEGIN
v_query_str := 'SELECT COUNT(*) INTO :into_bind FROM emp_'
|| p_loc
|| ' WHERE job = :bind_job';
EXECUTE IMMEDIATE v_query_str
USING out v_num_of_employees, p_job;
RETURN v_num_of_employees;
END;
/


Note I used a SELECT INTO statement as my dyamic string and used a bind variable in the INTO clause.

I am currently travelling right now and won't have access to my computer back at home for a few days, but this has been nagging me for a bit. Tried reading the PL/SQL reference but they don't have an example of a select like this.

Thanks

Answer

No you can't use bind variables that way. In your second example :into_bind in v_query_str is just a placeholder for value of variable v_num_of_employees. Your select into statement will turn into something like:

SELECT COUNT(*) INTO  FROM emp_...

because the value of v_num_of_employees is null at EXECUTE IMMEDIATE.

Your first example presents the correct way to bind the return value to a variable.

Edit

The original poster has edited the second code block that I'm referring in my answer to use OUT parameter mode for v_num_of_employees instead of the default IN mode. This modification makes the both examples functionally equivalent.