Re: Beginner's question on Oracle stored procedure/function---HELP!!

From: <vijaylamba_at_my-deja.com>
Date: Sat, 04 Dec 1999 12:16:14 GMT
Message-ID: <82b0me$qli$1@nnrp1.deja.com>

hi dana,
1. A function can return more than one value and as u said using the OUT parameter but oracle suggests that if more than one value is required to be returned then use a procedure instead. Moreover if you are calling this function(which returns more than one row) from another procedure or function then it makes more sense. Otherwise try using a procedure with OUT parameters.
Example:
Function FN_BATCH_ID(pFeedid IN NUMBER,

                     pBatchDescription IN VARCHAR2,
                     lErr OUT VARCHAR2)
  RETURN NUMBER IS
   lBatchId                 NUMBER;

BEGIN
   SELECT Batch_Id
   INTO lBatchId
   FROM Batches_tmp
   WHERE Filename = pBatchDescription
   RETURN lBatchId ;
   lErr := NULL;
EXCEPTION
   WHEN NO_DATA_FOUND THEN

      lErr := 'Invalid Batch';
      lBatchId := NULL;

   RETURN lBatchId ;
END; in this case if select statement finds the data, lErr (OUT) will return NULL and function returns lBatchId
if select fails then lErr will return 'Invalid Batch' and function returns lBatchId(in this case NULL).

2. As far as I know a Function cannot return a record set.

I hope I was of some help to u.
All the best
Vijay
In article <384822d9$0$80466_at_news.choice.net>,   "Dana Jian" <djian_at_trasa.com> wrote:
> Hello,
>
> I'm a new user of PL/SQL in Oracle8, I have two questions as
following:
>
> 1) Can function in Oracle server return more than one value? how?
>
> I was trying to use OUT type of parameters in a function on the Oracle
> server, but I got NULL(VB program as front-end) returned from that
function.
>
> Anyone knows the reason?
>
> 2) Can function in Oracle server return a recordset--multiple rows
with
> multiple columns? How??
>
> I really appreciate the help!!!
>
> Dana Jian
> dan_jian_at_hotmail.com
>
>

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Sat Dec 04 1999 - 06:16:14 CST