CODEWITHSUNDEEP
sqlplsqloracle-sqldeveloper
Adrian
Adrian

Reputation: 836

PLSQL - create type dynamically using function

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..

Upvotes: 1

Views: 875

Answers (1)

Justin Cave
Justin Cave

Reputation: 231661

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.

Upvotes: 2

Related Questions