-
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
-
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
-
Try to use "cursor for loop"
cheese
anandkl
anandkl
-
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
-
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:
Code:
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:
Code:
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
-
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
-
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
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|