Re: PL/SQL 8.1.6 : RETURNING CLAUSE problems [REVISED]

From: Lachlan Pitts <Lachlan_Pitts_at_softworks.com.au>
Date: 2000/07/20
Message-ID: <3976c3b4@grissom>#1/1

Sorry people,

Give all relevant information before asking questions.... good motto to live by... :)

I couldn't include proprietary code in my post so I just made up an example on the fly...and consequently got it wrong...

What follows is my test script....

• INSERTED CODE BEGINS --- -- Drop table DROP TABLE T_TMP_RETURNING; / -- Create table CREATE TABLE T_TMP_RETURNING ( K_KEY NUMBER NOT NULL, N_VALUE NUMBER ); /
  • Create/Recreate primary, unique and foreign key constraints ALTER TABLE T_TMP_RETURNING ADD CONSTRAINT PK_TMP_RETURNING PRIMARY KEY (K_KEY); /
  • Drop sequence DROP SEQUENCE SEQ_TMP_RETURNING; /
  • Create sequence CREATE SEQUENCE SEQ_TMP_RETURNING START WITH 1; /

CREATE OR REPLACE
PACKAGE tstRETURNING
IS

• Author : LACHLAN PITTS
• Created : 20/07/2000 6:37:51 PM
• Purpose : Test semantics of RETURNING CLAUSE

PROCEDURE tstProc
(

    pnValue IN NUMBER,
    pnoKey OUT NUMBER
);

END tstRETURNING;
/

CREATE OR REPLACE
PACKAGE BODY tstRETURNING
IS

• Author : LACHLAN PITTS
• Created : 20/07/2000 6:37:51 PM
• Purpose : Test semantics of RETURNING CLAUSE

PROCEDURE tstProc
(

    pnValue IN NUMBER,
    pnoKey OUT NUMBER
)
IS
BEGIN
    INSERT INTO T_TMP_RETURNING ( K_KEY, N_VALUE )     SELECT SEQ_TMP_RETURNING.NEXTVAL, pnValue     FROM DUAL
    RETURNING K_KEY INTO pnoKey;
END tstProc;

END tstRETURNING;
/
SHOW ERRORS;
/
DECLARE
    var NUMBER;
BEGIN
    tstRETURNING.tstPROC( 100, var );
END;
/

• INSERTED CODE ENDS ---

Note on 8.1.6 on Microsoft Advanced Server 2000 it reports "SQL command not properly ended";

I assumed that it would be a supported concept in 8.1.6 - am I wrong? (Obviously only returning the LAST inserted value into the variable)

Does anyone know? Any work arounds?
How about capacity for bulk-binds - ie. get a collection of the returned / inserted keys?

Lachlan Pitts

"Jacques Desmazieres" <nospam.jacques.desmazieres_at_is2france.com> wrote in message news:Ybxd5.484$IO2.2148379_at_nnrp1.proxad.net...
> First there is a syntax error in this function: you have to specify the
> return code in the function prototype, or you should use a procedure
 instead

>

> Should be

>


> PROCEDURE domainWriteQuery

>                     (

>                         pnValue IN NUMBER,

>                         pnNewKey OUT NUMBER )

> IS
> BEGIN
> INSERT INTO T_TABLE( K_KEY, V_VALUE )
> VALUES ( SEQ_TABLE.NEXTVAL, pnValue )
> RETURNING K_KEY INTO pnNewKey;
> END domainWriteQuery;

>

> or

>


> FUNCTION domainWriteQuery

>                     (

>                         pnValue IN NUMBER

>                     )

> RETURN NUMBER
> IS
> BEGIN
> INSERT INTO T_TABLE( K_KEY, V_VALUE )
> VALUES ( SEQ_TABLE.NEXTVAL, pnValue )
> RETURNING K_KEY INTO pnNewKey;

>


>     RETURN pnNewKey;

> END domainWriteQuery;

>
>

> "Lachlan Pitts" <Lachlan_Pitts_at_softworks.com.au> wrote in message
> news:3976492d_at_grissom...
> > G'day people,
> >
> > New problem for a new day....
> >
> > Any ideas what is wrong with this picture?
> > It compiles but at runtime complains of "SQL Statement not ended";
> >
> > FUNCTION domainWriteQuery
> > (
> > pnValue IN NUMBER,
> > pnNewKey OUT NUMBER )
> > IS
> > BEGIN
> > INSERT INTO T_TABLE( K_KEY, V_VALUE )
> > VALUES ( SEQ_TABLE.NEXTVAL, pnValue )
> > RETURNING K_KEY INTO pnNewKey;
> > END domainWriteQuery;
> >
> > Thanks in advance,
> >
> > Lachlan Pitts
> >
> >
> >

>
> Received on Thu Jul 20 2000 - 00:00:00 CDT