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

Question

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

Lawana

Related: https://mcmap.net/q/374501/-why-cannot-i-use-bind-variables-in-ddl-scl-statements-in-dynamic-sql/1461424 – Zombie

Answer 1

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.

Charley

Oh that makes sense, the ':into_bind' would be replaced when it is actually run. In the first example I wrote, would it still be using a bind variable in the INTO clause though? The reason I wanted to try the second way, was because it uses bind variables. – Lawana

@BYS2: Yes, you can use with OUT parameter mode. However I prefer EXECUTE IMMEDIATE INTO for clarity. – Charley

java2s.com/Tutorial/Oracle/0440__PL-SQL-Statements/… – Bausch

Answer 2

In my opinion, a dynamic PL/SQL block is somewhat obscure. While is very flexible, is also hard to tune, hard to debug and hard to figure out what's up. My vote goes to your first option,

EXECUTE IMMEDIATE v_query_str INTO v_num_of_employees USING p_job;

Both uses bind variables, but first, for me, is more redeable and tuneable than @jonearles option.

Dniester

Ok thank you for that information, since both use bind varialbes, the first option seems to be best! – Lawana

@BYS2: I also like this syntax best. No idea about the performance. – Charley

Answer 3

Put the select statement in a dynamic PL/SQL block.

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 := 'begin SELECT COUNT(*) INTO :into_bind FROM emp_' 
                 || p_loc
                 || ' WHERE job = :bind_job; end;';
  EXECUTE IMMEDIATE v_query_str
    USING out v_num_of_employees, p_job;
  RETURN v_num_of_employees;
END;
/

Rb

Ahh i see, thanks! On a side note, do you have any idea which way is better? encapsulating the SELECT INTO in a block like you did or using the INTO clause of the EXECUTE IMMEDIATE statement like I did in my first example.Do both use bind variables? any difference in efficiency? – Lawana

I agree with the others that the first method (dynamic SQL) is much better than the second (dynamic PL/SQL). Dynamic PL/SQL is very rare and almost never necessary. Based on some simple testing, I didn't see any performance difference between the two, but I would expect the dynamic PL/SQL method to be slightly slower in some cases. The dynamic PL/SQL method actually generates 3 bind variables: 2 for the PL/SQL block (although neither is captured in V$SQL_BIND_CAPTURE), and 1 for the SQL query. Quirks like that can make tuning and debugging very difficult. – Rb

Oh ok, thanks alot for all your advice! Couldn't put yours down as the answer because technically, that wasn't my question, but I'm all for learning so this is good to know :D – Lawana

This makes the most sense. Keep in mind, it's executing outside of PLSQL (right term?) like you'd see at a SQLPlus command prompt which is why you can do DDL from exec immed. – Jeffers

Answer 4

Bind variable can be used in Oracle SQL query with "in" clause.

Works in 10g; I don't know about other versions.

Bind variable is varchar up to 4000 characters.

Example: Bind variable containing comma-separated list of values, e.g.

:bindvar = 1,2,3,4,5

select * from mytable
  where myfield in
    (
      SELECT regexp_substr(:bindvar,'[^,]+', 1, level) items
      FROM dual
      CONNECT BY regexp_substr(:bindvar, '[^,]+', 1, level) is not null
    );

(Same info as I posted here: How do you specify IN clause in a dynamic query using a variable? )

Grit

Answer 5

Select Into functionality only works for PL/SQL Block, when you use Execute immediate , oracle interprets v_query_str as a SQL Query string so you can not use into .will get keyword missing Exception. in example 2 ,we are using begin end; so it became pl/sql block and its legal.

Evangelina