ORA-06572 -- any bypass to avoid it?

[huchen]

Hello,

I have a funcion
sf_a(v_1, v_2 out, v3 out) return number

I need to use the return value from sf_a in a view

select decode( sf_a(1, 1, 1),1,'Y','N') flag

and I got error:
ORA-06572: Function sf_a has out arguments

How can I use sf_v return value in select?

Thanks

 

[Beilstwh]

Why does your function have an out parameter? You should only use out parameters in procedures.

Bill
Oracle DBA/Developer
New York State, USA

 

[huchen]

Thank you Bill. We have all kind of things that Oracle allows us to have.
I need to use existing object.
I can write another function to call sf_a to get the result. But I do not want to add a new function to database.
So please let me know if anybody has any idea.
Thanks

 

[Beilstwh]

The problem is that you may not call a function with an out parameter in a select. This can only be overcome by one of the following.

1) Rewrite the function to not have an out parameter.
2) Write a wrapper function to call the other function.
3) Modify the function to make it overloaded so you can have a version that doesn't include the out parameter. See the following link for info

http://download-east.oracle.com/docs/cd/B10501_01/appdev.920/a96624/09_packs.htm#4937

Bill
Oracle DBA/Developer
New York State, USA

 

[carp]

We have all kind of things that Oracle allows us to have."
But it should be clear from what you are seeing that Oracle does NOT allow you to have a function with OUT arguments (well, OK, you can HAVE it, but you can't USE it!). As Bill points out, functions can only have IN arguments.
So - since this function obviously will not run, you are going to have to change it to eliminate the OUT arguments. If this disrupts the logic within your application, then you will need to change the logic as well. If you need to modify and return more than one value within your call, you will probably wind up either using a procedure (which can modify more than one argument in the same call) or creating a composite datatype that can be returned by your function.

 

[SantaMufasa]

...And to clarify...Oracle PROCEDUREs and FUNCTIONs can both have "IN", "OUT", and "IN OUT" arguments. However, if the immediate calling environment is SQL, the the only construct that you can use is a "FUNCTION with IN arguments". Any of the other construct combinations require a call from a PL/SQL (or the like) environment.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I can provide you with low-cost, remote Database Administration services: see our website and contact me via www.dasages.com]

 

[huchen]

My question is answered. Thanks to all.