Re: Use PL/SQL as query

have a look at pipelined functions,
use delete with returning clause

good luck
matthias

Markus Breuer wrote:
> I am looking for an way to use PL/SQL Functions as query. The idea is to
> use an function returning a cursor, which may be evaluated.
> In normal cases a SELECT statement would be used, for example
>
> SELECT * FROM accounts
>
> Instead of this I want to use an PL/SQL Function. The Function is
> located in a package and looks like following:
>
> FUNCTION foo RETURN TABLE OF VARCHAR(128);
>
> When using the OCI i want to call the function as "CALL foo" and expect
> and result cursor. But it seems not to work, oracle is unable to find
> the function. I used the schema and package as prefix, but it never works.
>
> My requirement is to implement a function deleting records, any deleted
> record (or its id) should be returned in a set of rows (like select).
> Can anyone tell me how to to this?
> The main problem is to use the oci interface and the possibly use of
> input host variables. Variables out of the statement are not allowed.
> (this is currently a restriction of my self implemented database layer)
>
> Is there any way the solve this problem with the given restrictions?
> (yes, i thought about a temporary table, but that's not good)
>
> regards markus
>
Received on Thu Dec 18 2003 - 10:24:16 CST