Home » Other » Client Tools » Funny Behavior with Stored Procedure
Funny Behavior with Stored Procedure [message #407846] Thu, 11 June 2009 21:40 Go to next message
russray
Messages: 9
Registered: May 2005
Junior Member
Please excuse me as I try to walk my way through this issue I am facing involving a stored procedure in Oracle 10g.

My issue is when I call the store procedure within SQL Developer, all is right with the world. The parameters get passed in correctly and the data is loaded in the tables correctly..... When I set the serverouput to on, I can see the parameters being passed correctly.


Next I back the call to the store procedure to a Java program. Here, the data is not right and I get a unique constraint error. I think the store procedure is correct but I can prove it.

Suggestion?


Russ
Re: Funny Behavior with Stored Procedure [message #407847 is a reply to message #407846] Thu, 11 June 2009 22:09 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
>Suggestion?

You work extra hard at determine what you are doing wrong.

> Here, the data is not right and I get a unique constraint error. I think the store procedure is correct but I can prove it.

If have choose between Oracle accurately reporting reality or inexperienced coder misunderstanding reality, I trust Oracle's error codes every time.

Oracle is too dumb to lie about what is knows is wrong.

You need to help us by following the Posting Guidelines as stated below.
http://www.orafaq.com/forum/t/88153/0/
Go to the URL above click the link "Posting Guidelines"
Go to the section labeled "Practice" & do as directed.
Re: Funny Behavior with Stored Procedure [message #407888 is a reply to message #407847] Fri, 12 June 2009 04:04 Go to previous messageGo to next message
russray
Messages: 9
Registered: May 2005
Junior Member
Thank you for your response........

I don't follow you.... In one instance, SQL Developer/Oracle is not 'lying' because no error is returned. However, when I call the store procedure within a Java Program, I get a constraint error and the data is not right. This time Oracle is yelling at me. Following your reasoning, which Oracle should I believe? Wink

Others have suggested the store procedures is called twice from within the Java Program. I don't know about this. I don't see how this is possible. I wrote the simple Java JDBC Call to the store procedure. It is not in a loop. What I do know is when I look at the tables, what is being passed into the store procedure is not causing a record to be entered into one table. Other tables have several columns with the wrong value. Almost like Gremlins have changed the data.

Thank you again for your comments and suggestion.

Russ
Re: Funny Behavior with Stored Procedure [message #407918 is a reply to message #407846] Fri, 12 June 2009 06:12 Go to previous messageGo to next message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
You can always turn on tracing and see how many times the procedure is being called and what parameters are being passed.
Re: Funny Behavior with Stored Procedure [message #407923 is a reply to message #407918] Fri, 12 June 2009 06:29 Go to previous messageGo to next message
russray
Messages: 9
Registered: May 2005
Junior Member
Oh..... You mean use the JDBC Driver with additional logging and set the trace to true...

Thanks I will try this.....

There is also tracing in Oracle...... Will this help me any?

Excllent suggestions..... thank you.....

Russ
Re: Funny Behavior with Stored Procedure [message #407924 is a reply to message #407846] Fri, 12 June 2009 06:48 Go to previous messageGo to next message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
I actually meant oracle tracing, but you might as well use both.
Re: Funny Behavior with Stored Procedure [message #416176 is a reply to message #407924] Fri, 31 July 2009 00:22 Go to previous message
Kevin Meade
Messages: 2103
Registered: December 1999
Location: Connecticut USA
Senior Member
to prove that your procedure is good, do two things:

1) put some auditing of some kind in it so that it can report to you somehow what the values of the paramters it received were. Using whatever you want, dbms_output, autonomous transaction, whatever works for you. Indeed, you should always think about some kind of audit routine for you code.

2) execute using sqlplus (or if you are in 11g where there is no sqlplus, download 10g sqllplus and use that).

Correct results will prove the value of the procedure.

From there run from java still letting the procedure audit its incomming parameters. Then you will know if it is receiving the same inputs.

As was said before, my money is on "OPERATOR ERROR".

1) you are not passing the values from the java program that you think you are (might be wrong variables, wrong order, datatype issue).

2) you are not calling the same procedure because you have a second copy of it somewhere else and your java call is using that one.

Good luck, Kevin
Previous Topic: ORA-03113: end-of-file on communication channel in toad
Next Topic: connection Failure ORA-12154: TNS:could not resolve service name ?
Goto Forum:
  


Current Time: Thu Mar 28 16:59:21 CDT 2024