Home » Developer & Programmer » Forms » frm 40735 and ora 01722 (oracle 10g forms and reports 9i)
frm 40735 and ora 01722 [message #597391] Fri, 04 October 2013 05:51 Go to next message
lacchhii
Messages: 118
Registered: May 2009
Location: bangalore
Senior Member

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 #597394 is a reply to message #597391] Fri, 04 October 2013 06:19 Go to previous messageGo to next message
Littlefoot
Messages: 19631
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
If you didn't modify any code, then data might cause that problem.
Oracle

ORA-01722: invalid number

Cause: The attempted conversion of a character string to a number failed because the character string was not a valid numeric literal. Only numeric fields or character fields containing numeric data may be used in arithmetic functions or expressions. Only numeric fields may be added to or subtracted from dates.

Action: Check the character strings in the function or expression. Check that they contain only numbers, a sign, a decimal point, and the character "E" or "e" and retry the operation.
Re: frm 40735 and ora 01722 [message #597403 is a reply to message #597391] Fri, 04 October 2013 08:42 Go to previous messageGo to next message
joy_division
Messages: 4515
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 messageGo to next message
lacchhii
Messages: 118
Registered: May 2009
Location: bangalore
Senior Member

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 #597493 is a reply to message #597490] Sat, 05 October 2013 02:39 Go to previous messageGo to next message
Littlefoot
Messages: 19631
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
I think that this command can't raise ORA-01722. As it doesn't even use any form item, you can easily run it in SQL*Plus and see what happens.

What are MSGH_TXN_NO and P_APPL_NO datatypes? Number? If so, TO_CHAR can do that easily. Character? If so, why do you TO_CHAR them?

Saying that you recompiled the form (but it didn't help): did you recompile ALL (Ctrl + Shift + K)? If not, try it now.
Re: frm 40735 and ora 01722 [message #597500 is a reply to message #597493] Sat, 05 October 2013 03:22 Go to previous messageGo to next message
lacchhii
Messages: 118
Registered: May 2009
Location: bangalore
Senior Member

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 messageGo to next message
Littlefoot
Messages: 19631
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 of
select 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 messageGo to next message
lacchhii
Messages: 118
Registered: May 2009
Location: bangalore
Senior Member

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 #597561 is a reply to message #597556] Sun, 06 October 2013 11:00 Go to previous messageGo to next message
Littlefoot
Messages: 19631
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Error ("Invalid number") you got when trying to select TO_NUMBER(MSGH_TXN_NO) suggests that this column contains something else but numbers. OK, no problem. It means that statement you use should look like
DELETE FROM T_MESSAGE_HDR WHERE TRIM(MSGH_TXN_NO) = TO_CHAR(P_APPLN_NO);

What happens if you use it as I wrote it?

By the way, are you *sure* that this is the line that raises the error?
Re: frm 40735 and ora 01722 [message #597575 is a reply to message #597561] Sun, 06 October 2013 23:45 Go to previous messageGo to next message
lacchhii
Messages: 118
Registered: May 2009
Location: bangalore
Senior Member

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 #597577 is a reply to message #597575] Mon, 07 October 2013 00:32 Go to previous messageGo to next message
Littlefoot
Messages: 19631
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
I don't see anything that could raise that error, not in code you posted so far.

That procedure accepts one parameter, P_APPLN_NO. Its datatype is Number. How do you provide it? What is the code that calls DELETE_DUMMY_APPLN_NO procedure? Could you post WHEN-BUTTON-PRESSED trigger code?

One more objection: cursor FOR loop doesn't need EXIT clause.
Re: frm 40735 and ora 01722 [message #597583 is a reply to message #597577] Mon, 07 October 2013 01:06 Go to previous messageGo to next message
lacchhii
Messages: 118
Registered: May 2009
Location: bangalore
Senior Member

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 messageGo to next message
cookiemonster
Messages: 10961
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 messageGo to next message
lacchhii
Messages: 118
Registered: May 2009
Location: bangalore
Senior Member

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
Re: frm 40735 and ora 01722 [message #597646 is a reply to message #597641] Mon, 07 October 2013 05:34 Go to previous message
cookiemonster
Messages: 10961
Registered: September 2008
Location: Rainy Manchester
Senior Member
foreign keys are properties of the table, you can't set them in the procedure. I suggest you read up on them in the documentation.
Previous Topic: Auto stretch Functionality in Oracle Forms
Next Topic: To open a new word document and delete that worddocument via forms
Goto Forum:
  


Current Time: Mon Sep 22 03:46:41 CDT 2014

Total time taken to generate the page: 0.11723 seconds