| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.misc -> Re: Problems with INSERT ... RETURNING
A copy of this was sent to "Edward Rusu" <erusu_at_softcomputer.com>
(if that email address didn't require changing)
On Fri, 16 Oct 1998 19:04:34 -0400, you wrote:
> Hi omniscient ALL!
>
>I try to create a function like this:
>CREATE OR REPLACE PROCEDURE Insert_Person_( rec_ IN OUT PERSON_T%ROWTYPE )
>is
>BEGIN
> /* the value of some columns updated by trigger */
> INSERT INTO PERSON_T( ptlname,ptfname,ptmname,ptdob )
> VALUES( rec_.ptlname, rec_.ptfname, rec_.ptmname, rec_.ptdob )
> RETURNING * INTO rec_;
>END;
>/
>
>But Oracle shows a strange message, like:
>Errors for PROCEDURE INSERT_PERSON_:
>LINE/COL ERROR
>-------- -----------------------------------------------------------------
>3/2 PLS-00801: internal error [22004]
>3/2 PL/SQL: SQL Statement ignored
>
>I could resolve this problem only by enumerating all columns name instead of
>using '*' and ROWTYPE record.
>I mean that this is:
>CREATE OR REPLACE PROCEDURE Insert_Person_( rec_ IN OUT PERSON_T%ROWTYPE )
>is
>BEGIN
> /* the value of some columns updated by trigger */
> INSERT INTO PERSON_T( ptlname,ptfname,ptmname,ptdob )
> VALUES( rec_.ptlname, rec_.ptfname, rec_.ptmname, rec_.ptdob )
> RETURNING ptfname, ptlname, ptmname, ptdob, ptcreat
> INTO rec_.ptfname, rec_.ptlname, rec_.ptmname,rec_.ptdob,
>rec_.ptcreat;
>END;
>
>So the question is, Why Oracle's syntax allows using '*' in RETURNING, but
>it does not allow in real life? Maybe there is some others way of avoiding
>column enumerating?
>
the syntax does not allow for '*' in the returning nor for a structure in the INTO clause of the returning (you can only have an expression, '*' is not an expression as defined in chapter 3 of the lang ref. You can only have a data list for the into clause and that consists of scalars, not structures) -- thats the cause of the internal error.
It is a parse bug (i opened a bug for this, it repros in 8.0.3- 8.1.3)....
Instead of listing each column however, you could code:
declare
l_rowid rowid;
begin
insert into person_t( ... )
values ( ... ) returning rowid into l_rowid;
select * into rec_ from person_t where rowid = l_rowid; end;
>Sincerely yours.
>
>Edward
>
Thomas Kyte
tkyte_at_us.oracle.com
Oracle Government
Herndon VA
--
http://govt.us.oracle.com/ -- downloadable utilities
Anti-Anti Spam Msg: if you want an answer emailed to you, you have to make it easy to get email to you. Any bounced email will be treated the same way i treat SPAM-- I delete it. Received on Sat Oct 17 1998 - 09:25:24 CDT
 |
 |