How to create a function(table_name) that return a collection in Oracle

functionsoracleplsqlstored-procedures

I need to create a function in Oracle that accepts a table name and return a collection the content of which is based the table that accepted.

I've been doing some search, many examples are of the form:

first, define a table type;
then, fill a table of that type and return it.

But I won't know the structure of the collection that need to be returned until the function is called, so I can not define a table type at the time of programming.

How can I make it?

Best Answer

I haven't syntax checked this but it should put you on the right path.

This should be put in a package but for demonstration purposes...

PROCEDURE GET_COLUMNS(table_name_in IN VARCHAR2, v_query_out OUT sys_refcursor)
IS
v_count NUMBER(9);
CURSOR the_columns IS 
Select COLUMN_NAME 
from all_tab_columns 
where table_name='table_name_in';
v_query VARCHAR2(4000):= 'Select  ';
BEGIN
IF table_name IS NOT NULL THEN
--requires permission to access dba_tables, if not available use ALL_TABLES
--or USER_TABLES
   SELECT count(*)
   INTO v_count  
   FROM dba_tables
   where table_name = table_name_in;
--might need the schema name too in a multi schema environment
   IF v_count <> 1 THEN
      RAISE bad_table_name;
   END IF;
ELSE
     RAISE bad_table_name;
END IF;

for all_columns in the_columns LOOP
v_query := vquery || all_columns.COLUMN_NAME ||',';
END LOOP;
--trim off the last comma
v_query := SUBSTR(v_query, 1, LENGTH(v_query) - 1);
v_query := v_query ||' from ' || table_name_in;

OPEN v_query_out for v_query;

EXCEPTION
when bad_table_name THEN
--log an error message
DBMS_OUTPUT.PUT_line('Table name is not accessible or does not exist');
RAISE; --could raise an custom exception if more detailed logging required
END;

This should work on Oracle 9,10 and 11 and could be simplified for 12 by using RETURN_RESULT.

Related Question