Re: Using Value of OUT Parameter

dtburke_at_software.rockwell.com (David Burke) wrote in message news:<87685036.0112180703.42570bb4_at_posting.google.com>...
> ganesh_at_gtfs-gulf.com (Ganesh Raja) wrote in message news:<a8aed4.0112172137.7085de9f_at_posting.google.com>...
> > dtburke_at_software.rockwell.com (David Burke) wrote in message news:<87685036.0112171104.1a21207e_at_posting.google.com>...
> > > I am Executing a Stored Proc. (with an OUT value) within a Stored
> > > Proc.
> > > I need to Get the Value of the OUT parameter, from the First Proc.,
> > > for an INSERT statement in the Other Proc.
> > > Code Snippet:
> > >
> > > BEGIN
> > > DECLARE lSeqId;
> > > BEGIN
> > > lSeqId := 1;
> > >
> > > /*This procedure takes an input and returns a new SequenceId*/
> > > spGetSeqNumber('TableName',lSeqId);
> >
> > What are u doing in this procedure... Check that Logic.
> >
> >
> > > INSERT INTO MyTable(lId, sName) VALUES (lSeqId, 'Smith');
> > >
> > > END;
> > >
> > > END;
> > >
> > > Why is lSeqId == NULL when the INSERT is executed?
> >
> > Regards,
> > Ganesh R
>
> Here is the Logic (BTW, I just started writing Oracle sps, so I have
> much to learn :o)):
> CREATE OR REPLACE PROCEDURE spOnyxGetSequenceNumber
> (
> strTableName IN varchar2,
> lOutId OUT int,
> lCount IN int:=1
> )
> AS
> lId int;
> lNextId int;
>
> CURSOR MyCursor IS SELECT lSequence FROM tblSysSequence WHERE
> sTableName = strTableName FOR UPDATE;
>
> BEGIN
> OPEN MyCursor;
> FETCH MyCursor INTO lId;
>
> lNextId := lId + lCount;
>
> UPDATE tblSysSequence SET lSequence = lNextId WHERE sTableName =
> strTableName;
>
> lOutId := lNextId;
>
> CLOSE MyCursor;
> COMMIT;
>
> END;
in SQLPlus

Variable x Number
Exec spOnyxGetSequenceNumber('tablename',:x); Print x

U will knwo what is returned then u can procedd to correct the progtram. My Guess is that that fetch is returning a Null so u'r gettiong a NULL.

My Two cents :

Dont give Commit inside the proc.. Give Commit Only if u'r Transaction is over and not after a Update/Delete or Insert.

HTH regards,
Ganesh R Received on Wed Dec 19 2001 - 01:31:34 CST