RE: Does Oracle Allow Retrieval of Parsed Insert Statement??

From: Alex Hillman <alex_hillman_at_physia.com>
Date: Wed, 31 Jan 2001 14:52:17 -0800
Message-ID: <F001.002A67BC.20010131134858@fatcity.com>

You do
not need OCI to do it. Returning supported everywhere one can use SQL.

Alex
Hillman

-----Original Message-----From: Jacques Kilchoer [mailto:Jacques.Kilchoer_at_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_at_cpas.com">mailto:SamB_at_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. > > 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 Release 8.1.5 A67846-01 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 Irvine, California 92618 U.S.A. <A href="http://www.quest.com" target=_blank>http://www.quest.com

Received on Wed Jan 31 2001 - 16:52:17 CST