How to make a function that takes code like "SELECT * FROM SOME_TABLE" as an input and returns a table as an output?

Question

I want to create a function that takes some code as an input (e.g. Select * FROM SOME_TABLE) and returns the result of a query as an output.

I want to use it in procedures in order to return tables as a result.

It should look like this:

BEGIN
    --some procedure code
    CREATE TABLE SOME_TABLE as Select * FROM ...;
    Select * FROM table(my_function('Select * FROM SOME_TABLE'));
END;

Important tips:

1. The resulting table can have multiple columns, from 1 to +inft
2. The resulting table can have multiple rows, from 1 to +inft
3. So the size of a table can be both very small or very large.
4. The input query can have several where, having, partition, and other Oracle constructions.
5. I want to have a table as an output, not DBMS_OUTPUT.
6. I can't install any modules/applications, or use other languages hints. However, I can manually create types, functions, procedures.

I tried to search in the net but could not find a solution that meets all my expectations. The best link I've found was this:

https://sqljana.wordpress.com/2017/01/22/oracle-return-select-statement-results-like-sql-server-sps-using-pipelined-functions/

Answer 1

DBMS_SQL.RETURN_RESULT works if your "code" is a select query

DECLARE
l_cur SYS_REFCURSOR;
l_query VARCHAR2(4000) := 'select * from SOME_TABLE';
BEGIN
    OPEN l_cur for l_query;
    DBMS_SQL.RETURN_RESULT(l_cur);
END;
/