3

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? – Andrew Brennan Jun 23 '14 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. – Justin Cave Sep 7 '15 at 9:19
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)
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.

| improve this answer | |
  • 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 – Justin Cave Mar 18 '16 at 17:07
  • @JustinCave Yes, that would be cleaner and less work – kevinsky Mar 20 '16 at 13:26

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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