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;
Scarlett72Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

sdstuberCommented:
you can't create a type directly off another object like you can with a pl/sql declaration.
0
sdstuberCommented:
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.
0
slightwv (䄆 Netminder) 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?
0
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

Scarlett72Author 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...
0
sdstuberCommented:
a pipelined function returns a table type.

That table type is a collection of some scalar type (like number, varchar2, clob, etc)  or an object  type.
If you want your function to return multiple columns like those of a cursor, then you want an object type.
Inside your pipelined function you do whatever you want to construct each object then pipe those objects out to form your collection


So, if you use the DDL from my queries above you'll get EMP_OBJ (that looks like the EMP table)  and EMP_TAB which is a collection of EMP_OBJ.

You can then use them in a function like this..


CREATE OR REPLACE FUNCTION get_employees(p_department IN NUMBER)
    RETURN emp_tab
    PIPELINED
AS
    -- Note I return an object type in the cursor
    -- alternatively I could return the columns and then fetch them
    -- individually into the object fields
    -- I find this way a little easier.
    CURSOR cur_employees
    IS
        SELECT emp_obj(
                   empno,
                   ename,
                   job,
                   mgr,
                   hiredate,
                   sal,
                   comm,
                   deptno
               )
          FROM emp
         WHERE p_department IS NULL OR deptno = p_department;

    v_emp   emp_obj;
BEGIN
    OPEN cur_employees;

    LOOP
        FETCH cur_employees INTO v_emp;

        EXIT WHEN cur_employees%NOTFOUND;
        PIPE ROW (v_emp);
    END LOOP;

    RETURN;
END;

Open in new window


and then you can use your function as  a data source


select * from table(get_employees(10))

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7782 CLARK      MANAGER         7839 09-JUN-81       2450                    10
      7839 KING       PRESIDENT            17-NOV-81       5000                    10
      7934 MILLER     CLERK           7782 23-JAN-82       1300                    10

Open in new window



select * from table(get_employees(20));

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7369 SMITH      CLERK           7902 17-DEC-80        800                    20
      7566 JONES      MANAGER         7839 02-APR-81       2975                    20
      7788 SCOTT      ANALYST         7566 19-APR-87       3000                    20
      7876 ADAMS      CLERK           7788 23-MAY-87       1100                    20
      7902 FORD       ANALYST         7566 03-DEC-81       3000                    20

Open in new window


select * from table(get_employees(null));

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7369 SMITH      CLERK           7902 17-DEC-80        800                    20
      7499 ALLEN      SALESMAN        7698 20-FEB-81       1600        300         30
      7521 WARD       SALESMAN        7698 22-FEB-81       1250        500         30
      7566 JONES      MANAGER         7839 02-APR-81       2975                    20
      7654 MARTIN     SALESMAN        7698 28-SEP-81       1250       1400         30
      7698 BLAKE      MANAGER         7839 01-MAY-81       2850                    30
      7782 CLARK      MANAGER         7839 09-JUN-81       2450                    10
      7788 SCOTT      ANALYST         7566 19-APR-87       3000                    20
      7839 KING       PRESIDENT            17-NOV-81       5000                    10
      7844 TURNER     SALESMAN        7698 08-SEP-81       1500          0         30
      7876 ADAMS      CLERK           7788 23-MAY-87       1100                    20
      7900 JAMES      CLERK           7698 03-DEC-81        950                    30
      7902 FORD       ANALYST         7566 03-DEC-81       3000                    20
      7934 MILLER     CLERK           7782 23-JAN-82       1300                    10

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Scarlett72Author 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.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.