This discussion is archived
5 Replies Latest reply: Jan 7, 2010 12:16 PM by 319958 RSS

Exception handling for array binding

525979 Newbie
Currently Being Moderated
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 Explorer
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Explorer
    Currently Being Moderated
    can you post a snippet of the code in question?
  • 4. Re: Exception handling for array binding
    525979 Newbie
    Currently Being Moderated
    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 Explorer
    Currently Being Moderated
    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.

Legend

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