-
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.
-
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?
-
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.
-
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|