oracle insert into insert,INSERT INTO With RETURNING INTO... — oracle-tech

Both the following methods work on Oracle9i:

SQL> DECLARE

2 l_empno NUMBER := 1001;

3 l_deptno NUMBER := 20;

4 l_ename VARCHAR2(50) := 'NAME';

5 l_table VARCHAR2(31) := 'EMP';

6 l_rowid ROWID;

7 BEGIN

8 EXECUTE IMMEDIATE 'insert into ' || l_table ||

9 '(empno, ename, deptno) values (:empno, :ename, :deptno) returning rowid into :rid'

10 USING IN l_empno, IN l_ename, IN l_deptno, OUT l_rowid;

11 dbms_output.put_line('rowid = ' || l_rowid);

12 END;

13 /

rowid = AAAIgBAAMAAAAAQAAA

PL/SQL procedure successfully completed.

SQL> DECLARE

2 l_empno NUMBER := 1002;

3 l_deptno NUMBER := 20;

4 l_ename VARCHAR2(50) := 'NAME';

5 l_table VARCHAR2(31) := 'EMP';

6 l_rowid ROWID;

7 BEGIN

8 EXECUTE IMMEDIATE 'insert into ' || l_table ||

9 '(empno, ename, deptno) values (:empno, :ename, :deptno) returning rowid into :rid'

10 USING IN l_empno, IN l_ename, IN l_deptno RETURNING INTO l_rowid;

11 dbms_output.put_line('rowid = ' || l_rowid);

12 END;

13 /

rowid = AAAIgBAAMAAAAAQAAB

PL/SQL procedure successfully completed.

SQL> disconnect

Disconnected from Oracle9i Enterprise Edition Release 9.2.0.3.0 - Production

With the Partitioning, OLAP and Oracle Data Mining options

JServer Release 9.2.0.3.0 - Production

SQL>