Home » SQL & PL/SQL » SQL & PL/SQL » can we use procedure on select command  () 1 Vote
can we use procedure on select command [message #297962] Mon, 04 February 2008 05:54 Go to next message
ishika_20
Messages: 339
Registered: December 2006
Location: delhi
Senior Member
hello sir

can we use procedure on select command, if not then why??

please revert me back..
waiting for your reply
Re: can we use procedure on select command [message #297963 is a reply to message #297962] Mon, 04 February 2008 06:01 Go to previous messageGo to next message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
No.
Why it can be and above all, how?
But you can use (some) functions (and you already use this when you call Oracle ones).

Regards
Michel

[Updated on: Mon, 04 February 2008 06:02]

Report message to a moderator

Re: can we use procedure on select command [message #298057 is a reply to message #297963] Mon, 04 February 2008 19:26 Go to previous messageGo to next message
ishika_20
Messages: 339
Registered: December 2006
Location: delhi
Senior Member
sir but why i can't use procedure in select statement,
please give me the reason behind it.
i want to know the reason of it.

waiting for your reply..


thankx
Re: Can we use procedure on select command [message #298068 is a reply to message #297962] Mon, 04 February 2008 22:48 Go to previous messageGo to next message
spmano1983
Messages: 269
Registered: September 2007
Senior Member
Friends,

Functions are basically pre-compiled, but procedures are not. Thats why we are able to call functions from select statement but not procedure.


Thanks

Mano
Re: can we use procedure on select command [message #298070 is a reply to message #297962] Mon, 04 February 2008 22:52 Go to previous messageGo to next message
spmano1983
Messages: 269
Registered: September 2007
Senior Member
Sorry friends,

I made mistake. I think both are pre-compiled.

Thanks
Mano
Re: can we use procedure on select command [message #298082 is a reply to message #298057] Mon, 04 February 2008 23:56 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Think about it. What 'part' of a function do you use in SQL? The returned parameter. That gives a value that you can either select or compare to another value in your where clause.
Procedures don't return values.
Re: can we use procedure on select command [message #298117 is a reply to message #298082] Tue, 05 February 2008 01:24 Go to previous messageGo to next message
ishika_20
Messages: 339
Registered: December 2006
Location: delhi
Senior Member
hello frank

suppose i dont want to compare to another value in my where clause.Now can i use procedure on select statement ?

if any other link do you have then please provide me.

waiting for your reply
Re: can we use procedure on select command [message #298123 is a reply to message #298117] Tue, 05 February 2008 01:29 Go to previous messageGo to next message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
No, you can't.
Just ask this question: where do you want to put your procedure call?

Regards
Michel
Re: can we use procedure on select command [message #298168 is a reply to message #297962] Tue, 05 February 2008 03:57 Go to previous messageGo to next message
ora_2007
Messages: 430
Registered: July 2007
Location: Mumbai
Senior Member
As we all know that function always returns one and only one value and procedure returns zero or more values

So when we call a function that time we have a guarantee that only one value will return.
But if prcedure retruns more than one values with out paramameter mode then how we can write in select statement.
Re: can we use procedure on select command [message #298171 is a reply to message #298168] Tue, 05 February 2008 04:06 Go to previous messageGo to next message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
As we all know that function always returns one and only one value and procedure returns zero or more values

And this is wrong.
Function return at least 1 value but can be more than 1.

Quote:
So when we call a function that time we have a guarantee that only one value will return.

So this conclusion is wrong.

Regards
Michel
Re: can we use procedure on select command [message #298177 is a reply to message #298171] Tue, 05 February 2008 04:15 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
Quote:

Quote:
As we all know that function always returns one and only one value and procedure returns zero or more values

And this is wrong.
Function return at least 1 value but can be more than 1.



Depends on how you define "return". I usually think of "return value" as the value that can be assigned in this way :

returnvalue := function(parameter1, paramenter2, ...., parameterN);


In that case we do know that we can expect exactly one return value from a function.

Of course the function may have parameters that are defined as OUT parameters, and can thus can be changed by the function during the function call.

And by that definition a procedure has NO return values. And therefore can't be used in a SQL directly.

[Updated on: Tue, 05 February 2008 04:16]

Report message to a moderator

Re: can we use procedure on select command [message #298189 is a reply to message #298177] Tue, 05 February 2008 05:09 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Exactly!

ThomasG worded it correctly in my vision: there's a big difference between a return value and an OUT parameter.
Re: can we use procedure on select command [message #298193 is a reply to message #298189] Tue, 05 February 2008 05:17 Go to previous messageGo to next message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I quite agree with you, I just answered to ora_2007 using his words.

Regards
Michel
Re: can we use procedure on select command [message #298331 is a reply to message #297962] Tue, 05 February 2008 13:31 Go to previous messageGo to next message
Bill B
Messages: 1484
Registered: December 2004
Senior Member
However, if you feel that you really need to call the procedure in a select, then wrap the call to the procedure in a function call and use the function in the select.
Re: can we use procedure on select command [message #298687 is a reply to message #297962] Thu, 07 February 2008 02:55 Go to previous messageGo to next message
jyothsna1217
Messages: 9
Registered: February 2008
Location: hyderabad
Junior Member

May i know the difference between function's return value and the procedures 'out' parameter
Thank you
Re: can we use procedure on select command [message #298688 is a reply to message #298687] Thu, 07 February 2008 03:02 Go to previous message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
See my last post.

A return parameter is "returned" by the function and assigned with ":=", a parameter is a parameter which changes it's value during the execution.

returnvalue := function(parameter1, paramenter2, ...., parameterN);
Previous Topic: about sequence
Next Topic: What is the maximum length of a query string in oracle?
Goto Forum:
  


Current Time: Sun Dec 11 04:20:05 CST 2016

Total time taken to generate the page: 0.04895 seconds