Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Copy a row with new id and return the new id

940095May 28 2012 — edited May 28 2012
As the subject says I need to copy a row, change some values, one of which is the id and return that id for use in my program.

I have tried something like the following, which works:

INSERT INTO table (id, text)
SELECT seq.nextval, text
FROM table WHERE id = :id

but when I try to add a RETURNING statement like so:

INSERT INTO table (id, text)
SELECT seq.nextval, text
FROM table WHERE id = :id
RETURNING id INTO :outValue

I get "ORA-00933: SQL command not properly ended". I have tried adding parenthesis around the select statement to no avail. Can anyone help me either figure out what I'm doing wrong or suggest an alternative to how it could be done? I don't know if it makes any difference but I cannot use stored procedures.
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Jun 25 2012
Added on May 28 2012
12 comments
4,029 views