14 Replies Latest reply: Jun 28, 2012 10:16 AM by Venkadesh Raja RSS

    OPEN out_cur FOR        SELECT  exception handling help

    user4485803
      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
          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
            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
              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
                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
                  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
                    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
                      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
                        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
                          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
                            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
                              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
                                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
                                  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
                                    Thanks a lot BluShadow for make sense :) .