RETURNING INTO Clause (Insert)
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 2 of 2

Thread: RETURNING INTO Clause (Insert)

  1. #1
    Join Date
    Sep 2001
    Location
    Atlanta, GA
    Posts
    7

    Question

    I am attempting to optimize a very large script and came discovered the RETURNING INTO clause.

    I was attempting to test it thus;

    DECLARE
    tempvar VARCHAR2(50);
    BEGIN
    INSERT INTO Garbage( Garbage1, Garbage2 )
    ( SELECT 'test3', TestID
    FROM Test
    WHERE TestID = 9052 )
    RETURNING Garbage1 INTO tempvar;

    DBMS_OUTPUT.PUT_LINE(tempvar);
    END;
    /

    Garbage1 and Garbage2 are both VarChar2(21)'s. The TestID exists as a unique ID (only one row is returned).

    Whenever I run this script, modified many different ways, I get; "ORA-00933: SQL command not properly ended"

    What I actually wanted to test was;

    DECLARE
    tempvar VARCHAR2(50);
    BEGIN
    INSERT INTO Garbage( Garbage1, Garbage2 )
    ( SELECT 'test3', TestID, TestName
    FROM Test
    WHERE TestID = 9052 )
    RETURNING TestName INTO tempvar;

    DBMS_OUTPUT.PUT_LINE(tempvar);
    END;
    /

    I wanted to see if I could return from the Select statement or if I had to return from the INTO table.

    I am accessing an Oracle 9i database using an Oracle 8i Client (apparently there is no Oracle 9i Client according to our DBA).

    Thanks for your insite.
    CMerc

  2. #2
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    returning into only works with VALUES, for example

    insert into xxx values(1, (select yy from zz where a=x))

    this would work, the problem with subquery is that you can get more than one row back and since a variable is not an array therefore this restriction

Posting Permissions

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



Click Here to Expand Forum to Full Width