TO_number

[chaitanyagoa]

hey

i had an issue in both the test and prod database where the TO_NUMBER function behaves differently depending on data.
I was just running explain plan for the same code using different oracle accounts and got different results.

Can someone help me with this?

is there anything on the database configuraton I need to check.
this is a prob since we moved form server A to server B.

Thanks

[davey23uk]

no examples?

[PAVB]

Let me understand this... you say to_number() function does different things depending on the data and then you say you are troubleshooting it by looking at explain plan?

wow!... I remember having a bottle of brandy around here, let me see...

[chaitanyagoa]

Here is an example of a query

SELECT COUNT(*)

INTO WI-COUNT

FROM S_MID_MEM_IDENTIFIER

WHERE MID_MEM_UID = TO_NUMBER(:WI-MEM-UID)

AND MID_MIT_CODE = 'HCID'

AND MID_DATE_BEGIN <= TRUNC(SYSDATE)

AND NVL(MID_DATE_END,SYSDATE) >= TRUNC(SYSDATE)

AND ROWNUM = 1



“WI-MEM-UID” is defined as an alpha-numeric field of 10 bytes [X(10)]. You can add your own mem_uid to make it work but if the number is less than 10 digits, be sure to right pad with blanks as COBOL would.

Thanks

[davey23uk]

what are we supposed to do with that?

[chaitanyagoa]

Actually i dont have access to the database....

they just send me this stuff and tell me to find a solution, thats y i am not being able to explain you guys properly......

I think it my turm to find a bottle of Brandy PAVB