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

User · Answer

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

User · 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

User · Answer

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

User · Answer

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

User · Answer

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

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

Examples related to sql

Examples related to oracle

Examples related to plsql

Examples related to oracle11g

Examples related to execute-immediate