5 Replies Latest reply: Jan 7, 2010 2:16 PM by 319958 RSS

    Exception handling for array binding

    525979
      Hi, I'm trying to resolve the following:

      1) I have PL/SQL stored procedure that is trying to insert passed data into a table. The insert should happened only after some data validation passed. We added user defined exception that is raised when data validation is failed;
      2) C# application, using ODP.NET, is calling stored procedure described in (1). The call on stored procedure is using array binding feature of ODP.NET. In case of exception we're processing Oracle ErrorCollection object to identify for which of passed values the stored procedure failed.

      If no exceptions were raised the stored procedure is executed the correct number of times. However, if user defined exception was raised the execution for the whole array is interrupted and control is passing back to the host application.

      Is this an expected behavior? Is there a way how to properly proceed with the described scenario?

      Thanks for the response.
        • 1. Re: Exception handling for array binding
          319958
          You'll need to encapsulate your validation within it's own block as described below:

          -- this will die on the first exception
          declare
            TYPE T_BADDATA_TEST IS TABLE OF VARCHAR2(1000) INDEX BY binary_integer ;
            tbt T_BADDATA_TEST ;
            aBadTypeFound exception ; 
          begin
             tbt(0) := 'a';
             tbt(1) := 'b';
             tbt(2) := 'c';
             
              for idx in tbt.first..tbt.last loop
                if tbt(idx) =  'b' then
                    raise aBadTypeFound ;      
                else
                    dbms_output.put_line(tbt(idx));      
                end if  ;
          
              end loop ;
          end ;
          --encapsulate the exception area in a begin/end block to handle the exception but continue on
          declare
            TYPE T_BADDATA_TEST IS TABLE OF VARCHAR2(1000) INDEX BY binary_integer ;
            tbt T_BADDATA_TEST ;
            aBadTypeFound exception ; 
          begin
             tbt(0) := 'a';
             tbt(1) := 'b';
             tbt(2) := 'c';
             
              for idx in tbt.first..tbt.last loop
                BEGIN
                if tbt(idx) =  'b' then
                    raise aBadTypeFound ;      
                else
                    dbms_output.put_line(tbt(idx));      
                end if  ;
          
                EXCEPTION
                  WHEN aBadTypeFound THEN
                      dbms_output.put_line(tbt(idx) || ' is bad data');        
                  WHEN OTHERS THEN
                      dbms_output.put_line('exception');        
                END ;
          
              end loop ;
          end ;
          
          
           /***
          output:
          a
          b is bad data
          c
          
          ***/
          • 2. Re: Exception handling for array binding
            525979
            Thanks for the attention to this, however, the question was not how to handle the user defined exception inside of PL/SQL but within C#, using bind array feature of ODP.NET
            • 3. Re: Exception handling for array binding
              319958
              can you post a snippet of the code in question?
              • 4. Re: Exception handling for array binding
                525979
                http://download.oracle.com/docs/html/B14164_01/featOraCommand.htm

                There is an example from Oracle doc. Check the "Error Handling for Array Binding" section. My original question is based on this topic.

                Thanks.
                • 5. Re: Exception handling for array binding
                  319958
                  is the exception being thrown in the Array Bind execution? Or are you looping through the collection and throwing the exception?
                  When an OracleException is thrown during an Array Bind execution, the OracleErrorCollection contains one or more OracleError objects.

                  with the looping mechanism you are going to need to catch the exception and handle it properly or the pl/sql app will spill the exception outwards thus terminating processing right then and there and only returning the final error.