title: ORACLE: Cursor with dynamic query - throws error "invalid identifier" for cursor field
body: <p>I have a logic to implement where I have to use dynamic sql(<em>column names and where clause is decided on the fly</em>).So here my cursor(<code>emp_ref_cursor</code>) has a dynamic sql, and has 3 cursor fields(<code>emp_id,emp_name,dept</code>). </p>
  
  <p>Using these cursor fields in <code>WHERE</code> clause I am trying to execute another dynamic sql inside the loop.Bt oracle isn't able to identify the cursor field and throws an error like "ORA-00904: <code>"EMP_REC"."EMP_ID": invalid identifier"</code> though I am able to output <code>emp_rec.emp_id</code> through <code>DBMS_OUTPUT</code>.</p>
  
  <blockquote>
    <p>NOTE: Please don't comment on the code quality this is not the actual code.
    This is just used to describe the problem. I can't post the actual code due to
    some compliance related stuff.</p>
  </blockquote>
  
  <pre><code>DECLARE
      emp_ref_cursor sys_refcursor; 
      v_sql varchar2(3900);
  
      TYPE emp_rec_type  IS RECORD (emp_id number,emp_name varchar2(100),dept_id varchar2(100)); 
      emp_rec emp_rec_type; 
  
      v_dept_id number:='1234';
  
      v_dob varchar2(100);
      v_desig varchar2(100);
  
      x_dynamic_col_1 varchar2(100):='dob'; --dynamic column(based on some condition)
      x_dynamic_col_2 varchar2(100):='designation'; --dynamic column(based on some condition)
      x_dynamic_col_3 varchar2(100):='emp_id'; --dynamic column(based on some condition)
  
  BEGIN   
      v_sql:='SELECT emp_id,emp_name,dept FROM employee WHERE dept_id=' || v_dept_id;
  
      OPEN  emp_ref_cursor FOR  v_sql;
          LOOP
                  FETCH emp_ref_cursor INTO  emp_rec;
                  exit WHEN emp_ref_cursor%NOTFOUND;
  
  
                  stmt:='SELECT ' || x_dynamic_col_1 || ',' || x_dynamic_col_2 || '
                        FROM employee A
                          WHERE emp_id=emp_rec.' || x_dynamic_col_3;
  
                  DBMS_OUTPUT.PUT_LINE(stmt); 
                  --Prints the SQL query as expected
  
                  DBMS_OUTPUT.PUT_LINE('emp_rec.emp_id:'||emp_rec.emp_id);
                  --Displays the value!!!
  
                  execute immediate stmt into v_dob, v_desig; 
                  --But why is it saying emp_rec.emp_id is invalid identifier??
      END LOOP;            
  END;
  </code></pre>