Home » SQL & PL/SQL » SQL & PL/SQL » Why we can not use the Oracle Procedure in an SQL Statement? (Oracle 10g)
Why we can not use the Oracle Procedure in an SQL Statement? [message #447118] Thu, 11 March 2010 23:18 Go to next message
suyogsheth
Messages: 18
Registered: November 2009
Location: Pune
Junior Member
Hi,

can anybody tell me that :

Why we can not use the Oracle Procedure in an SQL Statement?

Thanks in advance.
Re: Why we can not use the Oracle Procedure in an SQL Statement? [message #447124 is a reply to message #447118] Thu, 11 March 2010 23:25 Go to previous messageGo to next message
delna.sexy
Messages: 941
Registered: December 2008
Location: Surat, The Diamond City
Senior Member
SQL statements like, SELECT is used for data retrival from database and other DML statements are used for some kind of manipulation with data.
And for this purpose, the mechanism which returns data is required.
As the procedures do not return any data, then are not useful.
While on other hand, functions return some data. That's why we can use then in SQL statements.

regards,
Delna

[Updated on: Thu, 11 March 2010 23:25]

Report message to a moderator

Re: Why we can not use the Oracle Procedure in an SQL Statement? [message #447125 is a reply to message #447118] Thu, 11 March 2010 23:25 Go to previous messageGo to next message
BlackSwan
Messages: 25036
Registered: January 2009
Location: SoCal
Senior Member
>Why we can not use the Oracle Procedure in an SQL Statement?
SQL is not equal to or same as PL/SQL (Oracle Procedure).

SQL is valid in any ISO compliant RDBMS.
PL/SQL only valid in Oracle.
Re: Why we can not use the Oracle Procedure in an SQL Statement? [message #447130 is a reply to message #447118] Thu, 11 March 2010 23:34 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
Why we can not use the Oracle Procedure in an SQL Statement?

Because it returns no value.

Regards
Michel
Re: Why we can not use the Oracle Procedure in an SQL Statement? [message #447133 is a reply to message #447130] Thu, 11 March 2010 23:39 Go to previous messageGo to next message
suyogsheth
Messages: 18
Registered: November 2009
Location: Pune
Junior Member
Hi ,

Thanks for the reply.I know that Procedure can not return a value. But we can return the values by using the OUT Parameters.

So why we can not use it in a SQL Statement.?
Re: Why we can not use the Oracle Procedure in an SQL Statement? [message #447137 is a reply to message #447133] Thu, 11 March 2010 23:51 Go to previous messageGo to next message
delna.sexy
Messages: 941
Registered: December 2008
Location: Surat, The Diamond City
Senior Member
Can you write RETURN <some_value> inside procedure?

regards,
Delna
Re: Why we can not use the Oracle Procedure in an SQL Statement? [message #447143 is a reply to message #447137] Thu, 11 March 2010 23:59 Go to previous messageGo to next message
suyogsheth
Messages: 18
Registered: November 2009
Location: Pune
Junior Member
No. We can not return any value inside the Procedure.
Re: Why we can not use the Oracle Procedure in an SQL Statement? [message #447144 is a reply to message #447143] Fri, 12 March 2010 00:03 Go to previous messageGo to next message
delna.sexy
Messages: 941
Registered: December 2008
Location: Surat, The Diamond City
Senior Member
That means, procedures do not return any value.
Using OUT and IN OUT parameters with procedures, is only way of parameter passing type mechanism, to get updated values from procedures.

regards,
Delna
Re: Why we can not use the Oracle Procedure in an SQL Statement? [message #447146 is a reply to message #447133] Fri, 12 March 2010 00:14 Go to previous messageGo to next message
pablolee
Messages: 2834
Registered: May 2007
Location: Scotland
Senior Member
My assumption is that yours is a purely academic question and that you are not actually wanting to usse a procedure in a SQL statement, but rather, you simply want to know why it is so.
Re your comment about OUT parameters; Because OUT parameters are not the same as the programme returning a value. You can also use out parameters in a function, but you cannot use it withing a sql query.
Re: Why we can not use the Oracle Procedure in an SQL Statement? [message #447149 is a reply to message #447133] Fri, 12 March 2010 00:45 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
suyogsheth wrote on Fri, 12 March 2010 06:39
Hi ,

Thanks for the reply.I know that Procedure can not return a value. But we can return the values by using the OUT Parameters.

So why we can not use it in a SQL Statement.?

And how a query can use an OUYT parameter of a procedure?
Just think about it and how you could write and if you find then post it here.

Regards
Michel

[Updated on: Fri, 12 March 2010 00:46]

Report message to a moderator

Re: Why we can not use the Oracle Procedure in an SQL Statement? [message #447150 is a reply to message #447146] Fri, 12 March 2010 00:51 Go to previous messageGo to next message
suyogsheth
Messages: 18
Registered: November 2009
Location: Pune
Junior Member
Hi,

I was asking this question because ,i have faced the same question in a Interview. and i was not able to answer the question. So i want to know that is there any other right answer for the same.
Re: Why we can not use the Oracle Procedure in an SQL Statement? [message #447152 is a reply to message #447150] Fri, 12 March 2010 00:54 Go to previous messageGo to next message
delna.sexy
Messages: 941
Registered: December 2008
Location: Surat, The Diamond City
Senior Member
Except this (as described by forum members so far), there is no other right answer.

regards,
Delna
Re: Why we can not use the Oracle Procedure in an SQL Statement? [message #447153 is a reply to message #447152] Fri, 12 March 2010 00:56 Go to previous message
suyogsheth
Messages: 18
Registered: November 2009
Location: Pune
Junior Member
Thanks to all of you for the faster solution of my problem. Thanks a lot.
Previous Topic: What is the Difference between a Stand Alone Function & a Function declared in a Package.
Next Topic: Performing TYPE 2 operation using Merge in 10g
Goto Forum:
  


Current Time: Sun Dec 04 22:46:15 CST 2016

Total time taken to generate the page: 0.16872 seconds