Oracle Procedure execution on 2 different databases

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.
Who is Participating?
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')
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?

vishalgoyal123Author Commented:
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.
slightwv (䄆 Netminder) Commented:
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.
Forced accept.

EE Admin
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.