CODEWITHSUNDEEP
sqloracle-databaseplsql
Adrian
Adrian

Reputation: 836

Returning a 'table' (without pre-defining column names) from a PL/SQL function

For your information, my question is based on this web-tutorial http://www.adp-gmbh.ch/ora/plsql/coll/return_table.html

As you may see, the function returns a 'table' and I need to create a new object type that contains the fields that are going to be returned. My question is that I do not want to pre-define the object type because I wan to create generic function that can generate any table.

Any idea?!!

Upvotes: 2

Views: 238

Answers (1)

Justin Cave
Justin Cave

Reputation: 231671

I would strongly suspect that you don't want to do this. Dynamically creating object types and building this sort of flexibility in PL/SQL is almost always a recipe for ruin. You'll end up writing extremely complicated dynamic SQL everywhere (both in the callers and in your functions) which will make your code far, far harder to read and debug. When your code gets that complex, you're better off writing frameworks that generate the code but that leaves you with the much, much harder problem of writing code that generates code that solves your problem rather than just writing code that solves your problem.

That said, if you are truly, truly determined, it is possible to build a completely generic pipelined table function. That involves some deep, deep wizardry using the Oracle Data Cartridge framework. It's quite cool. But the number of people that could realistically support and extend this sort of code is very small.

With slightly less wizardry (but still way, way more than I would suggest for virtually any organization), you can build a flexible pipelined table function that relies on returning types from a pre-defined type hierarchy. That's more likely to be supportable but it assumes that you can build your type hierarchy up front.

Upvotes: 4

Related Questions