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 ()