jQuery in Action, 2nd edition*
The moose likes Oracle/OAS and the fly likes need help with returning value of pl/sql Function Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Murach's Java Servlets and JSP this week in the Servlets forum!
JavaRanch » Java Forums » Products » Oracle/OAS
Bookmark "need help with returning value of pl/sql Function" Watch "need help with returning value of pl/sql Function" New topic
Author

need help with returning value of pl/sql Function

bobby, morkos
Ranch Hand

Joined: Jan 04, 2002
Posts: 82
I have this and it compiles, however it does not return anything. Could anyone tell me why?
Jennifer Bhamoo
Ranch Hand

Joined: Mar 09, 2002
Posts: 47
If I remember correctly, when you don't assign a variable a default value, that variable is initialized to null.
I don't see where you ever assigned v_pegasus_id anything, so if C1%NOTFOUND is true, then it is just returning null, and if C1%NOTFOUND is indeed true, then in your else statement, I don't see how rec.pegasus_id could have return anything since a record wasn't returned.
I hope this helps.
-Jennifer
Mark Spritzler
ranger
Sheriff

Joined: Feb 05, 2001
Posts: 17249
    
    6

Or, I also don't see any exception handling. Maybe something is wrong in the query, and it throws and exception, which isn't handled, and therefore no return statement is ever executed.
Actually, I take that back. However it will be somthing just like that.
In your fetch it finds no records, therefore it throws the NOTFOUND exception, and trys to go to the exception handling that is not there. It won't run the code after the fetch.
So try this

Then remove the IF statement in the Body of the function.
I wish I could show this better than I am right now, but basically you have it good. It's just that you need to move the code for NOTFOUND into the EXCEPTION area, so that it will return a value

Just my guess.
Mark
[ April 30, 2002: Message edited by: Mark Spritzler ]

Perfect World Programming, LLC - Two Laptop Bag - Tube Organizer
How to Ask Questions the Smart Way FAQ
Mark Spritzler
ranger
Sheriff

Joined: Feb 05, 2001
Posts: 17249
    
    6

OK try this
Mark Spritzler
ranger
Sheriff

Joined: Feb 05, 2001
Posts: 17249
    
    6

OK, for some reason I feel I am still off here.
It looks like the procedure is trying to first see if the record already exists. If it does it returns the ID, if not it inserts and returns a new ID.
FOr new ID, I would use the nextVal rather than currval.
Second, I don't think you need a Cursor for this type of function. And also that way my solution of the Exception handling would work, and also give less code.
Now try this

I feel much better about that one.
Here are a couple of sites I'd suggest you add to your favorites
a great link is Ask Tom.
Ask Tom is a guy at Oracle that knows everything. You ask him a question and he'll respond,
usually within the day. I have never had him fail me yet.
For those that like a good Oracle FAQ There is no better than
Ari Kaplan's Oracle Tips.

Hope that helps
Mark
bobby, morkos
Ranch Hand

Joined: Jan 04, 2002
Posts: 82
Thanks for your help and links. You been really helpful.

Originally posted by Mark Spritzler:
OK, for some reason I feel I am still off here.
It looks like the procedure is trying to first see if the record already exists. If it does it returns the ID, if not it inserts and returns a new ID.
FOr new ID, I would use the nextVal rather than currval.
Second, I don't think you need a Cursor for this type of function. And also that way my solution of the Exception handling would work, and also give less code.
Now try this

I feel much better about that one.
Here are a couple of sites I'd suggest you add to your favorites
a great link is Ask Tom.
Ask Tom is a guy at Oracle that knows everything. You ask him a question and he'll respond,
usually within the day. I have never had him fail me yet.
For those that like a good Oracle FAQ There is no better than
Ari Kaplan's Oracle Tips.

Hope that helps
Mark
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: need help with returning value of pl/sql Function
 
Similar Threads
Closing cursor in a stored procedure when invoked from a callable statement
Need Expert Advice
TOAD run as script error
writing my first stored procedure
I have started this java program that passes an object to oracle pl/sql