title: PLSQL - create type dynamically using function
body: <p>I would like to implement something like the following pseudo-code. Let say I have table named <b>T_TMP_TABLE</b> with column <b>'hour'</b>, <b>'day'</b>, and <b>'year'</b>. The table name is used as an input parameter for <b>create_types_fn</b> function. Let's assume that I can get a list of column names and store it to <b>column_name_array</b>. 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 (<b>'hour'</b>, <b>'day'</b>, and <b>'year'</b>)</p>
  
  <p>Briefly speaking, I have a table and I need output as table-format with same column names.</p>
  
  <p>Am I able to do this? any suggestion or recommendation would be much appreciated!!</p>
  
  <pre><code>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
  </code></pre>
  
  <p><strong>EDIT</strong> <br/>
  Here is what I am trying to do<br/>
  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..</p>