> -----Original Message-----
> From: Sam Bootsma [mailto:[EMAIL PROTECTED]]
> Sent: mercredi, 31. janvier 2001 10:31
> To: Multiple recipients of list ORACLE-L
> 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,)
RETURNING col1, col2, col3
INTO :out1, :out2, :out3
>>
------
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.
http://www.quest.com
