Can you call a stored proc from a Select Statement?

[TGJ]

Hello, I am wondering if it is possible to call a stored procedure from a select statement?

Basically what I want to do looks like this: (DO_STUFF is the stored proc)

SELECT A.ID, A.NAME, A.AMOUNT,
DO_STUFF(A.AMOUNT)

FROM SOMETABLE A


Any help on how I can accomplish this or if it even can be done is appreciated.

 

[SantaMufasa]

TGJ, Although you cannot do what you want directly with a procedure, you can, instead:

CREATE OR REPLACE [B]FUNCTION[/B] DO_STUFF (VAL_IN NUMBER) RETURN <some datatype> IS
   BEGIN
      <logic here>;
      <some other PROCEDURE here, if you wish>;
      RETURN <some expression of some datatype>;
   END;
/

Let us know if this answers your question.


Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[jimirvine]

Just wait for the

ORA-04091

 

[SantaMufasa]

An "ORA-04091" error ("table <table_name> is mutating, trigger/function may not see it") is certainly not a forgone conclusion. If you ensure that the function does not modify a table, then there is no error.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[jimirvine]

I agree Dave. However, it is a very real risk. Exactly what was the OP trying to do? We don't know because neither of us actually asked the OP.
The (intended light hearted) point I was trying to make, was that if you tell someone that they can do such a thing, when they have a lack of knowledge to the extent that they don't know whether a procedure can be called from a sql statement or not, you are opening the door to issues if you do not point out the potential problems and simply offer a generic workaround. I respect you knowledge, Dave, but I think that rather than offering a solution to an unknown problem, more info should have been asked for.
No offence was intended nor sleight on your expertise.

 

[SantaMufasa]

Jim, No offence or sleight taken.

Your point about avoiding any INSERT, UPDATE, or DELETE during execution of a "SELECT...<function>..." is very important.

My including the "no updating" provision in my original suggestion would have only improved it.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]