Oracle create type table from existing table%rowtype ?

you can't create a type directly off another object like you can with a pl/sql declaration.

you can generate DDL for an object and a related table by querying an existing table's columns


That might look someting like this...

  SELECT    'CREATE OR REPLACE TYPE '
         || table_name
         || '_OBJ IS OBJECT ('
         || LISTAGG(
                   column_name
                || ' '
                || CASE data_type
                       WHEN 'NUMBER' THEN 'NUMBER(' || data_precision || ',' || data_scale || ')'
                       WHEN 'VARCHAR2' THEN 'VARCHAR2(' || data_length || ')'
                       WHEN 'CHAR' THEN 'CHAR(' || data_length || ')'
                       ELSE data_type
                   END,
                ',' || chr(10)
            )
            WITHIN GROUP (ORDER BY column_id)
         || ');'
             ddl_for_object,
         'CREATE OR REPLACE TYPE ' || table_name || '_TAB IS TABLE OF ' || table_name || '_OBJ;'
             ddl_for_table
    FROM user_tab_cols
   WHERE table_name = 'EMP'
GROUP BY table_name

Select allOpen in new window

The query is just a template.  You may need to expand it for timestamps or intervals with varying resolutions or to specify byte vs char in lengths.

>>Hi, I'm trying to create a test pipelined table function

Do you have a requirement to create a pipelined function from an existing table definition or are you just wanting to play around with pipelined functions?

Hi, I am playing around trying to understand how they work.  Do I have to create an explicit custom type each time I want to create a pipelined function, ie if I want to return a dataset that's got lot's of columns.  All examples online are very generic and use examples that don't seem practical.  ie, iterating from 1 .. 10 etc...I'd like an example of returning data from a table or cursor or query ... I'm not even sure if this is possible...

Thanks sdstuber, apologies for the late reply.  This whole concept has been confusing to me, but I think I am coming around, have been able to create what I wanted with the help of this post.