This discussion is archived
6 Replies Latest reply: Jan 3, 2013 10:17 AM by rp0428 RSS

record vs object vs globle table in pipelined function

947771 Newbie
Currently Being Moderated
i want to make pipeline function , i show it can be made in following ways
please suggest which one is better in performance and maintenance.

1)
create type my_tab_type is object
(prodid number, a varchar2(1), b varchar2(1),
c varchar2(1), d varchar2(1), e varchar2(1))
.
/

create type my_tab_type_coll is table of my_tab_type;
/

create or replace function get_some_data (p_val in number)
return my_tab_type_coll pipelined is
begin
FOR i in (select * from my_table where prodid=p_val) loop
pipe row(my_tab_type(i.prodid,i.a,i.b,i.c,i.d,i.e));
end loop;
return;
end;
/

SELECT * FROM table(get_Some_Data(3));

2)
one can create globle tem table "Tlb_3". then can make a package like fllowing
create or replace
PACKAGE pk1
AS
TYPE T_type IS TABLE OF Tlb_3%ROWTYPE;
END;

and rest of the thing will be same like first one.

3)
TYPE outrec_typ IS RECORD (
var_num NUMBER(6),
var_char1 VARCHAR2(30),
var_char2 VARCHAR2(30)
);
TYPE outrecset IS TABLE OF outrec_typ;

and rest of the thing will be same like first one

so main question is relating to declaretion of TABLE which is returned.


yours sincerely

Edited by: 944768 on Jan 2, 2013 4:23 AM
  • 1. Re: record vs object vs globle table in pipelined function
    Stew Ashton Expert
    Currently Being Moderated
    Option 2) is very unusual. The temporary table is never actually used to contain any data. I suggest anything unusual will cause maintenance problems, since people will not understand why that table is there.

    Option 3) will actually create the same types as option 1), except that in 3) the names will be system-generated and people will wonder why they are in your data dictionary.

    There should be no difference in performance, but Option 1) will probably be the easiest to maintain if you choose meaningful names.
  • 2. Re: record vs object vs globle table in pipelined function
    BillyVerreynne Oracle ACE
    Currently Being Moderated
    944768 wrote:
    i want to make pipeline function
    Why? The sample code you have posted does not justify using a slower, and less flexible and scalable pipeline table, in comparison with simply using native SQL.

    Pipeline tables are pretty much an exception - and need proper justification when used. Like doing data transformation that is beyond the ability of the SQL engine.

    First make sure you have valid reasons for using a pipeline table - and only then decide on how best to design this pipeline.
  • 3. Re: record vs object vs globle table in pipelined function
    rp0428 Guru
    Currently Being Moderated
    DUPLICATE THREAD!

    How many times do you intend to ask this question?

    This is the same question that you ask, and got answered, six months ago in this thread?
    how to write Function returing table or set of rows.

    And you ask it again a week ago in this thread
    object vs record in pipelined function.

    Have you forgotten those answers already? Why didn't you take the advice given there and perform some tests?

    And you don't seem to acknowledge any of the help you get to your questions by marking them ANSWERED when they have been.

    Please revisit this 32 questions and mark them ANSWERED as appropriate - Total Questions: 73 (32 unresolved)
    >
    i want to make pipeline function , i show it can be made in following ways
    please suggest which one is better in performance and maintenance.
    >
    Why didn't you take the advice given there and perform some tests?

    Option #1, using SQL types is better, especially for maintenance. Also SQL types are required if the function is going to be called from SQL. You can define PL/SQL or %ROWTYPE package variables and use them but Oracle will silently create 'hidden' (in 11g) SQL types and use those.

    See Solomon's explanation and sample code in this recent thread
    Re: Pipe line function

    There certainly isn't any need to create a global temp table just so you can create the %ROWTYPE variable; you can create one of those based on a CURSOR.
  • 4. Re: record vs object vs globle table in pipelined function
    947771 Newbie
    Currently Being Moderated
    need some help
  • 5. Re: record vs object vs globle table in pipelined function
    947771 Newbie
    Currently Being Moderated
    thank u,
    as u have mentiond , "about making of answers".
    I am not able to see my all posts
    how can i see them.

    yours sincerely
  • 6. Re: record vs object vs globle table in pipelined function
    rp0428 Guru
    Currently Being Moderated
    >
    thank u,
    as u have mentiond , "about making of answers".
    I am not able to see my all posts
    how can i see them.
    >
    Use the 'Your Control Panel' link at the top right of the page.

    Then use the 'Your Questions' link at the top right of that page

    That will list all of your questions in sets (e.g. maybe 10 questions per page.
    Every question without a green star is still unresolved.

    Visit those 'unresolved' questions and mark them ANSWERED if they have been.

    If they really have NOT been resolved and are old questions then add another reply to them so they will get bumped to the top of the list where people will notice them again.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points