Oracle Procedure execution on 2 different databases

Posted on 2007-08-08
Last Modified: 2008-02-01
I am facing a problem in Oracle.
I am calling a procedure which has 7 parameters : 2 are date type and rest 5 are number types. This is being executed well on test database.

However this call is being failed on Production and throwing : ORA-01858 error.
I drilled further and found that when tracing for this session is trurned ON, it executes. But when it is turned Off for this session, it fails immediately.

Seems to be strange and irrelevant, but switching tracing for the Session ON and OFF os doing trick. However the same piece of code is working good on Test environment without turning session tracing ON and OFF.
the s/w version on both the test and database environment is same.
Any clues.
Question by:vishalgoyal123
    LVL 12

    Expert Comment

    maybe you have different nls settings on the databases, so DATE formats are not the same?

    how do you pass the date parameters to the procedure?


    Author Comment

    This is the first step i did. nls settngs on both are exactly  same.

    Also these parameter are being passed from front end VB application which are of date type.
    When this VB application is pointed to Test database, it works fine.
    LVL 12

    Accepted Solution

    i don't know how VB interprets date formats ... can you pass the parameters with a format mask like

    TO_DATE('2007/08/08', 'YYYY/MM/DD')
    LVL 76

    Assisted Solution

    by:slightwv (䄆 Netminder)
    I can't explain why setting tracing causes it to magically work.  If it works on test and not production, there has to either be a difference in the configurations ( O/S or database ), or some bug that hasn't been seen in test base on anything from amount of data to the servername itself (basically if it's a bug, anything goes).

    I can tell you from past experience with VB.Net that it's best to force the date format in the VB code and use to_date on the Oracle end as jwhal suggested.  This eliminates any possibility of confusion.
    LVL 1

    Expert Comment

    Forced accept.

    EE Admin

    Featured Post

    Gigs: Get Your Project Delivered by an Expert

    Select from freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely and get projects done right.

    Join & Write a Comment

    Suggested Solutions

    How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
    Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
    This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
    This video shows how to recover a database from a user managed backup

    755 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    21 Experts available now in Live!

    Get 1:1 Help Now