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.
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...
Pablo (Paul) Berzukov
Author of Understanding Database Administration
available at amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.
here it goes
Here is an example of a query
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.
what are we supposed to do with that?
sorry about that guys...
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
Click Here to Expand Forum to Full Width