Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 722
  • Last Modified:

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.
2 Solutions
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.
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')
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

Featured Post

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now