This discussion is archived
14 Replies Latest reply: Jun 28, 2012 8:16 AM by Venkadesh Raja RSS

OPEN out_cur FOR        SELECT  exception handling help

user4485803 Newbie
Currently Being Moderated
Here is the stored procedure for reading a data based on the input, if no data then return null ref cursor and proper error message.
I am not sure, i am handling proper exception handling ? Please help me to complete this item.
Thanks.


PROCEDURE testing
(
module IN VARCHAR2,
module_id IN VARCHAR2,
out_cur OUT SYS_REFCURSOR,
out_error_no OUT NUMBER
)


BEGIN
out_error_no := 0;



CASE
WHEN module = 'a' AND module_id = 'b' THEN
BEGIN

OPEN out_cur FOR
SELECT id,
mime_type,
file_length,
file_name ,
uploadeddate,
created_user ,
status_name
FROM l_table_cnt
WHERE id = module_id;



EXCEPTION
WHEN OTHERS THEN

OPEN out_cur_file_cursor FOR
SELECT
NULL id,
NULL mime_type,
NULL file_length,
NULL file_name,
NULL uploadeddate,
NULL created_user,
NULL status_name
FROM dual
WHERE 1= 0;

out_error_no := 2;
RAISE_APPLICATION_ERROR(-20024,'No Document ');
END;
  • 1. Re: OPEN out_cur FOR        SELECT  exception handling help
    sb92075 Guru
    Currently Being Moderated
    user4485803 wrote:
    Here is the stored procedure for reading a data based on the input, if no data then return null ref cursor and proper error message.
    I am not sure, i am handling proper exception handling ? Please help me to complete this item.
    Thanks.


    PROCEDURE testing
    (
    module IN VARCHAR2,
    module_id IN VARCHAR2,
    out_cur OUT SYS_REFCURSOR,
    out_error_no OUT NUMBER
    )


    BEGIN
    out_error_no := 0;



    CASE
    WHEN module = 'a' AND module_id = 'b' THEN
    BEGIN

    OPEN out_cur FOR
    SELECT id,
    mime_type,
    file_length,
    file_name ,
    uploadeddate,
    created_user ,
    status_name
    FROM l_table_cnt
    WHERE id = module_id;



    EXCEPTION
    WHEN OTHERS THEN

    OPEN out_cur_file_cursor FOR
    SELECT
    NULL id,
    NULL mime_type,
    NULL file_length,
    NULL file_name,
    NULL uploadeddate,
    NULL created_user,
    NULL status_name
    FROM dual
    WHERE 1= 0;
    What is the purposed of statement immediately above?
  • 2. Re: OPEN out_cur FOR        SELECT  exception handling help
    user4485803 Newbie
    Currently Being Moderated
    it is a Read call.
    Based on the input parameter, we want to return null cursor if no data found with error message .
  • 3. Re: OPEN out_cur FOR        SELECT  exception handling help
    sb92075 Guru
    Currently Being Moderated
    user4485803 wrote:
    it is a Read call.
    Based on the input parameter, we want to return null cursor if no data found with error message .
    cursor below is NOT being returned!
    OPEN out_cur_file_cursor FOR
  • 4. Re: OPEN out_cur FOR        SELECT  exception handling help
    Venkadesh Raja Pro
    Currently Being Moderated
    try this
    Untested.
    PROCEDURE TESTING
    (
    MODULE IN VARCHAR2,
    MODULE_ID IN VARCHAR2,
    OUT_CUR OUT SYS_REFCURSOR,
    OUT_ERROR_NO OUT NUMBER
    )
    
    BEGIN
    OUT_ERROR_NO := 0;
    CASE
    WHEN MODULE = 'A' AND MODULE_ID = 'B' THEN
    CURSOR C IS SELECT ID,
    MIME_TYPE, 
    FILE_LENGTH,
    FILE_NAME , 
    UPLOADEDDATE,
    CREATED_USER ,
    STATUS_NAME
    FROM L_TABLE_CNT
    WHERE ID = MODULE_ID;
    CURSOR D IS SELECT
    NULL ID,
    NULL MIME_TYPE,
    NULL FILE_LENGTH,
    NULL FILE_NAME,
    NULL UPLOADEDDATE,
    NULL CREATED_USER,
    NULL STATUS_NAME
    FROM DUAL
    WHERE 1= 0;
    V_NO_DATA_FOUND BOOLEAN:=TRUE;
    BEGIN
    FOR I IN C LOOP
    V_NO_DATA_FOUND := FALSE;
    /*YOUR STUFF*/
    END LOOP;
    IF V_NO_DATA_FOUND THEN
    FOR J IN D LOOP
    /*YOUR STUFF*/
    OUT_ERROR_NO := 2;
    RAISE_APPLICATION_ERROR(-20024,'NO DOCUMENT ');
    END LOOP;
    END IF;
    END;
    But 2nd select query should be return 0 rows
    because of
    WHERE 1= 0;
    i don't understand why you want to use that select instead of just printing error message

    Venkadesh
  • 5. Re: OPEN out_cur FOR        SELECT  exception handling help
    user4485803 Newbie
    Currently Being Moderated
    the code which has posted is working fine but just want to make sure exception handling is proper. Any thoughts?
    thanks.
  • 6. Re: OPEN out_cur FOR        SELECT  exception handling help
    6363 Guru
    Currently Being Moderated
    user4485803 wrote:
    the code which has posted is working fine but just want to make sure exception handling is proper. Any thoughts?
    You should remove the exception handling as it is not doing anything.

    Since the procedure never fetches from the cursor, it will never throw an exception or be able to determine how many rows the cursor will fetch or whether it will fetch any rows.
  • 7. Re: OPEN out_cur FOR        SELECT  exception handling help
    BluShadow Guru Moderator
    Currently Being Moderated
    If you are expecting an exception to get raised because there is no data returned from the query then you do not understand ref cursors.
    No data is being fetched, as all you are doing is opening the ref cursors, so there won't be a NO_DATA_FOUND exception (and you certainly shouldn't be using an OTHERS exception).

    I think you must be thinking that by opening a ref cursor you are fetching a set of data. Wrong.

    {thread:id=886365}
  • 8. Re: OPEN out_cur FOR        SELECT  exception handling help
    Venkadesh Raja Pro
    Currently Being Moderated
    dude.
    the code which has posted is working fine but just want to make sure exception handling is proper?
    no.using for loop doesn't return no_data_found error you can use my code.


    see here eg:
    begin
    for i in (select ename from emp where empno=1234)
    loop
    dbms_output.put_line(i.ename);
    end loop;
    end;
    here no data contains empno=1234 but this code doesn't return any no_data found error.
    DECLARE
      CURSOR C IS SELECT ENAME,SAL FROM emp where empno=1234;
      V_ENAME C%ROWTYPE;
      V_NO_DATA_FOUND BOOLEAN := TRUE;
    BEGIN
      FOR I IN C LOOP
          V_NO_DATA_FOUND := FALSE;
          DBMS_OUTPUT.PUT_LINE(I.ENAME);
      END LOOP;
      
      IF V_NO_DATA_FOUND THEN
        DBMS_OUTPUT.PUT_LINE('NO DATA');
      END IF;
    END;
    
    
    
    NO DATA
    here no_data return



    Venkadesh

    Edited by: Venkadesh on Jun 28, 2012 7:49 AM
  • 9. Re: OPEN out_cur FOR        SELECT  exception handling help
    BluShadow Guru Moderator
    Currently Being Moderated
    Venkadesh wrote:
    dude.
    the code which has posted is working fine but just want to make sure exception handling is proper?
    no.using for loop doesn't return no_data_found error you can use my code.
    Erm... but your code doesn't pass out a ref cursor, and it's already looped through the data. Sort of defeats the purpose of having a procedure pass out a ref cursor don't ya think?
  • 10. Re: OPEN out_cur FOR        SELECT  exception handling help
    Venkadesh Raja Pro
    Currently Being Moderated
    Erm... but your code doesn't pass out a ref cursor, and it's already looped through the data. Sort of defeats the purpose >of having a procedure pass out a ref cursor don't ya think?
    Ya its checking first having data or not :)..my intention is return NO_DATA_FOUND
  • 11. Re: OPEN out_cur FOR        SELECT  exception handling help
    BluShadow Guru Moderator
    Currently Being Moderated
    Venkadesh wrote:
    Erm... but your code doesn't pass out a ref cursor, and it's already looped through the data. Sort of defeats the purpose >of having a procedure pass out a ref cursor don't ya think?
    Ya its checking first having data or not :)..my intention is return NO_DATA_FOUND
    The correct way is to just open the ref cursor and pass it back and then the receiving code that is going to use that cursor handles whether there is any code in it or not. It doesn't make sense to request a ref cursor and and have the code which creates the cursor also tell you if there's data in it or not. It's an additional overhead that isn't necessary if the requesting code is written correctly.
  • 12. Re: OPEN out_cur FOR        SELECT  exception handling help
    Venkadesh Raja Pro
    Currently Being Moderated
    The correct way is to just open the ref cursor and pass it back and then the receiving code that is going to use that cursor handles whether there is any code in it or not.
    can you please explain with simple example
  • 13. Re: OPEN out_cur FOR        SELECT  exception handling help
    BluShadow Guru Moderator
    Currently Being Moderated
    Venkadesh wrote:
    The correct way is to just open the ref cursor and pass it back and then the receiving code that is going to use that cursor handles whether there is any code in it or not.
    can you please explain with simple example
    Is it really that difficult?

    Ok...

    Here's the procedure to return a ref cursor...
    SQL> ed
    Wrote file afiedt.buf
    
      1  create or replace procedure get_rc(p_deptno in number, p_rc out sys_refcursor) is
      2  begin
      3    open p_rc for 'select * from emp where deptno = :1' using p_deptno;
      4* end;
    SQL> /
    
    Procedure created.
    Now we have some application that wants to consume a ref cursor... in this case the application is SQL*Plus, but it could be Java or .NET etc.

    It declares it's local reference to the ref cursor...
    SQL> var r refcursor;
    then calls the procedure to get a ref cursor reference assigned...
    SQL> exec get_rc(10, :r);
    
    PL/SQL procedure successfully completed.
    Now, the application itself determines if there is any data when it comes to actually perform the fetches on it...
    SQL> print r;
    
         EMPNO ENAME      JOB              MGR HIREDATE                    SAL       COMM     DEPTNO
    ---------- ---------- --------- ---------- -------------------- ---------- ---------- ----------
          7782 CLARK      MANAGER         7839 09-JUN-1981 00:00:00       2450                    10
          7839 KING       PRESIDENT            17-NOV-1981 00:00:00       5000                    10
          7934 MILLER     CLERK           7782 23-JAN-1982 00:00:00       1300                    10
    in the above case it had data, so it displayed it.

    So what if there isn't any data...
    SQL> exec get_rc(90, :r);
    
    PL/SQL procedure successfully completed.
    
    SQL> print r;
    
    no rows selected
    
    SQL>
    SQL*Plus (the application that calls the procedure) is the one that has determined that there was not data when it came to fetch it (using the print statement in this case). And when it found there was no data it handled it itself (in this case printing the message "no rows returned").

    The procedure doesn't have to do any overhead of determining if there is data going to be returned or not, because it's not it's responsibility and completely unnecessary. The calling application can easily determine if there is data or not when it starts to try and fetch it.
  • 14. Re: OPEN out_cur FOR        SELECT  exception handling help
    Venkadesh Raja Pro
    Currently Being Moderated
    Thanks a lot BluShadow for make sense :) .

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points