From alex_hillman@physia.com Wed, 31 Jan 2001 14:52:17 -0800
From: Alex Hillman <alex_hillman@physia.com>
Date: Wed, 31 Jan 2001 14:52:17 -0800
Subject: RE: Does Oracle Allow Retrieval of Parsed Insert Statement??
Message-ID: <F001.002A67BC.20010131134858@fatcity.com>
MIME-Version: 1.0
Content-Type: text/plain
Title: RE: Does Oracle Allow Retrieval of Parsed Insert Statement??



You do 
not need OCI to do it. Returning supported everywhere one can use 
SQL.
<SPAN 
class=562413821-31012001> 
Alex 
Hillman

  <FONT face=Tahoma 
  size=2>-----Original Message-----From: Jacques Kilchoer 
  [mailto:Jacques.Kilchoer@quest.com]Sent: Wednesday, January 31, 
  2001 1:57 PMTo: Multiple recipients of list 
  ORACLE-LSubject: RE: Does Oracle Allow Retrieval of Parsed Insert 
  Statement??
  > -----Original Message----- > 
  From: Sam Bootsma [<A 
  href="mailto:SamB@cpas.com">mailto:SamB@cpas.com] <FONT 
  size=2>> Sent: mercredi, 31. janvier 2001 10:31 <FONT 
  size=2>> To: Multiple recipients of list ORACLE-L <FONT 
  size=2>> Subject: Does Oracle Allow Retrieval of Parsed Insert 
  Statement?? > > 
  > We have a situation where we insert rows into a 
  table, without having > knowledge of the primary 
  key.  One of our developers needs to > 
  determine the > rowid of such a row (primary key 
  unknown) immediately after the row is > inserted 
  into the  table.  > <FONT 
  size=2>> Does anybody know if the rowid can be retrieved (or somehow 
  returned) > immediately after the row is inserted 
  (without requerying the table)? 
  From Oracle Call Interface Programmer's Guide <FONT 
  size=2>Release 8.1.5 A67846-01 <FONT 
  size=2>Chapter 5 Binding and Defining 
  <<DML with RETURNING Clause The 
  OCI supports the use of the RETURNING clause with SQL INSERT, UPDATE, and 
  DELETE statements. This section outlines the rules an OCI application must 
  follow to correctly implement DML statements with the RETURNING clause. 
  
  Note: For more information about the use of the RETURNING 
  clause with INSERT, UPDATE, or DELETE statements, please refer to the 
  descriptions of those commands in the Oracle8i SQL Reference. 
  For a complete code example, see the demonstration programs 
  included with your Oracle installation. For additional information, refer to 
  Appendix B, "OCI Demonstration Programs". 
  Using DML with RETURNING Clause Using 
  the RETURNING clause with a DML statement allows you to essentially combine 
  two SQL statements into one, possibly saving you a server round-trip. This is 
  accomplished by adding an extra clause to the traditional UPDATE, INSERT, and 
  DELETE statements. The extra clause effectively adds a query to the DML 
  statement. 
  In the OCI, the values are returned to the application through 
  the use of OUT bind variables. The rules for binding these variables are 
  described in the next section. In the following examples, the bind variables 
  are indicated by the preceding colon, such as :out1. These examples assume the 
  existence of a table called table1, which contains three columns: col1, col2, 
  and col3. 
  For example, the following statement inserts new values into 
  the database and then retrieves the column values of the affected row from the 
  database, allowing your application to work with inserted rows. 
  INSERT INTO table1 VALUES (:1, :2, :3,) <FONT 
  size=2>     RETURNING col1, col2, col3 <FONT 
  size=2>     INTO :out1, :out2, :out3 <FONT 
  size=2>>> 
  ------ any ignorant comments made are 
  the sole responsibility of J. R. Kilchoer and should not reflect adversely 
  upon my employer.
    Jacques R. Kilchoer 
  (949) 754-8816 Quest Software, 
  Inc. 8001 Irvine Center Drive <FONT 
  size=2>Irvine, California 92618 U.S.A. 
  <A href="http://www.quest.com" 
  target=_blank>http://www.quest.com