Can't I call Open cursor in exception part of PL/SQL

[amolik]

Hi
Please look at following function. I am writing a function similar to below
When I pass the value which is existing in emp table then it returns me the proper values but when I pass value (for eid) which is not there in emp table then it does not return the values which are there in exception block.

if I run it independently (both queries ) it runs well . then why query in exception block is not running.
(The pack1.cursortype is an already declared packaged cursor)

create or replace function Listemp(eid in number)
return pack1.cursortype is
l_cursor pack1.cursortype;
iapno number;
begin
open l_cursor for
select empno, ename, job
from emp
where empno = eid;
return l_cursor;
exception when no_data_found then
open l_cursor for
select distinct 1 empno,'Smith' ename, 'Manager' job
from dual;
return l_cursor;
end;


Amol

[ales]

Hi,
the exception block in your function will never run because opening cursor doesn't raise NO_DATA_FOUND exception.

You can use the %FOUND and %NOTFOUND cursor attributes but never before the first FETCH.

Ales

[anandkl]

Try to use "cursor for loop"

cheese
anandkl

[amolik]

Hi all

The solution I found like this yesterday.
The Open cursor does not still work in Excpetion but if you call a different function altogether with same code and assing its output to the cursor then it works.

l_cursor:=CalledData(iapid);

Is there any simple solution?

Amol

[ales]

Originally posted by amolik
The Open cursor does not still work in Excpetion

Sure it does.
The only thing you must to do is to raise that exception:

scott@oracle> var c refcursor
scott@oracle> create or replace function Listemp(eid in number)
return pack1.cursortype is
  l_cursor pack1.cursortype;
  i number;
begin
  -- this DEFINITELY raises NO_DATA_FOUND
  SELECT 1 INTO i FROM dual WHERE 1=0;
  exception when NO_DATA_FOUND then
    open l_cursor for 
      select distinct 1 empno,'Smith' ename, 'Manager' job 
      from dual; 
  return l_cursor;
end;
/

Function created.

scott@oracle> exec :c:=Listemp(0);

PL/SQL procedure successfully completed.

scott@oracle> print c

     EMPNO ENAME JOB
---------- ----- -------
         1 Smith Manager

I'd suggest this adjustment of your function:

scott@oracle> var c refcursor
scott@oracle> create or replace function Listemp(eid in number)
return pack1.cursortype is
  l_cursor pack1.cursortype;
  l_eid number;
begin
  -- this checks for eid and eventually raises NO_DATA_FOUND
  SELECT empno INTO l_eid FROM emp WHERE empno=eid;
  open l_cursor for 
    select empno, ename, job 
    from emp 
    where empno = eid; 
  return l_cursor; 
exception when NO_DATA_FOUND then
  open l_cursor for 
    select distinct 1 empno,'Smith' ename, 'Manager' job 
    from dual; 
  return l_cursor;
end;
/

Function created.

scott@oracle> exec :c:=Listemp(0);

PL/SQL procedure successfully completed.

scott@oracle> print c

     EMPNO ENAME JOB
---------- ----- -------
         1 Smith Manager

scott@oracle> exec :c:=Listemp(7499)

PL/SQL procedure successfully completed.

scott@oracle> print c

     EMPNO ENAME      JOB
---------- ---------- ---------
      7499 ALLEN      SALESMAN

HTH,
Ales

[amolik]

Hi Ales

Thanks for your suggestions
Hwever.... How will it possible to use such a function when
it has to be used on Java Project with a web server?..I am
writing a similar function which will be called by a java servlet and I cannot declare a variable at their end.
Now the things are working with me when I am calling the exception part from another function. However personally I didnt favour this method as it adds one more function and since I am going to use cursor at many places so many of functions also will be added.
I am sure there must be other way out.
Can anybody suggest a way out based on my example.

Amol

[amolik]

Anybody knows hoe to return Null in the
exception block of the open cursor.
I want the calling function to return Null values but should not raise the exception "Function Returned without values"
Handling No_data_found is not solving the problem

Amol