Home » SQL & PL/SQL » SQL & PL/SQL » Return all errors on a screen from exception (Oracle 10g)
Return all errors on a screen from exception [message #394304] Thu, 26 March 2009 07:31 Go to next message
virmahi
Messages: 266
Registered: April 2008
Location: India
Senior Member
Hi,
I will be getting a record of data into a parking table. I have to validate the data fetched from Parking table and return the set of errors in a array so that the user can enter the correct data on the form again.

My requirement is that despite more than one error, the flow should continue till end and finally send the array of errors. But now once the first error is found the loop is exiting and I am not able to continue for the other errors.

Please advice as how can we validate all the conditions before exiting.
I am pasting a part of the code. The package and procedures are working fine but exiting only after first error :-
CREATE OR REPLACE PACKAGE pks_ams_upload AS

  TYPE ERROR_ARR IS VARRAY(66) OF VARCHAR2(100);
  TYPE c1 IS REF CURSOR;
 
   PROCEDURE sp_check_P1 (
     p_file_id      IN   VARCHAR2,
     p_flag         IN   VARCHAR2,
     p_session_id   IN   VARCHAR2,
     p_error_arr OUT pks_ams_upload.error_arr);

    end pks_ams_upload;


CREATE OR REPLACE PACKAGE BODY pks_ams_upload
IS
PROCEDURE sp_check_p1 (
      p_file_id      IN   VARCHAR2,
      p_flag         IN   VARCHAR2,
      p_session_id   IN   VARCHAR2,
      p_error_arr     OUT   pks_ams_upload.error_arr
   )
   IS
   
      l_error_arr pks_ams_upload.ERROR_ARR := pks_ams_upload.ERROR_ARR();

      c3        c1;
      c2        tmp_ams_ia_profil_parking%ROWTYPE;
      v_query   VARCHAR2 (500);
      
      v_appl_name         VARCHAR2(50);
      v_um_bp_code        VARCHAR2(50);
      v_agent_no          VARCHAR2(10);           
      v_unitname          VARCHAR2(50);
      v_source            VARCHAR2(50);
 c_status     CONSTANT CHAR(1)              := 'U';
-- user defined exceptions  
      app_null          EXCEPTION; 
      app_non_numeric   EXCEPTION; 
      app_size_more EXCEPTION;                                 
      app_future_date   EXCEPTION; 
      app_date_null     EXCEPTION; 
 BEGIN
      
      v_query :=
            'SELECT  *  FROM TMP_AMS_IA_PROFIL_PARKING  WHERE FILE_ID = '
         || p_file_id
         || ' AND session_id ='
         || p_session_id;
      OPEN c3 FOR v_query;
      LOOP
         BEGIN
            FETCH c3
             INTO c2;
            EXIT WHEN c3%NOTFOUND;
IF TRIM(c2.appl_no) IS NULL THEN
               RAISE app_null;               
            
          ELSIF is_number(TRIM(c2.appl_no)) = FALSE
            THEN
               RAISE app_non_numeric;               
            END IF;
                      
            IF Length(TRIM(c2.appl_no)) > 8 THEN
              RAISE app_size_more;      
            END IF;

            IF TRIM(c2.DATE_OF_APPL) IS NULL THEN
              RAISE app_date_null;   
            END IF;  
           
            IF c2.DATE_OF_APPL > SYSDATE THEN
              RAISE app_future_date;   
            END IF;   
 EXCEPTION
       WHEN app_null THEN
            v_error_index := v_error_index + 1;
            l_error_arr.extend;
            l_ERROR_ARR(v_error_index) := 'Application No cannot be NULL';
             
            WHEN app_non_numeric THEN
             
            v_error_index := v_error_index + 1;
            l_error_arr.extend;
            l_ERROR_ARR(v_error_index) := 'Application No should be Numeric...from array';
            dbms_output.put_line(l_ERROR_ARR(v_error_index));
           
            WHEN app_size_more THEN
            v_error_index := v_error_index + 1;
            l_error_arr.extend;
            l_ERROR_ARR(v_error_index) := 'Application No should be maximum 8 digit';
            
WHEN OTHERS
            THEN
               raise_application_error (-20001,
                                           'An error was encountered - '
                                        || SQLCODE
                                        || ' -ERROR-'
                                        || SQLERRM
                                       );
 END;
      END LOOP;
   END sp_check_p1;
END pks_ams_upload;   



I am getting the array returned for only the first error.

Regards,
Mahi
Re: Return all errors on a screen from exception [message #394312 is a reply to message #394304] Thu, 26 March 2009 07:57 Go to previous messageGo to next message
ehegagoka
Messages: 493
Registered: July 2005
Senior Member
Not sure if I understand it correctly, but looks like you're just logging the 'error message'. Why not just directly insert it to the array instead of raising it?



IF TRIM(c2.appl_no) IS NULL THEN
     -- RAISE app_null;                           
     l_error_arr.extend;
     l_ERROR_ARR(l_error_arr.last) := 'Application No cannot be NULL';




HTH.
Re: Return all errors on a screen from exception [message #394314 is a reply to message #394304] Thu, 26 March 2009 07:59 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
As you haven't posted the actual errors you got, I'll have to guess:

The error that you get back isn't one of the 3 you're catching. If you go into your WHEN OTHERS, then it will use Raise Application Error to raise an exception, which will terminate the loop processing and return to the calling process.

Your When others is uneccessary - the only thing it does is to remove information about the exception that happened.

Either accept that exceptions you weren't expecting should terminate the code, and remove the WHEN OTHERS, or change the logic in it to add another line to your error array, and remove the Raise_application_error call.
Re: Return all errors on a screen from exception [message #394316 is a reply to message #394304] Thu, 26 March 2009 07:59 Go to previous messageGo to next message
cookiemonster
Messages: 12422
Registered: September 2008
Location: Rainy Manchester
Senior Member
There's no code in your example to populate p_error_arr.
Re: Return all errors on a screen from exception [message #394317 is a reply to message #394312] Thu, 26 March 2009 08:03 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
@ehegagoka's suggestion is a better approach - that way you would return all the problems with each line, rather than simply the first problem with each line.
Re: Return all errors on a screen from exception [message #394324 is a reply to message #394317] Thu, 26 March 2009 08:17 Go to previous messageGo to next message
virmahi
Messages: 266
Registered: April 2008
Location: India
Senior Member
Thanks all of you for your suggestions.
I am not getting any error but want to only add all errors to the array.
The idea of ehegagoka is great.
I implement it for some. But the problem of termination of the procedure remains same. Whenever there is no data found then it terminates then it comes only with the first error.

I have removed the when others too.
Re: Return all errors on a screen from exception [message #394333 is a reply to message #394324] Thu, 26 March 2009 08:30 Go to previous messageGo to next message
cookiemonster
Messages: 12422
Registered: September 2008
Location: Rainy Manchester
Senior Member
virmahi wrote on Thu, 26 March 2009 13:17
Whenever there is no data found then it terminates then it comes only with the first error.



So are you missing an exception handler for no_data_found?
Re: Return all errors on a screen from exception [message #394336 is a reply to message #394312] Thu, 26 March 2009 08:41 Go to previous messageGo to next message
virmahi
Messages: 266
Registered: April 2008
Location: India
Senior Member
I changed the code as ehegagoka suggested. It works as it was working before...but the code is smaller now.

But the problem still remains and I get only the first error.

Please help me on this....
Re: Return all errors on a screen from exception [message #394338 is a reply to message #394304] Thu, 26 March 2009 08:46 Go to previous messageGo to next message
cookiemonster
Messages: 12422
Registered: September 2008
Location: Rainy Manchester
Senior Member
post the code
Re: Return all errors on a screen from exception [message #394340 is a reply to message #394336] Thu, 26 March 2009 08:52 Go to previous messageGo to next message
virmahi
Messages: 266
Registered: April 2008
Location: India
Senior Member
The reason may be that I had inserted values for only some columns and not others for a record.
I tried to validate DATE_OF_APPL as below...it was not going inside the IF condition. When I updated the column with NULL then the error displayed.
IF TRIM(c2.DATE_OF_APPL) IS NULL THEN
              
            l_error_arr.extend;
            l_ERROR_ARR(l_error_arr.last) := 'Please enter Application Date'; 
            dbms_output.put_line(l_ERROR_ARR(l_error_arr.last));  
            END IF;  


Why this happened? Do I need to update all empty columns with NULL?
Re: Return all errors on a screen from exception [message #394341 is a reply to message #394304] Thu, 26 March 2009 08:55 Go to previous messageGo to next message
cookiemonster
Messages: 12422
Registered: September 2008
Location: Rainy Manchester
Senior Member
If they're not null then they're not strictly empty - probably they contain spaces.

Post results of:
desc TMP_AMS_IA_PROFIL_PARKING


select dump(DATE_OF_APPL) from TMP_AMS_IA_PROFIL_PARKING;
Re: Return all errors on a screen from exception [message #394348 is a reply to message #394341] Thu, 26 March 2009 09:17 Go to previous messageGo to next message
virmahi
Messages: 266
Registered: April 2008
Location: India
Senior Member
Hi,
I am pasting a aprt of the code where I am checking the validations.
 IF TRIM(c2.appl_no) IS NULL THEN
                 l_error_arr.extend;
               l_ERROR_ARR(l_error_arr.last) := 'Application No cannot be NULL';  
               dbms_output.put_line(l_ERROR_ARR(l_error_arr.last));            
            
          ELSIF is_number(TRIM(c2.appl_no)) = FALSE
            THEN
               
            l_error_arr.extend;
            l_ERROR_ARR(l_error_arr.last) := 'Application No should be Numeric...from array';  
            dbms_output.put_line(l_ERROR_ARR(l_error_arr.last));             
            END IF;
              
             -- Validating Length of Application Number
            IF Length(TRIM(c2.appl_no)) > 8 THEN
             
            l_error_arr.extend;
            l_ERROR_ARR(l_error_arr.last) := 'Application No should be maximum 8 digit';
            dbms_output.put_line(l_ERROR_ARR(l_error_arr.last));      
            END IF;
            
                   
            IF TRIM(c2.DATE_OF_APPL) IS NULL THEN
              
            l_error_arr.extend;
            l_ERROR_ARR(l_error_arr.last) := 'Please enter Application Date'; 
            dbms_output.put_line(l_ERROR_ARR(l_error_arr.last));  
            END IF;  
            -- Validating Application Date which cannot be a future Date
            IF c2.DATE_OF_APPL > SYSDATE THEN
              l_error_arr.extend;
            l_ERROR_ARR(l_error_arr.last) := 'Application Date can not be future date.';  
            dbms_output.put_line(l_ERROR_ARR(l_error_arr.last));   
            END IF;
            
            -- Validating if Application Receipt Date is NULL
            IF TRIM(c2.DATE_OF_RECEIPT) IS NULL THEN
              l_error_arr.extend;
            l_ERROR_ARR(l_error_arr.last) := 'Please enter a value for Application Receipt Date.';  
            dbms_output.put_line(l_ERROR_ARR(l_error_arr.last));                 
            END IF;
            
            -- Validating Application Receipt Date which cannot be a future Date
            IF TRIM(c2.DATE_OF_RECEIPT) > SYSDATE THEN
              l_error_arr.extend;
            l_ERROR_ARR(l_error_arr.last) := 'Application Receipt Date can not be future date.';    
            dbms_output.put_line(l_ERROR_ARR(l_error_arr.last));          
            END IF;
            
                  
            -- UM/BP Code should be Alpha Numeric.
            v_um_bp_code := LENGTH(TRIM(TRANSLATE(c2.um_bp_code, 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ+-.0123456789', ' ')));

            IF v_um_bp_code > 0 THEN
                   l_error_arr.extend;
            l_ERROR_ARR(l_error_arr.last) := 'UM/BP Code should be Alpha Numeric.';
            dbms_output.put_line(l_ERROR_ARR(l_error_arr.last));  
            END IF;      
            
            SELECT AGENTNO INTO v_agent_no FROM INTERMEDIARY  WHERE AGENTNO = TRIM(c2.um_bp_code);
            IF v_agent_no IS NULL THEN
            l_error_arr.extend;
            l_ERROR_ARR(l_error_arr.last) := 'UM/BP Code is not valid.';
            END IF;


There is value only for the column appl_no and the value is not numeric ...its a string. I am getting the below output :-
Application No should be Numeric...from array
Please enter Application Date
Please enter a value for Application Receipt Date.


I am not sure why I am not getting error messages for other columns too which are all NULL except the appl_no(only column with data for validation).
And when the pointer reached the position :-
SELECT AGENTNO INTO v_agent_no FROM INTERMEDIARY  WHERE AGENTNO = TRIM(c2.um_bp_code);

It gave the error :-
ORA-01403: no data found
ORA-06512: at "AMSUAT.PKS_AMS_UPLOAD", line 118
ORA-06512: at line 4


Please help me on this as what is going wrong and how to check the NULL values....

[Updated on: Thu, 26 March 2009 09:25]

Report message to a moderator

Re: Return all errors on a screen from exception [message #394349 is a reply to message #394304] Thu, 26 March 2009 09:27 Go to previous message
cookiemonster
Messages: 12422
Registered: September 2008
Location: Rainy Manchester
Senior Member
Seems fairly straight foward.

This query is not finding any data:
SELECT AGENTNO INTO v_agent_no FROM INTERMEDIARY  WHERE AGENTNO = TRIM(c2.um_bp_code);


If you want the procedure to carry on when that happens you need to wrap that select in a begin/end block with an exception handler for no_data_found.

As for testing for nulls, IS NULL is what you do.
Which is what you're doing at moment so if you think it's not working I can only suggest that your data is not really NULL.

EDIT: typos

[Updated on: Thu, 26 March 2009 09:30]

Report message to a moderator

Previous Topic: How to convert Varchar2 to CLOB (split from hijacked thread)
Next Topic: ORA-01779: Error
Goto Forum:
  


Current Time: Fri Dec 09 23:36:44 CST 2016

Total time taken to generate the page: 0.10560 seconds