Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

How can I use EXECUTE IMMEDIATE with the %rowtype? Please help me..

User_VDWRCMar 3 2021

Hi. I have a problem...
I need to use EXECUTE IMMEDIATE with the %rowtype as a bind variable.
but when I put the column like ROW_EMP.EMPNO, It's working.
I don't know why the error appears as below that is a sample.

Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production
---------------------------------------------------------------
DECLARE
ROW_EMP EMP%ROWTYPE;
V_STMT VARCHAR2(1000);
BEGIN
ROW_EMP.EMPNO := 1234;
DBMS_OUTPUT.put_line ('--1 ROW_EMP.EMPNO ('||ROW_EMP.EMPNO||')');

V_STMT := 'BEGIN :1 := NULL; END;';
EXECUTE IMMEDIATE V_STMT USING IN OUT ROW_EMP;
DBMS_OUTPUT.put_line ('--2 ROW_EMP.EMPNO ('||ROW_EMP.EMPNO||')');
END;

SQL Error [6550] [65000]: ORA-06550: line 8, column 41:
PLS-00457: expressions have to be of SQL types
ORA-06550: line 8, column 3:
PL/SQL: Statement ignored
---------------------------------------------------------------
DECLARE
ROW_EMP EMP%ROWTYPE;
V_STMT VARCHAR2(1000);
BEGIN
ROW_EMP.EMPNO := 1234;
DBMS_OUTPUT.put_line ('--1 ROW_EMP.EMPNO ('||ROW_EMP.EMPNO||')');

V_STMT := 'BEGIN :1 := NULL; END;';
EXECUTE IMMEDIATE V_STMT USING IN OUT ROW_EMP.EMPNO;
DBMS_OUTPUT.put_line ('--2 ROW_EMP.EMPNO ('||ROW_EMP.EMPNO||')');
END;

--1 ROW_EMP.EMPNO (1234)
--2 ROW_EMP.EMPNO ()

This post has been answered by mathguy on Mar 3 2021
Jump to Answer
Comments
Post Details
Added on Mar 3 2021
7 comments
1,630 views