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

Thread: TO_number

  1. 06-30-2008, 11:11 AM #1
    chaitanyagoa
    chaitanyagoa is offline Junior Member
    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
    Reply With Quote Reply With Quote
  2. 06-30-2008, 11:13 AM #2
    davey23uk
    davey23uk is offline Senior Advisor
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,334
    no examples?
    Reply With Quote Reply With Quote
  3. 06-30-2008, 11:27 AM #3
    PAVB's Avatar
    PAVB
    PAVB is offline Senior Advisor
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,555
    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.
    Reply With Quote Reply With Quote
  4. 06-30-2008, 11:43 AM #4
    chaitanyagoa
    chaitanyagoa is offline Junior Member
    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
    Reply With Quote Reply With Quote
  5. 06-30-2008, 11:56 AM #5
    davey23uk
    davey23uk is offline Senior Advisor
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,334
    what are we supposed to do with that?
    Reply With Quote Reply With Quote
  6. 06-30-2008, 12:05 PM #6
    chaitanyagoa
    chaitanyagoa is offline Junior Member
    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
    Reply With Quote Reply With Quote
« Previous Thread | Next Thread »

Posting Permissions

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

Forum Rules

Click Here to Expand Forum to Full Width