Re: ROWID problems with OCI 7 app and Oracle 8 server

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: 1998/01/15
Message-ID: <34d22608.217402498@inet16>#1/1

On Wed, 14 Jan 1998 14:28:32 -0800, "Mark E. Hansen" <meh_at_unify.com> wrote:

>Can anyone please help us with this one?
>
>We have an OCI version 7 application that, among other things, must
>insert new records. After the insert, we construct the ROWID from the
>information available in the cursor structure, and use that value for
>subsequent fetch/update operations.
>
>This works fine until we connect to an Oracle 8 server. Now, the ROWIDs
>we construct are the wrong format, and we can find no way to create the
>ROWIDs in the proper format.
>
>Oracle Support says that what we're trying to do is not possible! I find
>that pretty hard to believe.
>
>Does anyone have an OCI version 7 application that must construct the
>ROWID for a newly inserted row against an Oracle 8 database server? can
>you please help us?

Instead of using the cda trick you are using, a portable way to do this that is supported might look like:

create or replace package rowid_pkg
as

   the_last_inserted_rowid rowid;
end;
/

create or replace trigger rowid_pkg_trigger_for_emp after insert on emp
for each row
as
begin
  rowid_pkg.the_last_inserted_rowid := :new.rowid; end;
/

and then just access the_last_inserted_rowid in the package. (by portable I mean that this solution would work the same in o7 as o8). The rowid format in Oracle8 changed in its external representation. Instead of looking like:

SQL> select rowid from dual;

ROWID

---

0000033D.0000.0001

they now look like:

ROWID

---

AAAACsAABAAAAGiAAA

Also, you might look into the dbms_rowid package with Oracle8 that might help in some of your conversions...

When and if you start using the oracle8 oci, this (getting the rowid) is achieved via the following syntax:

  1 declare
  2 r rowid;
  3 begin

  4      insert into x (y) values (1) returning rowid into r;
  5      dbms_output.put_line( r );

  6* end;
SQL> / AAAEVKAAFAAAV4oAAA

Notice the "returning rowid into r" syntax that is new with oracle8. This might open another way to perform this without a trigger (but your C code would be a little different for O8 then O7). If you detect in your code that you were connected to a 7.x vs an 8.x database, you could code something like:

 if ( v7 ) then

• execute insert into table .....
• inspect the CDA to get the rowid and build it else
• execute the anonymous block from above instead of just an insert and
• bind not only the INPUTS to the insert statement but bind the output "r"
• to get the rowid back out end

so, in o8 maybe you can bind and execute the anonymous block:

declare
  r rowid;
begin
  insert into t (a,b,c) values(:a,:b,:c) returning rowid into r;   :host_rowid := r;
end;

instead of just
  insert into t (a,b,c) values(:a,:b,:c);

as you would have in o7...     

Thomas Kyte
tkyte_at_us.oracle.com
Oracle Government
Bethesda MD  

http://govt.us.oracle.com/ -- downloadable utilities  

---

Opinions are mine and do not necessarily reflect those of Oracle Corporation  

Anti-Anti Spam Msg: if you want an answer emailed to you, you have to make it easy to get email to you. Any bounced email will be treated the same way i treat SPAM-- I delete it. Received on Thu Jan 15 1998 - 00:00:00 CST