asked on

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

hi 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

Helena Marková

In the code there is
wrk_date_param1 DATE;

What is its value when you call VALIDATION_2 in PROC_employee_DESC ?

chalie001

ASKER

Is proc_date in out date

chalie001

ASKER

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

Helena Marková

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.

chalie001

ASKER

ya how can i do that

chalie001

ASKER

in my code

Helena Marková

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

chalie001

ASKER

i dont pass that value i only pass

PROC_employee_DESC(PRAMA1,
       PRAMA2,
       PRAMA3,
       PRAMA4);

slightwv (䄆 Netminder)

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á

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 ?

chalie001

ASKER

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

chalie001

ASKER

when I did this
VALIDATION_2
(wrk_param1,
PRAMA1,
nvl(wrk_date_param1,SYSDATE),
PRAMA2,

ASKER CERTIFIED SOLUTION

Helena Marková

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

chalie001

ASKER

wrk_date_param1:= nvl(wrk_date_param1,SYSDATE);