Re: Using Value of OUT Parameter

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 Tue Dec 18 2001 - 09:03:12 CST