Avatar of Scarlett72
Scarlett72
 asked on

Oracle create type table from existing table%rowtype ?

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;
Oracle Database

Avatar of undefined
Last Comment
Scarlett72

8/22/2022 - Mon
Sean Stuber

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

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.
slightwv (䄆 Netminder)

>>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?
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck
Scarlett72

ASKER
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...
ASKER CERTIFIED SOLUTION
Sean Stuber

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
Scarlett72

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