10 replies to this topic

[sudhakar]

Dear friends,

I got one error
@MSG - 366
@MSG TEXT 366 - sql error
@MSGARGS - IRMCSD

@SYSERR - 016E
@SYSERRTEXT - ORA-01722:invalid number


What is this error?
I wonder this error is giving in runtime environment where as in development environment, there is no error.
But both are pointing to different databases(both are oracle only).

I checked table structures of both environments and are similar.
I tried to reexport the function from dev env. but there is no change.

Can anybody tell why this error is giving.

One more thing i observed is when i disconnect and reconnect session this error gone for that particular claim number, that i am doing posting.

But the error comes, for next claim number.

Can anybody faced this problem. Let me know your suggetions.

Thanks,

Sudhakar Chunduri.

[twallis]

This error shows a difference between the ProIV file def of the table and the oracle one.

I usually find I get it when I have forgotten to change NUMBER field in the file def in ProIV
but it can happen when the length is out or a number of other cases.

HAVE a GOOD look to make sure the 2 match

e.g.

table test

has fields TEST1, TEST2, TEST3, TEST4

and the ProIV file def is TEST1, TEST3, TEST4

I just missed one field when creating the file def in ProIV.

[twallis]

oh and make sure you look at the detail too

020 N TEST 1
external type:NUMBER storage format: 1.0 encrypted?:
help:
attr: bold: blink: underline: hide: reverse:
colour: on:
alternate:

[twallis]

I should look more at the error before opening my mouth

Yours is specific to

@SYSERRTEXT - ORA-01722:invalid number

you are trying to add a non-number character into an oracle number field.

[Rob Donovan]

Hi,

I would enable the ProIV Trace, TRACEALL = 15 and see what the real SQL is that is being sent to Oracle.

This should give you an idea of what is wrong back in ProIV.

Rob D.

[Joseph Bove]

Sudhakar,

I like Rob's idea. I'd probably go with TRACESQL=8 instead of TRACEALL=15. This will considerably limit the TRACE information.

hth,

Joseph

[twallis]

Rob's Idea will make it easier to find the fault.

still looks like

you are trying to add a non-number character into an oracle number field.

rob's idea will tell which one it is.

[sudhakar]

Hi,


Thanks for your replies.

I like to know what is this TRACESQL = 8 and how to set it.

Sorry, iam not good at oracle.

I tried to put umsgs in all fields of that file in Before write. Everything is fine and all fields are assigned with values. Even iam getting user message which was there at the last line. But from that logic, it is not going to next logic and giving this sql error.

I put umsg in after write - error also. But it is not going there.

I doubt, when proiv fails to add record into file, it should go into AWE logic right? Why it is failing in my environment?

The only conversion of numeric to string iam using in this function is -

IANOS_CESSION_LAST_AMEND = PIC(#AMEND_NOS , '999')

Here IANOS_CESSION_LAST_AMEND is the key field of this IRMCSD file.

and BW iam using one numeric field assigned like this-
INDTE_CREATE = @DATE ( INDTE_CREATE is numeric field)

For tracing the problem, i removed this BW logic also. That means only key fields i assigned with none of other fields assigned (all are blank). Even then also, this sql error is coming.


Let me know all your suggetions...

Thanks,
Sudhakar.

[twallis]

@F IRMCSD

Look at the detail for example in ProIV it mite be


019 A INDTE_CREATE 10 12.2
external type:ALPHA storage format: S12.2 encrypted?:
help:
attr: bold: blink: underline: hide: reverse:
colour: on:
alternate:

but in oracle it is likly to be

INDTE_CREATE NUMBER(18,6) DEFAULT 0 NOT NULL

[Marcel De Rijk]

You will also get this error if the number exceeds 12.2, ie 13.2 - the old number too large for display code type error.

[Joseph Bove]

Sudhakar,

You may want to post your ProIV file definition and your Oracle table definition. If your error is not being caused by a number that is too large, then you most likely have a discrepancy between the ProIV file and the Oracle table.

Regards,

Joseph