OraFAQ Forum: Forms » frm 40735 and ora 01722

Home » Developer & Programmer » Forms » frm 40735 and ora 01722 (oracle 10g forms and reports 9i)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

frm 40735 and ora 01722 [message #597391]

Fri, 04 October 2013 05:51

lacchhii
Messages: 151
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.

Report message to a moderator

Re: frm 40735 and ora 01722 [message #597394 is a reply to message #597391]

Fri, 04 October 2013 06:19

Littlefoot
Messages: 21807
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.

Report message to a moderator

Re: frm 40735 and ora 01722 [message #597403 is a reply to message #597391]

Fri, 04 October 2013 08:42

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.

Report message to a moderator

Re: frm 40735 and ora 01722 [message #597490 is a reply to message #597403]

Sat, 05 October 2013 01:57

lacchhii
Messages: 151
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?

Report message to a moderator

Re: frm 40735 and ora 01722 [message #597493 is a reply to message #597490]

Sat, 05 October 2013 02:39

Littlefoot
Messages: 21807
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.

Report message to a moderator

Re: frm 40735 and ora 01722 [message #597500 is a reply to message #597493]

Sat, 05 October 2013 03:22

lacchhii
Messages: 151
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

Report message to a moderator

Re: frm 40735 and ora 01722 [message #597529 is a reply to message #597500]

Sat, 05 October 2013 12:22

Littlefoot
Messages: 21807
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

lacchhii
Messages: 151
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().

Report message to a moderator

Re: frm 40735 and ora 01722 [message #597561 is a reply to message #597556]

Sun, 06 October 2013 11:00

Littlefoot
Messages: 21807
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?

Report message to a moderator

Re: frm 40735 and ora 01722 [message #597575 is a reply to message #597561]

Sun, 06 October 2013 23:45

lacchhii
Messages: 151
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

Report message to a moderator

Re: frm 40735 and ora 01722 [message #597577 is a reply to message #597575]

Mon, 07 October 2013 00:32

Littlefoot
Messages: 21807
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.

Report message to a moderator

Re: frm 40735 and ora 01722 [message #597583 is a reply to message #597577]

Mon, 07 October 2013 01:06

lacchhii
Messages: 151
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 .

Report message to a moderator

Re: frm 40735 and ora 01722 [message #597622 is a reply to message #597583]

Mon, 07 October 2013 04:08

cookiemonster
Messages: 13920
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.

Report message to a moderator

Re: frm 40735 and ora 01722 [message #597641 is a reply to message #597622]

Mon, 07 October 2013 05:15

lacchhii
Messages: 151
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

Report message to a moderator

Re: frm 40735 and ora 01722 [message #597646 is a reply to message #597641]

Mon, 07 October 2013 05:34

cookiemonster
Messages: 13920
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.

Report message to a moderator

Previous Topic:	Auto stretch Functionality in Oracle Forms
Next Topic:	To open a new word document and delete that worddocument via forms

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Apr 19 16:28:20 CDT 2024