Single Insert, Capturing ROWID
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 4 of 4

Thread: Single Insert, Capturing ROWID

  1. #1
    Join Date
    Dec 2001
    Location
    Keene, NH
    Posts
    510
    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.
    Don't blame me, I'm from Red Sox Nation.

  2. #2
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    Code:
    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.
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  3. #3
    Join Date
    Dec 2001
    Location
    Keene, NH
    Posts
    510
    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.
    Don't blame me, I'm from Red Sox Nation.

  4. #4
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    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:

    Code:
    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.
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width