ORACLE: Cursor with dynamic query - throws error "invalid identifier" for cursor field

Question

I have a logic to implement where I have to use dynamic sql(column names and where clause is decided on the fly).So here my cursor(emp_ref_cursor) has a dynamic sql, and has 3 cursor fields(emp_id,emp_name,dept).

Using these cursor fields in WHERE 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: "EMP_REC"."EMP_ID": invalid identifier" though I am able to output emp_rec.emp_id through DBMS_OUTPUT.

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.

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;

Answer 1

You have emp_rec defined as a local PL/SQL variable. None of the PL/SQL data is in scope to the dynamic SQL execution. When it is executed it as if you tried to run the statement - as it is displayed by your dbms_output standalone in a separate SQL context. If you did that it would be clear that emp_rec doesn't exist to the query.

You refer to it you would need to use a bind variable:

WHERE emp_id=:dynamic_col_3';

And then execute it with:

execute immediate stmt using emp_rec.emp_id;

But you can't use the x_dynamic_col_3 local variable in the using clause. Since - in this example anyway - the query would also need to change to use a different table column is the dynamic record field changed - that doesn't seem too much of a problem. But you said the where clause will change on the fly too. In that case you could have another local variable that you set to the relevant x field before the executin.

Answer 2

You have incorrect using of EXECUTE IMMEDIATE. You don't need to put INTO clause to SQL query. Use this instead:

            stmt:='SELECT ' || x_dynamic_col_1 || ',' || x_dynamic_col_2 || '
                     FROM employee A
                    WHERE emp_id=emp_rec.' || x_dynamic_col_3;

            execute immediate stmt into v_dob, v_desig;