title: Using bind variables with dynamic SELECT INTO clause in PL/SQL
body: <p>I have a question regarding where bind variables can be used in a dynamic SQL statement in PL/SQL.</p>
  
  <p>For example, I know that this is valid:</p>
  
  <pre><code>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;
  /
  </code></pre>
  
  <p>I was wondering if you could use a bind variables in a select statement like this</p>
  
  <pre><code>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;
  /
  </code></pre>
  
  <p>Note I used a SELECT INTO statement as my dyamic string and used a bind variable in the INTO clause.</p>
  
  <p>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.</p>
  
  <p>Thanks</p>