frm-40735: KEY-COMMIT trigger raised unhandled exception ora-06502

chalie001
chalie001 used Ask the Experts™
on
errorhi am geting this error when commiting in my form the error happen when am calling another procedure
this is how i call the procedure
 PROC_employee_DESC(PRAMA1,
    	                PRAMA2,
       	              PRAMA3,
       	              PRAMA4);

this si the procedure giving error how can i handle that exception is there a better way

PROCEDURE  PROC_employee_DESC (PRAMA1          in out varchar2,
                         PRAMA2     in out varchar2,
                         PRAMA3          out varchar2,
                         PRAMA4          out varchar2) IS
BEGIN
  DECLARE
  wrk_param1                   NUMBER(11);
  wrk_param2                VARCHAR2(8);
  wrk_err_param_msg               VARCHAR2(80);
  wrk_param               VARCHAR2(7);
  wrk_pram3             VARCHAR2(200);
  wrk_param4               VARCHAR2(1);
  wrk_err_param5              VARCHAR2(1);
  wrk_param6              NUMBER(2);
  wrk_param7              hr.employee%type;
  wrk_param8          VARCHAR2(1);
  wrk_param9       VARCHAR2(1);
  wrk_param10        VARCHAR2(3);
  wrk_param11              VARCHAR2(1);
  wrk_param12                   VARCHAR2(2);
  wrk_param13           NUMBER(11,2);
  wrk_param14          VARCHAR2(1);
  wrk_param15        VARCHAR2(1);
  wrk_param16              VARCHAR2(1);
  wrk_param17               VARCHAR2(1);
  wrk_param18                VARCHAR2(1);
  wrk_param19         VARCHAR2(1);
  wrk_param20  VARCHAR2(1);
  wrk_param21               VARCHAR2(80);
  wrk_param22                  varchar2(16);
  wrk_date_param1                  DATE;
  wrk_name_param             VARCHAR2(1);
  struc_prama 						varchar2(1);
  pref_param1              varchar2(1);
  mit_param2     							varchar2(1);
  crit_param3       					  varchar2(1);
	param_ind    				  varchar2(1);
  param_cde 					  varchar2(1);
	pram_mtl_param  					  varchar2(1);
  itm_param1 							varchar2(1);
	itm_param2 							varchar2(1);
  ets_param123					  varchar2(1);
  wrk_param131         varchar2(2);
  wrk_paream12          varchar2(1);
  BEGIN 
  	
  --	message('CALLINGSTORE');
  	
     VALIDATION_2
                    (wrk_param1,
                     PRAMA1,
                     wrk_date_param1,
                      PRAMA2,
                     wrk_pram3,
                     wrk_param4,
                     wrk_err_ind,
                     wrk_err_cde,
                     wrk_err_param_msg,
                     wrk_com_cde,
                     wrk_param8,
                     wrk_itm_stat_cde,
                     wrk_param10,
                     wrk_param11,
                     wrk_param12,
                     wrk_param13,
                     wrk_param14,
                     wrk_param15,
                     wrk_param16,
                     wrk_param17,
                     wrk_param18,
                     wrk_lsar_ws_item,
                     wrk_param20 ,
                     wrk_param21,
                     struc_prama,
                     pref_param1,
                     mit_param2,   
						 				 crit_param3,
						 				 param_ind,
						 				 param_cde,
						 				 pram_mtl_param,
						 				 itm_param1,
						 				 itm_param2,
						 				 ets_param123,
									   wrk_param131,
									   wrk_paream12);
									   
									   
									   
  IF wrk_err_param5= 'Y' THEN
  	 PRAMA3 := wrk_err_ind;
  	 PRAMA4 := wrk_err_param_msg;                                               
     message(PRAMA4);
     raise form_trigger_failure;
  ELSE
      PRAMA2 := wrk_pram3;
  END IF;
  END ;
END;

Open in new window


am in oracle database 11gr2,I think I have to handle the null value when passing value how can I do that
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Helena Markováprogrammer-analyst

Commented:
In the code there is
wrk_date_param1                  DATE;

What is its value when you call VALIDATION_2 in PROC_employee_DESC ?

Author

Commented:
Is proc_date in out date

Author

Commented:
How can I handle the null value when passing value because the error come when is null if I pass value there is no erro
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Helena Markováprogrammer-analyst

Commented:
I would use NVL function.
http://docs.oracle.com/cd/E11882_01/server.112/e41084/functions119.htm#SQLRF00684

http://docs.oracle.com/cd/E11882_01/server.112/e17766/e4100.htm#ORA-06502
ORA-06502: PL/SQL: numeric or value errorstring
Cause: An arithmetic, numeric, string, conversion, or constraint error occurred. For example, this error occurs if an attempt is made to assign the value NULL to a variable declared NOT NULL, or if an attempt is made to assign an integer larger than 99 to a variable declared NUMBER(2).

Action: Change the data, how it is manipulated, or how it is declared so that values do not violate constraints.

Author

Commented:
ya how can i do that

Author

Commented:
in my code
Helena Markováprogrammer-analyst

Commented:
VALIDATION_2
                    (wrk_param1,
                     PRAMA1,
                    nvl(wrk_date_param1,SYSDATE),
                      PRAMA2,
.....

Author

Commented:
i dont pass that value i only pass


PROC_employee_DESC(PRAMA1,
                          PRAMA2,
                           PRAMA3,
                           PRAMA4);
Most Valuable Expert 2012
Distinguished Expert 2018

Commented:
Not for points.

I strongly encourage you to use meaningful variable and parameter names.

You know what PRAMA1 is now since you are working with the code.

Come back to it in 6 months or a year when you need to make a change to the code and you likely will have no idea what that parameter is for.
Helena Markováprogrammer-analyst

Commented:
I agree with slightwv comment.

PROC_employee_DESC(
nvl(PRAMA1,default_value1),
                        nvl(PRAMA2,default_value2),
                      nvl(PRAMA3,default_value3),
                      nvl(PRAMA4,default_value4)
);

Those default_value1 ... default_value4 - it is on you, because I don't know your system - maybe it can be random value ?

Author

Commented:
am getting the error nvl cannot be used as an assigment target

Author

Commented:
when I did this
VALIDATION_2
                     (wrk_param1,
                      PRAMA1,
                      nvl(wrk_date_param1,SYSDATE),
                       PRAMA2,
programmer-analyst
Commented:
I am sorry for confusion.
You can do this:

 --    message('CALLINGSTORE');
 wrk_date_param1:= nvl(wrk_date_param1,SYSDATE);  
     VALIDATION_2
                    (wrk_param1,
                     PRAMA1,
                     wrk_date_param1,
                      PRAMA2,
 ...

Author

Commented:
wrk_date_param1:= nvl(wrk_date_param1,SYSDATE);

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial