0

I want to write a function that returns table records that I can show in my app's grid. My app shall be oblivious to the table's structure. I want it to be still working when someone adds columns to the table or removes them.

In my app it shall look something like this:

myQuery = "select * from table(myfunction)";
...

The function should hence be something like:

CREATE OR REPLACE FUNCTION myfunction RETURN TABLE OF mytable%ROWTYPE AS ...

But RETURN TABLE OF mytable%ROWTYPE is not allowed. One has to create an SQL type.

But CREATE TYPE table_of_mytable_rows IS TABLE OF mytable%ROWTYPE is not allowed either.

So, is there a way to achieve exactly what I want?

As an alternative I thought of working with IDs. This would make my app's code look something like this:

myQuery = "select * from mytable where rowid in (select * from table(myfunction))";
...

And the function would then be

CREATE OR REPLACE FUNCTION myfunction RETURN TABLE OF UROWID AS ...

But again, RETURN TABLE OF UROWID is not allowed. Neither is creating the SQL table type with CREATE TYPE table_of_rowids IS TABLE OF UROWID.

I know I could create an object type resembling the table's primary key columns and then create a table type on this object. Then my app would have to know the table's primary key in order to

myQuery = "select * from mytable where (key1, key2) in (select key1, key2 from table(myfunction))";
...

I would much prefer my function to return table rows or rowids. Is this possible?

I'm currently still working on Oracle 11.2, but I would also be interested in solutions for newer versions.

Thorsten Kettner
  • 89,309
  • 7
  • 49
  • 73
  • 1
    Why do you want a function at all - why not just query the table directly? Or, if you're trying to hide it logic used in the function to determine which rows are returned, have the function return a ref cursor opened for your underlying query (something like [this](https://stackoverflow.com/a/51252457/266304) perhaps)? – Alex Poole Jun 05 '19 at 16:28
  • @Alex Poole: The real scenario is much more complicated of course :-) This is about reporting batch logs to a user, but which logs are currently interesting to a user depends on who this user is and what their role in the company is. There is quite some dynamic SQL inside the function. What would I gain from returning a ref cursor? Would my app (let it be C#) be able to access it? – Thorsten Kettner Jun 05 '19 at 16:32
  • 1
    If you call the function as a statement rather than a query, I don't see why not; I've linked to a Java example, but I think [this](https://stackoverflow.com/a/4126886/266304) might be what you're looking for? Or [this](https://stackoverflow.com/q/32953374/266304)? (I don't use C# so can't be too sure!) – Alex Poole Jun 05 '19 at 16:35
  • 2
    @Alex Poole: Wow, yes, I didn't even think I would be able to access a ref cursor from outside of PL/SQL, but you are right, there is an `OracleRefCursor` in `OracleManagedDataAccess`, which is the .NET library I am using. Thank you very much, Alex, I will use a ref cursor. – Thorsten Kettner Jun 05 '19 at 16:45
  • Yes.. Almost all popular high-level languages with Oracle db APIs would generally have an interface to deal with ref cursors. – Kaushik Nayak Jun 05 '19 at 17:00

0 Answers0