6 Replies Latest reply: Jan 3, 2013 12:17 PM by rp0428 RSS

    record vs object vs globle table in pipelined function

    947771
      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
          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
            Billy~Verreynne
            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
              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.
              • 5. Re: record vs object vs globle table in pipelined function
                947771
                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
                  >
                  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.