Avatar of 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
  FOR i in (select * from LOADER.SD_DIM_DATE) loop
  end loop;
Oracle Database

Avatar of undefined
Last Comment

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

         || table_name
         || '_OBJ IS OBJECT ('
         || LISTAGG(
                || ' '
                || 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
                ',' || chr(10)
            WITHIN GROUP (ORDER BY column_id)
         || ');'
         'CREATE OR REPLACE TYPE ' || table_name || '_TAB IS TABLE OF ' || table_name || '_OBJ;'
    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

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

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.