Results 1 to 4 of 4
  1. #1
    Join Date
    Nov 2003
    Posts
    2

    Question Unanswered: insert...returning / select

    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 ??

  2. #2
    Join Date
    Oct 2003
    Location
    Germany - Stuttgart
    Posts
    14
    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

  3. #3
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: insert...returning / select

    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.

  4. #4
    Join Date
    Aug 2001
    Posts
    66

    Re: insert...returning / select

    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>
    Padderz
    SYSOP, Quest Software / RevealNet Labs PL/SQL Pipeline

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •