|
|
| Re: How to write a function returning query result [message #41544 is a reply to message #41532] |
Thu, 09 January 2003 09:42   |
Ivan
Messages: 180
Registered: June 2000
|
Senior Member |
|
|
Unfortunately, you'll be able to do that only using dynamic SQL. Either DBMS_SQL built-in package, or EXECUTE IMMEDIATE construct (depending on the Oracle's version you use).
Looking at the statement you wrote, your function must return a string, representing a name of a table ((or a list of tables), or a inline view (or a list of them), or any combination of the above.
The value (string) returned by the function will then be used to build the SQL statement.
The string representing the SQL statement, then, can be used to open a ref cursor using.
To do all of the above, you'll have to work in PL/SQL.
No way to do it in SQL
|
|
|
|
| Re: How to write a function returning query result [message #41579 is a reply to message #41532] |
Tue, 14 January 2003 06:33 |
Adrian Billington
Messages: 139
Registered: December 2002
|
Senior Member |
|
|
Darek
You seem to be asking how to return a resultset and query it like a table. Well, in 8i and upwards, this is commonly known as "select from function". You need two, maybe three, things:-
1) if more than one "column" in results, an object type
2) a nested table type (could use varray) either of a scalar built-in type (eg VARCHAR2) or of your object type. This is a COLLECTION.
3) a function to return the collection type.
Here's a demo:-
<pre style="font-family: Courier New, Monospace; color: Black; font-size: 90%">
816> -------------------------------------------------------------------------------------
816> --Author: A Billington
816> --Date: 14 May 2001
816> --Purpose: Simple demo of selecting from a PL/SQL-loaded nested table.
816> --
816> --Creates: myTableType
816> --
816> -------------------------------------------------------------------------------------
816>
816> --
816> -- Create a nested table type...
816> --
816> create or replace type myTableType as table of varchar2(30);
2 /
Type created.
816>
816> --
816> -- Create a function to return loaded array of nested table type...
816> --
816> create or replace function demo_fnc return myTableType as
2 t_return_array myTableType := myTableType('DBMS_SQL','DBMS_OUTPUT','DBMS_UTILITY');
3 begin
4 return t_return_array;
5 end;
6 /
Function created.
816>
816> --
816> -- Test it with an IN predicate...
816> --
816> select owner, object_name, object_type
2 from all_objects
3 where object_name in (select column_value
4 from TABLE(CAST(demo_fnc() AS myTableType)));
OWNER OBJECT_NAME OBJECT_TYPE
------------------------------ ------------------------------ ------------------
SYS DBMS_OUTPUT PACKAGE
PUBLIC DBMS_OUTPUT SYNONYM
SYS DBMS_SQL PACKAGE
PUBLIC DBMS_SQL SYNONYM
SYS DBMS_UTILITY PACKAGE
PUBLIC DBMS_UTILITY SYNONYM
6 rows selected.
816>
816> --
816> -- Test it with an equality predicate...
816> --
816> select a.owner, a.object_name, a.object_type
2 from all_objects a
3 , table(cast(demo_fnc() as myTableType)) b
4 where a.object_name = b.column_value;
OWNER OBJECT_NAME OBJECT_TYPE
------------------------------ ------------------------------ ------------------
SYS DBMS_OUTPUT PACKAGE
PUBLIC DBMS_OUTPUT SYNONYM
SYS DBMS_SQL PACKAGE
PUBLIC DBMS_SQL SYNONYM
SYS DBMS_UTILITY PACKAGE
PUBLIC DBMS_UTILITY SYNONYM
6 rows selected.
</pre>
Hope this helps.
Regards
Adrian
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
