frm 40735 and ora 01722 [message #597391] |
Fri, 04 October 2013 05:51 ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
|
hi,
i face this problem since a week. the form which was working fine till last week suddenly started giving this error when button pressed
"frm 40735 when button pressed returned trigger raised unhandled exception ora 01722"
i have not made any changes to the form or procedure, but suddenly its not working.
the same form works fine in a laptop . i copied the same form to check if there is any changes happened, but yet its not working.
i checked in many forums , but not many seem to mention about ora 01722 even though they mention about frm 40735.
In one of the forum help it says to recompile the form and create a fresh .fmx file. I did that but yet it gives the same problem.
is there any other solution.
|
|
|
|
Re: frm 40735 and ora 01722 [message #597403 is a reply to message #597391] |
Fri, 04 October 2013 08:42 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
joy_division
Messages: 4963 Registered: February 2005 Location: East Coast USA
|
Senior Member |
|
|
You are doing TO_NUMBER or a varchar2 field, I bet.
If you are doing a lookup into a table and one row has data that cannot be converted to a number, you can get this.
|
|
|
Re: frm 40735 and ora 01722 [message #597490 is a reply to message #597403] |
Sat, 05 October 2013 01:57 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
|
hi,
thanks for the reply. this is what is there in the proceedure
DELETE FROM T_MESSAGE_HDR WHERE TO_CHAR(TRIM(MSGH_TXN_NO))=TO_CHAR(P_APPLN_NO);
Do i have to check this table T_MESSAGE_HDR to see if there is any data that cannot be converted to a number?
there are around 73000 rows in this table and the problem has occurred only from the last week. is there any ways of checking the wrong data from this table?
|
|
|
|
Re: frm 40735 and ora 01722 [message #597500 is a reply to message #597493] |
Sat, 05 October 2013 03:22 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
|
i did compile it by Ctrl + Shift + K only . Actually the form is to delete the extra application number indexed. this number sits into many tables as shown below .
CREATE OR REPLACE PROCEDURE SAIBANG.DELETE_DUMMY_APPLN_NO(P_APPLN_NO IN NUMBER)
IS
CURSOR C1 IS
SELECT APPLN_PK
FROM T_APPLICATION_HDR WHERE APPLN_NO=P_APPLN_NO;
BEGIN
FOR I IN C1 LOOP
DELETE FROM T_APPLN_DCRG_WITHHELD WHERE ADW_APPLN_PK=I.APPLN_PK;
DELETE FROM T_APPLN_DCRG_NOMINEE WHERE ADN_APPLN_PK=I.APPLN_PK;
DELETE FROM T_APPLN_PNSNR_FAMILY WHERE APF_APPLN_PK=I.APPLN_PK;
DELETE FROM T_APPLN_BF_REVISION WHERE ABR_APPLN_PK=I.APPLN_PK;
DELETE FROM T_INWARD_OUTWARD WHERE INOUT_APPLN_REF_PK=I.APPLN_PK;
DELETE FROM T_APPLN_PENSIONER WHERE APEN_APPLN_PK=I.APPLN_PK;
DELETE FROM T_APPLN_BENEFITS WHERE APB_APPLN_PK=I.APPLN_PK;
DELETE FROM T_APPLN_AUTHORITY WHERE APA_APPLN_PK=I.APPLN_PK;
DELETE FROM T_APPLN_SRVC_VRFN WHERE ASV_APPLN_PK=I.APPLN_PK;
DELETE FROM T_APPLN_REASON WHERE APR_APPLN_PK=I.APPLN_PK;
DELETE FROM T_APPLN_RECOVERY WHERE AR_APPLN_PK=I.APPLN_PK;
EXIT WHEN C1%NOTFOUND;
END LOOP;
DELETE FROM T_MESSAGE_HDR WHERE TO_CHAR(TRIM(MSGH_TXN_NO))=TO_CHAR(P_APPLN_NO);
DELETE FROM T_PRINT_BATCH_HDR WHERE PBH_APPLN_NO=P_APPLN_NO;
DELETE FROM T_APPLICATION_HDR WHERE APPLN_NO=P_APPLN_NO;
commit;
END;
It was not giving any problem and was getting deleted from all tables. number conversion takes place in only one table.
MSGH_TXN_NO is varchar2 and P_APPLN_NO is number datatype.
It does gives this error "frm 40735 when button pressed returned trigger raised unhandled exception ora 01722"
I am not sure where the problem is. i glanced through the values of MSGH_TXN_NO field, but could figure out any extra characters other than number. I am not sure why MSGH_TXN_NO field is a character datatype since there is only numbers being stored, but i can't change the datatype now.
any suggestion would be of great help
|
|
|
Re: frm 40735 and ora 01722 [message #597529 is a reply to message #597500] |
Sat, 05 October 2013 12:22 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
![](http://www.orafaq.com/forum/images/custom_avatars/72104.gif) |
Littlefoot
Messages: 21818 Registered: June 2005 Location: Croatia, Europe
|
Senior Member Account Moderator |
|
|
Quote:
MSGH_TXN_NO is varchar2 and P_APPLN_NO is number datatype
DELETE FROM T_MESSAGE_HDR WHERE TO_CHAR(TRIM(MSGH_TXN_NO))=TO_CHAR(P_APPLN_NO);
^
|
LF: Why do you TO_CHAR data that is already character?
What is the result ofselect to_number(msgh_txn_no) from t_message_hdr;
P.S. Forgot to say: all of these deletes would probably be faster if you kick out the loop and use DELETE with a subquery.
[Updated on: Sat, 05 October 2013 12:24] Report message to a moderator
|
|
|
Re: frm 40735 and ora 01722 [message #597556 is a reply to message #597529] |
Sun, 06 October 2013 08:44 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
|
I am not sure how it is. MSGH_TXN_NO is a varchar2 datatype. this was the same delete condition used with t_char(MSGH_TXN_NO) only and was working fine.
I did try
select to_number(msgh_txn_no) from t_message_hdr;
result ---- Invalid number
select to_char(msgh_txn_no) from t_message_hdr;
--- I got the values from the table.
I dont know how it is happening. Only thing i know is values of P_appln_no and msgh_txn_no are same. appln_no is number datatype. Is that the reason why it is to_char().
|
|
|
|
Re: frm 40735 and ora 01722 [message #597575 is a reply to message #597561] |
Sun, 06 October 2013 23:45 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
|
i am not sure if this line is the one that raises the error. i used the delete command individually and its working fine without using to_char for t_message_hdr while deleting from that table. since in the procedure thats the only table which is using to_char and the search in the net says the error message relates to string conversion. so i thought the problem might be in this delete command.
select TRIM(MSGH_TXN_NO) fROM T_MESSAGE_HDR
gives me the required value.
Is there any way that i can check if there are wrong values stored in T_MESSAGE_HDR table
|
|
|
|
Re: frm 40735 and ora 01722 [message #597583 is a reply to message #597577] |
Mon, 07 October 2013 01:06 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
|
here is the WHEN-BUTTON-PRESSED trigger code.
declare
alert_button number;
begin
if
:APPLN_NO is null then
message('Please Enter Application No....');
message(' ');
else
alert_button := SHOW_ALERT ('MY_ALERT');
IF alert_button = alert_button1 Then
DELETE_DUMMY_APPLN_NO(:APPLN_NO );
message('Application No is deleted'); message(' ');
ELSIF alert_button = alert_button2 Then
null;
ELSE
null;
END IF;
end if;
END;
Could you also suggest the changes required in the procedure by using a sub query. is it possible to modify the procedure DELETE_DUMMY_APPLN_NO using sub query without loop as suggested by you .
|
|
|
Re: frm 40735 and ora 01722 [message #597622 is a reply to message #597583] |
Mon, 07 October 2013 04:08 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
cookiemonster
Messages: 13937 Registered: September 2008 Location: Rainy Manchester
|
Senior Member |
|
|
Sounds like you need some on delete cascade foreign keys. It looks like you're deleting all the child records of a particular parent. If you set up the foreign keys right then you can just delete the parent and the foreign keys will take care of the rest.
|
|
|
Re: frm 40735 and ora 01722 [message #597641 is a reply to message #597622] |
Mon, 07 October 2013 05:15 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
|
HI,
yes sir you are right. i am deleting the child record of a particular parent.
Is my procedure doing the right job.
Quote:"If you set up the foreign keys right then you can just delete the parent and the foreign keys will take care of the rest."
How do i do this. do i have to change the procedure to set right the foreign keys .
your suggestions is helping me dig more into the problem
thanks a lot
|
|
|
|