Hi

I have a JSP/JDBC program that processes sql statements from other JSPs to an Oracle 8.16 database. I need to modify this program to retreive the data that was just inserted (from different tables, with different keys and different INSERT statement structures).

The best for me would be to perform a SELECT *... for the inserted record... but I just can't figure out how to retreive this record and it's becoming frustrating !!

I've been searching for a way to do this with PL/SQL 'INSERT... RETURNING...' but everything I found on the web isn't clear and i'm quite new to SQL and JDBC.

Could someone PLEASE clearly explain to me if it's possible and HOW... if not, is there any way I can ever achieve this without having to tear down the INSERT statement and build some sort of a SELECT statement out of it ??

Hi,

the syntax is

insert into table (column1,column2,column3)
values(1,2,3)
returning column1,column2 into variable1,variable2

but it doesn't work with multitable-inserts and it is really slowly (on 9i)
the faster way is

select primary_key from sequence.nextval into variable...
then insert with this pk an reselect the values inserted...

good luck

In general it shouldn't be necessary to SELECT to find out what you just inserted - you know what you just inserted! The only exceptions are values set by DEFAULT clauses or triggers.

It would be a good idea (good practice) to take the insert statements out of the JSP code and put them in PL/SQL packaged procedures. These procedures can then have OUT arguments to return the required data. However, I imagine that is a big change from where you are now.

I see no problem with the performance of INSERT RETURNING on 9i database. It appears to perform rather better that SELECT then INSERT even WITHOUT further SELECT to retrieve row.

Frankly I'm disappointed when I see people making assertions of this kind without any evidence. There's enough Oracle misconceptions floating around without adding to the steaming pile.

Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.1.0 - Production

SQL> CREATE TABLE table_name (column_name NUMBER (10));

Table created.

SQL> CREATE UNIQUE INDEX index_name ON table_name (column_name);

Index created.

SQL> CREATE SEQUENCE sequence_name INCREMENT BY 1 CACHE 10000;

Sequence created.

SQL> SET SERVEROUTPUT ON;
SQL> DECLARE
2 v_start_time INTEGER := 0;
3 v_column_name NUMBER (10);
4 v_sequence_no NUMBER (10);
5 v_iterations INTEGER := 10000;
6 BEGIN
7 DBMS_OUTPUT.PUT_LINE ('Case 1: SELECT then INSERT');
8 v_start_time := DBMS_UTILITY.GET_TIME;
9 FOR i IN 1..v_iterations LOOP
10 SELECT sequence_name.NEXTVAL
11 INTO v_sequence_no
12 FROM dual;
13 INSERT INTO table_name (column_name)
14 VALUES (v_sequence_no);
15 END LOOP;
16 DBMS_OUTPUT.PUT_LINE ('Hsecs: ' ||
17 (DBMS_UTILITY.GET_TIME - v_start_time));
18
19 DBMS_OUTPUT.PUT_LINE ('Case 2: INSERT RETURNING');
20 v_start_time := DBMS_UTILITY.GET_TIME;
21 FOR i IN 1..v_iterations LOOP
22 INSERT INTO table_name (column_name)
23 VALUES (sequence_name.NEXTVAL)
24 RETURNING column_name INTO v_column_name;
25 END LOOP;
26 DBMS_OUTPUT.PUT_LINE ('Hsecs: ' ||
27 (DBMS_UTILITY.GET_TIME - v_start_time));
28 END;
29 /
Case 1: SELECT then INSERT
Hsecs: 202
Case 2: INSERT RETURNING
Hsecs: 129

PL/SQL procedure successfully completed.

SQL> /
Case 1: SELECT then INSERT
Hsecs: 196
Case 2: INSERT RETURNING
Hsecs: 153

PL/SQL procedure successfully completed.

SQL> /
Case 1: SELECT then INSERT
Hsecs: 199
Case 2: INSERT RETURNING
Hsecs: 118

PL/SQL procedure successfully completed.

SQL>