Home » SQL & PL/SQL » SQL & PL/SQL » can we call any procedure from select statement
can we call any procedure from select statement [message #272562] Fri, 05 October 2007 09:43 Go to next message
misragopal
Messages: 125
Registered: June 2005
Location: New Delhi, india
Senior Member

can we call any procedure from select statement.? there is one way 'call that procedure in function and then call that function in select statement'. but this method is having its own limitations. Any other idea..?
Re: can we call any procedure from select statement [message #272564 is a reply to message #272562] Fri, 05 October 2007 09:49 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Nope.
You can only call functions from SQL.

What are you trying to do?
Re: can we call any procedure from select statement [message #272585 is a reply to message #272564] Fri, 05 October 2007 10:44 Go to previous messageGo to next message
misragopal
Messages: 125
Registered: June 2005
Location: New Delhi, india
Senior Member

thanks, actually this is a hot discussion topis between my friends... so i just want to explore possibilities... specially with Pipelined functions.
Re: can we call any procedure from select statement [message #272587 is a reply to message #272585] Fri, 05 October 2007 10:54 Go to previous messageGo to next message
Michel Cadot
Messages: 64119
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
specially with Pipelined functions

which are functions not procedures.

Regards
Michel
Re: can we call any procedure from select statement [message #272595 is a reply to message #272562] Fri, 05 October 2007 12:37 Go to previous messageGo to next message
Kevin Meade
Messages: 2101
Registered: December 1999
Location: Connecticut USA
Senior Member
Yes, only functions can be called from DML. Additionally, if you consider one difference between a function and procedure being that a procedure is intended to allow the return of data via an out parameter then you can consider the following code which is bad practice but shows valid functions non-the-less.

drop function misragopal_f1 ;
drop function misragopal_f2 ;
drop function misragopal_f3 ;

create or replace function misragopal_f1 return number is
begin
return (1);
end;
/

select misragopal_f1 from dual
/


create or replace function misragopal_f2 (p1 out number) return number is
begin
return (2);
end;
/

select misragopal_f2 from dual
/


create or replace function misragopal_f2 (p1 out number) return number is
begin
return (2);
end;
/

select misragopal_f2(dummy) from dual
/


create or replace function misragopal_f3 (p1 in out number) return number is
begin
return (2);
end;
/

select misragopal_f3(dummy) from dual
/



If executing this, you will see that Oracle will not allow you to call a function from DML with an out parameter (where would it put the out item it is returning?).

Of course this begs the question, why would any one create a function with an out parameter. This is in general poor code practice (although if we look close, Oracle does it in a few places).

Good luck, Kevin
Re: can we call any procedure from select statement [message #272605 is a reply to message #272595] Fri, 05 October 2007 12:58 Go to previous messageGo to next message
Michel Cadot
Messages: 64119
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Kevin don't you see the code tags?

Quote:
Of course this begs the question, why would any one create a function with an out parameter. This is in general poor code practice

I beg to differ.
All C functions return a status as return value.
Many return other values and/or fill buffer inout parameter.
How do you want to code a read that must return:
1/ a status
2/ an error code
3/ fill a buffer
4/ the length of data put in the buffer
Yes, you can do it with a procedure but it is nicer to code "if read(...) = ok then ..." than "read(..., status); if status = ok then ..."

Just my opinion.

Regards
Michel
Re: can we call any procedure from select statement [message #272620 is a reply to message #272605] Fri, 05 October 2007 13:47 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
return an errorcode?
That's why god created exceptions!
(ok, Larry did).
Re: can we call any procedure from select statement [message #272623 is a reply to message #272562] Fri, 05 October 2007 13:52 Go to previous messageGo to next message
Kevin Meade
Messages: 2101
Registered: December 1999
Location: Connecticut USA
Senior Member
Your opinion is quite valid and in many circles this practice is quite acceptable. However, I would consider this simliar to use of goto in plsql. Bad practice, but as always there are a few exceptions to the rule.

The distiniguishing factor I suppose is the idea of business data vs. system meta data.

I would consider this an acceptable function:

create or replace function f_get_emp_name (emp_id_p in number) return varchar2;

I would consider this a horrible function:

create or replace function f_get_emp_name (emp_id_p in number, emp_age_p out number) return varchar2;

I would consider this a useless function:

create or replace function f_get_emp_name (emp_id_p in number, error_code_p out number) return varchar2;

select f_get_emp_name (101) from dual; -- crap, where to put the damed return item???

does not support use directly in DML:
implements an error checking mentality of C which I suppose if you are a C programmer is OK.

I have worked in shops that do this kind of error checking and in shops that don't. If you worked for me and tried it I'd slap you silly for it. In PL/SQL, use exception handlers to catch errors. I have never seen anything but trouble when codes were used to mask errors. This is an old debate.

What do you think? I am comfortable to leave it as "a difference of professional opinion".

Kevin

Re: can we call any procedure from select statement [message #272625 is a reply to message #272623] Fri, 05 October 2007 14:09 Go to previous messageGo to next message
Michel Cadot
Messages: 64119
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Both are valuable ways of writing.

I agree, this "create or replace function f_get_emp_name (emp_id_p in number, emp_age_p out number) return varchar2;" is awful, if you want to return name and age the use "create or replace function f_get_emp_name (emp_id_p in number) return emp_record;" where emp_record contains both age and name.

Using exceptions is great and I use them often. But some sites prefer return code and error message to exception. Each site has its code standard. (Often in this case I internally use exceptions and wrap this internal package to external one that translates exceptions to error messages.)

I programmed for 30 years in many and many languages of all types, procedural, object, internal, and other weird types of programming (logic, math, list... even binary) that does not look of anything else.

Regards
Michel

[Updated on: Sat, 06 October 2007 14:53]

Report message to a moderator

Re: can we call any procedure from select statement [message #272627 is a reply to message #272625] Fri, 05 October 2007 14:17 Go to previous message
Kevin Meade
Messages: 2101
Registered: December 1999
Location: Connecticut USA
Senior Member
thanks, of course you are correct,

when in Rome, do as the Romans do... what choice do you... usually best to follow the pre-existing standard.

But when I am President, it ain't happening.

A pleasure, Kevin.
Previous Topic: ORA-00902: invalid datatype
Next Topic: Converting SQL from MSSQL to ORACLE
Goto Forum:
  


Current Time: Tue Dec 06 10:21:21 CST 2016

Total time taken to generate the page: 0.09727 seconds