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?

  • A collection of what exactly? Commented Jun 23, 2014 at 12:04
  • Why? Why return a collection rather than a simple cursor? Why does it need to be dynamic? That combination is almost never a good idea. You can use the Data Cartridge functionality to do this oracle-developer.net/display.php?id=422 However that is insanely complicated code. It is very, very likely that there is a better solution to whatever problem you actually have. Commented Sep 7, 2015 at 9:19

1 Answer 1


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)
v_count NUMBER(9);
CURSOR the_columns IS 
from all_tab_columns 
where table_name='table_name_in';
v_query VARCHAR2(4000):= 'Select  ';
IF table_name IS NOT NULL THEN
--requires permission to access dba_tables, if not available use ALL_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;
     RAISE bad_table_name;

for all_columns in the_columns LOOP
v_query := vquery || all_columns.COLUMN_NAME ||',';
--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;

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

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

  • Assuming that returning a cursor is an option rather than using a table function, I'm not sure why you bother to query all_tab_columns rather than just dynamically generating the statement 'select * from ' || table_name_in Commented Mar 18, 2016 at 17:07
  • @JustinCave Yes, that would be cleaner and less work
    – kevinskio
    Commented Mar 20, 2016 at 13:26

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.