Use DBMS_SQL with the RETURNING clause.

  

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>

Related examples in the same category

1.	First DBMS_SQL Example
2.	Pass a query statement to a stored procedure
3.	Use dbms_sql.open_cursor create a cursor
4.	Use dbms_sql.bind_variable, dbms_sql.execute to insert value to a table
5.	Use dbms_sql.bind_array to bind array to a cursor
6.	Use dbms_sql.parse to bind a select statement to a cursor
7.	Call dbms_sql.describe_columns2 to get info for a column
8.	Non-Query DML and DDL Statements
9.	DBMS_SQL.NATIVE;
10.	DBMS_SQL.PARSE and DBMS_SQL.EXECUTE
11.	Dynamic sql statement with variable binding
12.	Dynamic select statement and row fetch
13.	Execute PL/SQL block
14.	DBMS_SQL package: dynamic SQL
15.	Pass where clause to a stored procedure
16.	Executing Queries and use DBMS_SQL.COLUMN_VALUE to map value
17.	Executing PL/SQL Blocks and use BIND_VARIABLE to bind variable
18.	drop the supplied table using dynamic SQL.
19.	DBMS_SQL.BIND_VARIABLE_CHAR
20.	illustrate the importance of setting out_value_size.
21.	Calling a function which uses dynamic SQL from within an SQL statement.
22.	Illustrates the interaction of roles and dynamic SQL.
23.	illustrates a DDL statement which is built dynamically from the procedure parameters.
24.	Use dbms_sql to process query, cursor and value
25.	Use dbms_sql.bind_variable to bind variable
26.	dbms_sql.varchar2_table type variable

Use DBMS_SQL with the RETURNING clause. : dbms_sql « System Packages « Oracle PL / SQL

Related examples in the same category