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

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
chalie001Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Helena Markováprogrammer-analystCommented:
In the code there is
wrk_date_param1                  DATE;

What is its value when you call VALIDATION_2 in PROC_employee_DESC ?
chalie001Author Commented:
Is proc_date in out date
chalie001Author 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
Your Guide to Achieving IT Business Success

The IT Service Excellence Tool Kit has best practices to keep your clients happy and business booming. Inside, you’ll find everything you need to increase client satisfaction and retention, become more competitive, and increase your overall success.

Helena Markováprogrammer-analystCommented:
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.
chalie001Author Commented:
ya how can i do that
chalie001Author Commented:
in my code
Helena Markováprogrammer-analystCommented:
VALIDATION_2
                    (wrk_param1,
                     PRAMA1,
                    nvl(wrk_date_param1,SYSDATE),
                      PRAMA2,
.....
chalie001Author Commented:
i dont pass that value i only pass


PROC_employee_DESC(PRAMA1,
                          PRAMA2,
                           PRAMA3,
                           PRAMA4);
slightwv (䄆 Netminder) 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-analystCommented:
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 ?
chalie001Author Commented:
am getting the error nvl cannot be used as an assigment target
chalie001Author Commented:
when I did this
VALIDATION_2
                     (wrk_param1,
                      PRAMA1,
                      nvl(wrk_date_param1,SYSDATE),
                       PRAMA2,
Helena Markováprogrammer-analystCommented:
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,
 ...

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
chalie001Author Commented:
wrk_date_param1:= nvl(wrk_date_param1,SYSDATE);
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.