Re: Using Value of OUT Parameter

Hi David,

when is lId initialized? It seems that it is null from the beginning of the session. You should have something like  

   if lId is null then lId:= 1; else
   lNextId := lId + lCount;
   end if;

Regards,

Martin  

David Burke wrote:
>
> 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;
Received on Mon Dec 24 2001 - 02:46:18 CST