PLSQL - create type dynamically using function

Question

I would like to implement something like the following pseudo-code. Let say I have table named T_TMP_TABLE with column 'hour', 'day', and 'year'. The table name is used as an input parameter for create_types_fn function. Let's assume that I can get a list of column names and store it to column_name_array. Now, I need to create "custom type" using those column names. The reason is that the function will return table as an output, and the columns of the returned table would be the same ('hour', 'day', and 'year')

Briefly speaking, I have a table and I need output as table-format with same column names.

Am I able to do this? any suggestion or recommendation would be much appreciated!!

CREATE OR REPLACE FUNCTION create_types_fn (table_name in varchar2)
....
  begin      
    array column_name_array = get_column_name_in_array_by_table_name(table_name)

    CREATE OR REPLACE TYPE my_type AS OBJECT (
     column_name_array(0) NUMBER,
     column_name_array(1) NUMBER,
     column_name_array(2) VARCHAR2(30)    
    );

    CREATE OR REPLACE type my_table AS TABLE OF my_type ;

    select * bulk collect into my_table ;
  end

EDIT
Here is what I am trying to do
I am trying to compare two tables and return rows if there are any difference. So, I think the output should be table-format. Since every table has different column names, I think it would be nice if I can make generic function..

EXECUTE IMMEDIATE may be your friend. Docs here. Share and enjoy. — Bob Jarvis - Слава Україні
– Bob Jarvis - Слава Україні, Commented Oct 9, 2014 at 16:59
All references to the type would have to be dynamic as well though. Creating database objects on the fly is usually not a good idea. You couldn't use the not-yet-created table type as a return type for a function, unless you created that dynamically, which means calls to it would have to be dynamic, etc. Doesn't sound like a nice road to go down. What is calling the function and how will it use the result; maybe a ref cursor would work instead? — Alex Poole
– Alex Poole, Commented Oct 9, 2014 at 17:34
You could use dynamic SQL to create your object type and to create your table type. You could then write a dynamic PL/SQL block that would declare an instance of the new table type and populate it via your BULK COLLECT. But you couldn't then return an instance of the new table type from your function (short of casting it to a SYS.ANYDATA and then trying to code something in the caller that would somehow know how to deal with that usefully. I would strongly suggest taking a step back because that sort of solution would quickly get into the weeds — Justin Cave
– Justin Cave, Commented Oct 9, 2014 at 17:36
I don't really understand what you are trying to do. Maybe you should elaborate a little on this, so we could suggest some other tool ? As of how, the only use case I could see for generating type dynamically as you intend would be writing some "DB bootstrapper" or "installer" -- that is, building some kind of skeleton database, and then letting the programmer develop on top of that. — Sylvain Leroux
– Sylvain Leroux, Commented Oct 9, 2014 at 18:03

Justin Cave · Accepted Answer · 2014-10-09 19:30:49Z

2

If you are trying to compare the data in two different tables, you would almost certainly want to use the dbms_comparison package rather than writing your own. That populates a generic structure rather than creating new types for each table.

answered Oct 9, 2014 at 19:30

Justin Cave

233k25 gold badges378 silver badges395 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

PLSQL - create type dynamically using function

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related