Re: rowid of inserted row

smartin_at_bluewhale.emergent.com (Scott Martin) wrote:
>In article <3pm47j$j3q_at_cantaloupe.srv.cs.cmu.edu>,
>Mark Hjelm <hjelm+_at_cs.cmu.edu> wrote:
>>
>>I may be overlooking something obvious, but is there a direct way to
>>obtain the rowid of a row I've just inserted into a table? I can
>>think of several indirect ways, but I'd rather not have that much
>>overhead. I'm using ProC, but I'd gladly drop lower, if it would
>>help.
>>
>I may have an answer for you. Please refer to the following definition
>of a cda_def from $ORACLE_HOME/rdbms/demo/ocidfn.h
>
>/* the real CDA, padded to 64 bytes in size */
>struct cda_def {
> sb2 v2_rc; /* V2 return code */
> ub2 ft; /* SQL function type */
> ub4 rpc; /* rows processed count */
> ub2 peo; /* parse error offset */
> ub1 fc; /* OCI function code */
> ub1 rcs1; /* filler area */
> ub2 rc; /* V7 return code */
> ub1 wrn; /* warning flags */
> ub1 rcs2; /* reserved */
> sword rcs3; /* reserved */
> struct { /* rowid structure */
> struct {
> ub4 rcs4;
> ub2 rcs5;
> ub1 rcs6;
> } rd;
> ub4 rcs7;
> ub2 rcs8;
> } rid;
> sword ose; /* OSD dependent error */
> dvoid *rcsp; /* pointer to reserved area */
> ub1 rcs9[64 - sizeof (struct cda_head)]; /* filler to 64 */
>};
>
>The rowid structure "rid" may contain the rowid you are looking for in some
>combination of the fields rcs[4...8]. You are looking for three numbers,
>a block, a row, and a file. Frankly, I'm not sure why there are 5 variables
>but I'm pretty sure the answer lies within a combination of them.
>I used to work at Oracle in the RDBMS group and we put that hack in back
>in version 6 to help us win a benchmark by saving the very same effort you
>are trying to save.
>
>Hope I'm right,
>SCott.
>smartin_at_turnstone.com
>415-851-9576
>
>>Oracle 7.1.3, SunOS 4.1.3
>>
>>Mark Hjelm
>>mwh_at_formtek.com
>>hjelm_at_cs.cmu.edu

Mark,

You're right, but it is indeed a hack. I used to work at a 4GL company, and I had to use the ROWID in the Cursor Data Area to help us win a benchmark :-)

The ROWID in the CDA is a real nightmare. I remember that the data types and names of the fields and the name of the structures were platform dependent. On some platforms the .h file with the declarations was simply missing or incorrect (this was 7.0.13). With Pro*C on MS-Windows there was a weird patch, because of alignment differences with various compiler options.

The most important drawback of the ROWID in the CDA is that there is no documentation on the conversion from the fields in the structure (the internal ROWID) to an external ROWID. So you have the choice of re-engineering an undocumented conversion or using the internal ROWID (which apperantly is supported by SQL, but not by PL/SQL).

Good luck,

-Ren\'e Pijlman-                      _-^^-_
Rene.Pijlman_at_inter.NL.net           _- _ -  -_
                                   //   _ -  /
On no account allow a Vogon       || \  -  /\\
to read poetry at you.            ||  \___/ ||
- Hitchiker's Guide to the Galaxy ||       //|\
------------------------------------------------