TO_number
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 6 of 6

Thread: TO_number

  1. #1
    Join Date
    Jan 2008
    Posts
    45

    TO_number

    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

  2. #2
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,333
    no examples?

  3. #3
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    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.

  4. #4
    Join Date
    Jan 2008
    Posts
    45

    here it goes

    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

  5. #5
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,333
    what are we supposed to do with that?

  6. #6
    Join Date
    Jan 2008
    Posts
    45

    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width