OraFAQ Forum: Client Tools » Funny Behavior with Stored Procedure

Home » Other » Client Tools » Funny Behavior with Stored Procedure

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Funny Behavior with Stored Procedure [message #407846]

Thu, 11 June 2009 21:40

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

Report message to a moderator

Re: Funny Behavior with Stored Procedure [message #407847 is a reply to message #407846]

Thu, 11 June 2009 22:09

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.

Report message to a moderator

Re: Funny Behavior with Stored Procedure [message #407888 is a reply to message #407847]

Fri, 12 June 2009 04:04

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

Report message to a moderator

Re: Funny Behavior with Stored Procedure [message #407918 is a reply to message #407846]

Fri, 12 June 2009 06:12

cookiemonster
Messages: 13961
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.

Report message to a moderator

Re: Funny Behavior with Stored Procedure [message #407923 is a reply to message #407918]

Fri, 12 June 2009 06:29

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

Report message to a moderator

Re: Funny Behavior with Stored Procedure [message #407924 is a reply to message #407846]

Fri, 12 June 2009 06:48

cookiemonster
Messages: 13961
Registered: September 2008
Location: Rainy Manchester

Senior Member

I actually meant oracle tracing, but you might as well use both.

Report message to a moderator

Re: Funny Behavior with Stored Procedure [message #416176 is a reply to message #407924]

Fri, 31 July 2009 00:22

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

Report message to a moderator

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:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sun Dec 22 10:34:28 CST 2024