Re: Please help converting from Sybase to Oracle - Retunring results

Look up ref cursor. About every couple of days someone asks this question. See docs for ref cursor or search this ng for it. Jim
"jw" <jw21604_at_newsguy.com> wrote in message news:9nbccl02o2p_at_drn.newsguy.com...
> I'm not sure how to get results back from a stored procedure on Oracle. If
 these
> Sybase SPs:
>
> ------------------------------------------------------------------
> create proc GetAccounts
> _at_UserID char(8)
> as
> begin
>
> declare _at_cnt int
> select _at_cnt = count(*)
> from UserAccount
> where UserID = _at_UserID
>
> if _at_cnt > 0
> begin
> select AccountMnemonic
> from UserAccount
> where UserID = _at_UserID
> end
>
> end
> ------------------------------------------------------------------------
>
> the other is:
>
> create proc ValidateUser
> _at_USERID char(8)
> as
> begin
>
> declare _at_cnt int
> declare _at_mesg char(20)
>
> select _at_cnt = count(*) from PTSUser
> where UserID = _at_USERID
>
> if _at_cnt = 0
> begin
> select _at_mesg = "User Not Found"
> select _at_mesg
> return
> end
>
> select _at_mesg = "Found the User"
> select _at_mesg
>
> end
>
> ------------------------------------------------------------------------
>
> These procdures are called from a Java program and in sybase work jus find
> returning a results but I can't figure out the syntax in Oracle to just
 return a
> result set.
>
> Thanks IN Advance
>
> jw
>
Received on Sat Sep 08 2001 - 00:10:32 CEST