Win a copy of Design for the Mind this week in the Design forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

need help with returning value of pl/sql Function

 
bobby, morkos
Ranch Hand
Posts: 82
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I have this and it compiles, however it does not return anything. Could anyone tell me why?
 
Jennifer Bhamoo
Ranch Hand
Posts: 47
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 17278
6
IntelliJ IDE Mac Spring
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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 ]
 
Mark Spritzler
ranger
Sheriff
Posts: 17278
6
IntelliJ IDE Mac Spring
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
OK try this
 
Mark Spritzler
ranger
Sheriff
Posts: 17278
6
IntelliJ IDE Mac Spring
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 82
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic