Single Insert, Capturing ROWID

[gopi]

Hello,

If I do a single row INSERT can I capture the rowid without doing a:

SELECT rowid FROM table WHERE..(the row I just inserted)?

I do have the luxary of a host language using emedded SQL such as PRO*COBOL or PRO*C and Java.

[jmodic]

SQL> set serveroutput on
SQL> desc t1
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 C1                                                 NUMBER

SQL> declare
  2    v_rowid rowid;
  3  begin
  4    insert into t1 (c1) values (1) returning rowid into v_rowid;
  5    dbms_output.put_line('ROWID of the inserted row: ' ||  v_rowid);
  6  end;
  7  /

ROWID of the inserted row: AAAcjCAAPAAAQk5AAE

PL/SQL procedure successfully completed.

[gopi]

Thanks Jurij....This worked in SQL*Plus and I learned something new that I can use in Oracle objects like stored procedures, funcations, etc...

But the host language, ie PRO*COBOL, could not handle the rowid datatype.

If you have any creative ideas, please let me know.

thanks again.

[jmodic]

No, it is incorrect to say that "this worked in SQL*Plus" - it realy worked in PL/SQL. SQL*Plus was just a hosting environment, there was absolutely none of the SQL*Plus specific features used in my example.

And yes, PL/SQL does support the ROWID datatype, while many other hosting environments/programing languages do not. But this does not prevent you form using this new (as of 8i) SQL feature - the RETURNING INTO clause. Because that's what it is - it is purely SQL feature, it has nothing to do with the host language that you use.

If you cant declare a variable of type ROWID, then use VARCHAR2 datatype (or any other suitable string datatype) to store the ROWID value - Oracle will automaticaly convert ROWID into string for you.

Here is an example in pure SQL*Plus. It doesn't use any PL/SQL, simply to show you that this is not PL/SQL feature either. And it also demonstrates that SQL*Plus doesn't support ROWID datatype either, yet you can still return the ROWID of the currently inserted record:

SQL> REM The following shows that ROWID datatype is not supported in SQL*Plus
SQL> REM
SQL> variable v_rowid ROWID;
Usage: VAR[IABLE] [  [ NUMBER | CHAR | CHAR (n [CHAR|BYTE]) |
                    VARCHAR2 (n [CHAR|BYTE]) | NCHAR | NCHAR (n) |
                    NVARCHAR2 (n) | CLOB | NCLOB | REFCURSOR ] ]
SQL> REM the following shows that you can return the ROWID of the inserted
SQL> REM record into a host variable of VARCHAR2 datatype:
SQL> REM
SQL> variable v_rowid VARCHAR2(20);
SQL> 
SQL> insert into t1 (c1) values (1) returning rowid into :V_ROWID;

1 row created.

SQL> print V_ROWID

V_ROWID
--------------------------------
AAACzoAADAAAAMUAAH

SQL> insert into t1 (c1) values (1) returning rowid into :V_ROWID;

1 row created.

SQL> print V_ROWID

V_ROWID
--------------------------------
AAACzoAADAAAAMUAAI

So I can't see any reason why this would not work in any other host language, including Pro*COBOL.