Can't I call Open cursor in exception part of PL/SQL
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 7 of 7

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

  1. #1
    Join Date
    Dec 2000
    Posts
    255
    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

  2. #2
    Join Date
    Apr 2001
    Location
    Czechia
    Posts
    712
    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

  3. #3
    Join Date
    Aug 2001
    Location
    chennai,bangalore
    Posts
    840
    Try to use "cursor for loop"

    cheese
    anandkl
    anandkl

  4. #4
    Join Date
    Dec 2000
    Posts
    255
    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

  5. #5
    Join Date
    Apr 2001
    Location
    Czechia
    Posts
    712
    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



  6. #6
    Join Date
    Dec 2000
    Posts
    255
    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

  7. #7
    Join Date
    Dec 2000
    Posts
    255
    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
  •  


Click Here to Expand Forum to Full Width