Problems with INSERT ... RETURNING

From: Edward Rusu <erusu_at_softcomputer.com>
Date: Fri, 16 Oct 1998 19:04:34 -0400
Message-ID: <708jok$np7$1@scream.jriver.com>

        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?

Sincerely yours.

Edward Received on Fri Oct 16 1998 - 18:04:34 CDT