We help IT Professionals succeed at work.

Oracle create type table from existing table%rowtype ?

Scarlett72
Scarlett72 asked
on
2,770 Views
Last Modified: 2018-02-02
Hi, I'm trying to create a test pipelined table function.  In the first declaration then i am receiving the following error:  

"Identifier LOADER.SD_DIM_DATE must be declared."

How do I create a type based on an existing object, ie a table or cursor?

create type tDateDim AS table of LOADER.SD_DIM_DATE%ROWTYPE;
/
create or replace function tvf_DateDimm
return LOADER.SD_DIM_DATE%ROWTYPE pipelined is
begin
  FOR i in (select * from LOADER.SD_DIM_DATE) loop
    pipe row(I.REPORT_MONTH, I.REPORT_MONTH_DESC)
  end loop;
  return;
end;
Comment
Watch Question

Sean StuberDatabase Developer & Administrator
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2012

Commented:
you can't create a type directly off another object like you can with a pl/sql declaration.
Sean StuberDatabase Developer & Administrator
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2012

Commented:
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

Open 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.
CERTIFIED EXPERT
Most Valuable Expert 2012
Distinguished Expert 2019

Commented:
>>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?

Author

Commented:
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...
Database Developer & Administrator
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2012
Commented:
This problem has been solved!
(Unlock this solution with a 7-day Free Trial)
UNLOCK SOLUTION

Author

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