Copy a row with new id and return the new id
940095May 28 2012 — edited May 28 2012As 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.