Mega Code Archive
Use DBMS_SQL with the RETURNING clause
<![CDATA[
SQL>
SQL> CREATE TABLE MyTable (
2 num_col NUMBER,
3 char_col VARCHAR2(60)
4 );
Table created.
SQL>
SQL> set serveroutput on
SQL> DECLARE
2 v_RowidString VARCHAR2(100);
3 v_NumVal NUMBER := 1;
4 v_StringVal VARCHAR2(20) := 'rowid';
5 BEGIN
6 INSERT INTO MyTable VALUES (v_NumVal, v_StringVal)
7 RETURNING ROWID INTO v_RowidString;
8 DBMS_OUTPUT.PUT_LINE('ROWID of new row is: ' || v_RowidString);
9 END;
10 /
ROWID of new row is: AAADlMAABAAAKV6AAA
PL/SQL procedure successfully completed.
SQL>
SQL> DECLARE
2 v_RowidString VARCHAR2(100);
3 v_CursorID INTEGER;
4 v_NumVal NUMBER := 1;
5 v_StringVal VARCHAR2(20) := 'rowid';
6 sqlString VARCHAR2(100);
7 v_ReturnCode NUMBER;
8 BEGIN
9 sqlString :=
10 'INSERT INTO MyTable VALUES (:n, :c) ' ||
11 'RETURNING ROWID INTO :r';
12 v_CursorID := DBMS_SQL.OPEN_CURSOR;
13 DBMS_SQL.PARSE(v_CursorID, sqlString, DBMS_SQL.NATIVE);
14 DBMS_SQL.BIND_VARIABLE(v_CursorID, ':n', v_NumVal);
15 DBMS_SQL.BIND_VARIABLE(v_CursorID, ':c', v_StringVal);
16 DBMS_SQL.BIND_VARIABLE(v_CursorID, ':r', v_RowidString, 100);
17 v_ReturnCode := DBMS_SQL.EXECUTE(v_CursorID);
18 DBMS_SQL.VARIABLE_VALUE(v_CursorID, ':r', v_RowidString);
19 DBMS_OUTPUT.PUT_LINE('ROWID of new row is: '|| v_RowidString);
20 END;
21 /
ROWID of new row is: AAADlMAABAAAKV6AAB
PL/SQL procedure successfully completed.
SQL>
SQL>
SQL> drop table MyTable;
Table dropped.
SQL>
SQL>
]]>