DYNAMIC SQL RETURNING clause giving error in Oracle 10g

Question

Below is the anonymous block:

DECLARE
v_tablename VARCHAR2(20);
sql_statement VARCHAR2(500);
v_empid emp.emp_id%TYPE := 11;
v_empname emp.emp_name%TYPE;
v_deptid emp.dept_id%TYPE;
v_age emp.age%TYPE;
v_sex emp.sex%TYPE;
BEGIN
v_tablename := '&table';
sql_statement := 'UPDATE ' || v_tablename || ' SET age = age + 1 WHERE emp_id = :1 RETURNING empname,deptid,age,sex INTO :2, :3, :4, :5';
EXECUTE IMMEDIATE sql_statement USING v_empid RETURNING INTO v_empname, v_deptid, v_age, v_sex;
DBMS_OUTPUT.PUT_LINE('Name: ' || v_empname);
DBMS_OUTPUT.PUT_LINE('Dept Id: ' || v_deptid);
DBMS_OUTPUT.PUT_LINE('Age: ' || v_age);
DBMS_OUTPUT.PUT_LINE('Sex: ' || v_sex);
END;

When I execute this, I am getting the below error message:

ORA-00904: "DEPTID": invalid identifier

The error is thrown in the below line:

sql_statement := 'UPDATE ' || v_tablename || ' SET age = age + 1 WHERE emp_id = :1 RETURNING empname,deptid,age,sex INTO :2, :3, :4, :5';

The error sounds like the variable is not already declared but for RETURNING in a DML (i.e. UPDATE in my case) do we really need to declare the variables?

Kindly let me know how to resolve this and make this piece of block work.

Alex Poole · Accepted Answer · 2013-06-29 14:19:39Z

2

Your returning clause is attempting to return columns empname and deptid, when the columns in the table are emp_name and dept_id. It isn't the local variables names that are the problem, or mentioned in the error message.

So you need:

... RETURNING emp_id, dept_id, age, sex INTO ...

I'm not sure why you've dropped the underscores in your local variable names; it might be less confusing if you had v_emp_id, v_emp_name and v_dept_id.

Of course this doesn't need to be done with dynamic SQL anyway, but I assume you're experimenting.

answered Jun 29, 2013 at 14:19

Alex Poole

192k11 gold badges198 silver badges349 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

user182944 Over a year ago

Thanks for the resolution...my misconception lead me to this issue. Yes correct, I am new to DYNAMIC SQL and experimenting with it.

Collectives™ on Stack Overflow

DYNAMIC SQL RETURNING clause giving error in Oracle 10g

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related