-
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
-
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|