1 2 Previous Next 16 Replies Latest reply: Jun 29, 2012 12:33 PM by 946222 RSS

    urgent help

    user13490153
      Hi All,

      I had an employee table .

      emp_id
      dept_id,
      sal,
      last_name
      first_name

      and I have used pipeline function.here is my code.
      ***********************************************************************************
      create or replace type objs as object
      (
      /

      emp_id NUMBER(38),
      dept_id NUMBER(38),
      sal NUMBER(38),
      last_name VARCHAR2(10),
      first_name VARCHAR2(10)
      )


      CREATE OR REPLACE TYPE newtype AS TABLE OF objs;

      create or replace function ptest1
      (code in varchar2) return newtype pipelined
      is
      type tname is table of emp%rowtype
      index by pls_integer;

      result1 newtype;
      begin
      select * bulk collect into result1 from emp;
      for i in result1.first..result1.last
      loop

      pipe row(result1(i));
      end loop;
      return;
      end;
      ************************************************************************************************
      The above code throws error not enough values are provided

      then I have changed the code to

      begin
      select objs(emp_id,dept_id,sal,last_name,first_name) bulk collect into result1 from emp;

      then its working fine.


      but my question is if I have 100 columns its difficult to pass 100 values
      i.e. select objs(emp_id,dept_id,.............100).

      so any suggestions are highly appreciated.


      thanks

      Edited by: user13490153 on Jun 28, 2012 10:36 AM
        • 1. Re: Pipe line function
          sybrand_b
          My suggestion is that it is very strange to want to replace a REF CURSOR by a pipelined function as the latter one will be less performant.

          -----------
          Sybrand Bakker
          Senior Oracle DBA
          • 2. Re: Pipe line function
            946222
            Sybrand, that's not necessarily true. Actually, pipelined functions can be a great optimization tool. Please refer to the article below, written by Adrian Billington:

            http://www.oracle-developer.net/display.php?id=429

            Basically, for some scenarios, using pipelined functions can even outperform an approach using bulk collect / forall.

            Of course, everthing depends on the way the entire process is modeled.
            • 3. Re: Pipe line function
              MichaelS
              any suggestions are highly appreciated.
              you may try
              select value (t)
                bulk collect into result1
                from table (cast (multiset (select * from emp) as newtype)) t
              instead.
              • 4. Re: Pipe line function
                rp0428
                >
                but my question is if I have 100 columns its difficult to pass 100 values
                i.e. select objs(emp_id,dept_id,.............100).

                so any suggestions are highly appreciated.
                >
                It's not difficult at all. You only need to create the column list one time so use String Aggregation to query the ALL_TAB_COLUMNS view for your table to get the column names in one string separated by commas. Then plug that into your code.

                See String Aggregation in question 4. How do I convert rows to columns in the FAQ
                Re: 4. How do I convert rows to columns?

                Using BULK COLLECT in a PIPELINE function like you are doing is poor practice. Number one you don't need it - use a cursor to FETCH the rows - after all you are doling them out one row at a time. You aren't using a LIMIT clause, you are using expensive PGA memory, the method isn't scalable. There are a lot of things wrong with it especially since you don't need bulk anyway.

                Here is sample code that shows how to use a cursor to do this.
                -- type to match emp record
                create or replace type emp_scalar_type as object
                  (EMPNO NUMBER(4) ,
                   ENAME VARCHAR2(10),
                   JOB VARCHAR2(9),
                   MGR NUMBER(4),
                   HIREDATE DATE,
                   SAL NUMBER(7, 2),
                   COMM NUMBER(7, 2),
                   DEPTNO NUMBER(2)
                  )
                  /
                 
                -- table of emp records
                create or replace type emp_table_type as table of emp_scalar_type
                  /
                
                -- pipelined function 
                create or replace function get_emp( p_deptno in number )
                  return emp_table_type
                  PIPELINED
                  as
                   TYPE EmpCurTyp IS REF CURSOR RETURN emp%ROWTYPE;
                    emp_cv EmpCurTyp;
                    l_rec  emp%rowtype;
                  begin
                    open emp_cv for select * from emp where deptno = p_deptno;
                    loop
                      fetch emp_cv into l_rec;
                      exit when (emp_cv%notfound);
                      pipe row( emp_scalar_type( l_rec.empno, LOWER(l_rec.ename),
                          l_rec.job, l_rec.mgr, l_rec.hiredate, l_rec.sal, l_rec.comm, l_rec.deptno ) );
                    end loop;
                    return;
                  end;
                  /
                  
                select * from table(get_emp(20))
                • 5. Re: Pipe line function
                  Solomon Yakobson
                  rp0428 wrote:
                  It's not difficult at all. You only need to create the column list one time so use String Aggregation to query the ALL_TAB_COLUMNS view for your table to get the column names in one string separated by commas. Then plug that into your code.
                  Or simply take advantage of package pipelined functions where record type is automatically mapped to object and object table types:
                  create or replace
                    package pkg1
                      as
                        type emp_table_type
                          is
                            table of emp%rowtype;
                        function get_emp(
                                         p_deptno number
                                        )
                          return emp_table_type
                          pipelined;
                  end;
                  /
                  create or replace
                    package body pkg1
                      as
                        function get_emp(
                                         p_deptno number
                                        )
                          return emp_table_type
                          pipelined
                          is
                          begin
                              for v_rec in (select * from emp where deptno = p_deptno) loop
                                pipe row(v_rec);
                              end loop;
                          end;
                  end;
                  /
                  SQL> select  *
                    2    from  table(
                    3                pkg1.get_emp(20)
                    4               )
                    5  /
                  
                       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
                  
                  SQL> 
                  SY.
                  • 6. Re: Pipe line function
                    rp0428
                    >
                    Or simply take advantage of package pipelined functions where record type is automatically mapped to object and object table types:
                    >
                    Nice! I think that is more what OP is looking for since it obviates the need to itemize the columns.
                    • 7. Re: Pipe line function
                      Billy~Verreynne
                      Paulo Petruzalek wrote:
                      Sybrand, that's not necessarily true. Actually, pipelined functions can be a great optimization tool. Please refer to the article below, written by Adrian Billington:
                      Sybrand did not say that pipeline tables is not an optimisation tool. He said that it is not a substitution for dealing with a ref cursor. And that statement is correct. As the OP's sample code is just plain silly. An excellent example of how NOT to use pipeline tables.
                      Basically, for some scenarios, using pipelined functions can even outperform an approach using bulk collect / forall.
                      Wrong. It is not pipelined tables versus bulk processing. Pipelines are about data transformation primarily. Bulk collection is about context switching primarily. There is no one approach versus another approach in this case.

                      It is about having a hammer and a spanner in the same toolbox and using the correct one for the job. And not in the way that the shoddy pipeline code posted by the OP does.
                      Of course, everthing depends on the way the entire process is modeled.
                      No. It depends on using proper software engineering. Something that some here do not seem to know anything about.
                      • 8. Re: Pipe line function
                        Billy~Verreynne
                        Solomon Yakobson wrote:

                        Or simply take advantage of package pipelined functions where record type is automatically mapped to object and object table types:
                        Nice example. Horrible application of a pipeline table.

                        This is not how a pipeline should be used - moving data from the SQL engine into the PL/SQL engine and then back to the SQL engine. Without adding justification (such as doing complex data transformation) to sending that data via a PL/SQL engine detour.
                        • 9. Re: Pipe line function
                          Solomon Yakobson
                          Billy  Verreynne  wrote:

                          Horrible application of a pipeline table.
                          Agreed. But, as you already mentioned, the sole purpose of this example was to show how to avoid listing columns when such list is lengthy. And I should have mentioned it creates, behind the scenes, 4 system generated types (hidden in 11g). One more thing (and I don't want to hijack this topic). I don't know if it is a bug, but (I am using 10.2.0.4.0 since in 11g such types are hidden and it is a bit more work to show them):
                          SQL> create user u1 identified by u1 default tablespace users quota unlimited on users
                            2  /
                          
                          User created.
                          
                          SQL> grant create session,create table,create procedure to u1
                            2  /
                          
                          Grant succeeded.
                          
                          SQL> create table u1.emp as select * from emp
                            2  /
                          
                          Table created.
                          
                          SQL> connect u1/u1
                          Connected.
                          SQL> create or replace
                            2    package pkg1
                            3      as
                            4        type emp_table_type
                            5          is
                            6            table of emp%rowtype;
                            7        function get_emp(
                            8                         p_deptno number
                            9                        )
                           10          return emp_table_type
                           11          pipelined;
                           12  end;
                           13  /
                          
                          Package created.
                          
                          SQL> create or replace
                            2    package body pkg1
                            3      as
                            4        function get_emp(
                            5                         p_deptno number
                            6                        )
                            7          return emp_table_type
                            8          pipelined
                            9          is
                           10          begin
                           11              for v_rec in (select * from emp where deptno = p_deptno) loop
                           12                pipe row(v_rec);
                           13              end loop;
                           14          end;
                           15  end;
                           16  /
                          
                          Package body created.
                          
                          SQL> select  type_name
                            2    from  user_types
                            3  /
                          
                          TYPE_NAME
                          ------------------------------
                          SYS_PLSQL_76398_66_1
                          SYS_PLSQL_76398_DUMMY_1
                          SYS_PLSQL_76399_9_1
                          SYS_PLSQL_76399_DUMMY_1
                          
                          SQL> 
                          As you can see, user u1 was able to create types without create type privilege.

                          SY.
                          • 10. Re: Pipe line function
                            946222
                            Billy  Verreynne  wrote:

                            Sybrand did not say that pipeline tables is not an optimisation tool. He said that it is not a substitution for dealing with a ref cursor. And that statement is correct. As the OP's sample code is just plain silly. An excellent example of how NOT to use pipeline tables.
                            So you always test a resource with a high complexity implementation? I'm sure the OP is just trying to figure out how pipelined functions work. That's pretty obvious that one shouldn't create a rocket science project in order to try something new.
                            Basically, for some scenarios, using pipelined functions can even outperform an approach using bulk collect / forall.
                            Wrong. It is not pipelined tables versus bulk processing. Pipelines are about data transformation primarily. Bulk collection is about context switching primarily. There is no one approach versus another approach in this case.

                            It is about having a hammer and a spanner in the same toolbox and using the correct one for the job. And not in the way that the shoddy pipeline code posted by the OP does.
                            You have such a closed mind that it makes me wonder how you got an Ace status. Have you read the article I've quoted? Obviously no! Because there are some very clever techniques of optimization involving the use of pipelined functions which can outperform bulk processing, including for data loading purposes. Not just some empty chatter, there is actually plenty of examples and conceptual proofs. With a pipelined function you can replace several forall / bulk collect statements with only one DML operation, so how this is not about context switching? And if you need to use forall and bulk collect, you ARE transforming data as well, so how this is not about data transformation? Take your time and read the article. Maybe you will learn something new today.
                            Of course, everthing depends on the way the entire process is modeled.
                            No. It depends on using proper software engineering. Something that some here do not seem to know anything about.
                            And how is what you said different from what I said? How "Depends on the way the process is modeled" is different from "Depends of using proper software engineering"? I don't know what you are trying to prove here, but I suggest you start thinking a bit more on your answers from now on.
                            • 11. Re: Pipe line function
                              Billy~Verreynne
                              Paulo Petruzalek wrote:

                              So you always test a resource with a high complexity implementation? I'm sure the OP is just trying to figure out how pipelined functions work. That's pretty obvious that one shouldn't create a rocket science project in order to try something new.
                              And exactly where does the OP say that? I see a posting that "+I'm using pipeline functions as follows+". And what followed was a waste of code as it does not make sense to design and write pipelines that way.

                              To experiment with it - does it not make sense to use a somewhat realistic problem? And nor are all real problems complex either.

                              The issue is to take badly designed and written code, and tell the poster how to "fix" the code to make it compile and run. And then sit back thinking that the poster have learned something useful.

                              Poorly designed and badly written code has no place in software engineering - even when testing and experimenting. Doing it properly is not more complex. Is not extra effort. A good programmer by his/her very nature do the basics right.

                              Pointing the poster to a complex posting about a complex topic is not going to tell the poster what the basics are, and how to do that correctly.
                              • 12. Re: Pipe line function
                                946222
                                Billy  Verreynne  wrote:

                                And exactly where does the OP say that? I see a posting that "+I'm using pipeline functions as follows+". And what followed was a waste of code as it does not make sense to design and write pipelines that way.

                                To experiment with it - does it not make sense to use a somewhat realistic problem? And nor are all real problems complex either.

                                The issue is to take badly designed and written code, and tell the poster how to "fix" the code to make it compile and run. And then sit back thinking that the poster have learned something useful.
                                Actually the OP never said what are his real intentions with the code. He just asked about how to make it work. And if you just assume that "what followed was a waste of code" you aren't helping either. Even if that you say is truth (I'm not saying that it is or it's not), there are better ways to say that. Like, for instance, you could explain why a pipelined function would be a bad idea in that case and why you would use a ref cursor instead. This way you would really be helping. Sitting there and criticizing other people's code isn't gonna make a difference for the community. Actually you are just making this an hostile environment for the begginers and other people trying to learn something.
                                Billy  Verreynne  wrote:

                                Poorly designed and badly written code has no place in software engineering - even when testing and experimenting. Doing it properly is not more complex. Is not extra effort. A good programmer by his/her very nature do the basics right.

                                Pointing the poster to a complex posting about a complex topic is not going to tell the poster what the basics are, and how to do that correctly.
                                The post was not really that complex as it's extremelly well written. Also, it has many references to other articles that can help with the basics. And before you ask, I have nothing to do with the author, neither know him. I just like his work.
                                • 13. Re: Pipe line function
                                  rp0428
                                  >
                                  You have such a closed mind that it makes me wonder how you got an Ace status.
                                  >
                                  Interesting - most people would not start off with their weakest argument. A statement like that pretty much says that you don't want to be taken seriously.

                                  But it is easy to answer your question. Here is the home page for the Oracle ACE Program.
                                  http://www.oracle.com/technetwork/community/oracle-ace/index.html

                                  If you read the FAQ you will find this question
                                  >
                                  Who makes the decision about entry into the program?

                                  A nomination committee comprising members of the Oracle ACE Program Office and product management teams review every completed nomination form.
                                  >
                                  So is it your intent to question the judgement of the Oracle ACE Program Office and product management teams that selected Billy for the ACE program?
                                  If so, you can certainly communicate with them and inform them of the error of their ways. Be sure to provide your credentials so they can give your input the weight that it deserves.

                                  For those interested folk here is Billy's ACE profile
                                  http://apex.oracle.com/pls/otn/f?p=19297:4:951556196670901::NO:4:P4_ID:1200
                                  >
                                  Have you read the article I've quoted? Obviously no! Because there are some very clever techniques of optimization involving the use of pipelined functions which can outperform bulk processing, including for data loading purposes. Not just some empty chatter, there is actually plenty of examples and conceptual proofs.
                                  >
                                  Have YOU read the article you quoted? Obviously no! Because you are drawing conclusions which are not stated, or implied, in the article. The article is about
                                  'improving performance with pipelined table functions'. The article first shows a simple basic pipelined function and then makes this statement.
                                  >
                                  Considering that this is my first and most basic pipelined function implementation, the improvement in performance shown above is quite respectable. However, it is not quite the performance I can get when using a simple BULK COLLECT and FORALL solution (which runs in just over 5 seconds in my tests), so I will need to make some modifications to my pipelined function load.
                                  >
                                  Notice the text stating 'it is not quite the performance I can get when using a simple BULK COLLECT and FORALL solution'. The author is alerting readers to the fact that a 'simple' bulk solution is being used for comparison - not a tuned-to-the-hilt bulk solution. The author is trying to illustrate how to write and tune pipelined functions, not bulk and forall implementations, so as with all good test scenarios the BULK COLLECT and FORALL is constant and unchanged (untuned) for the various pipelined scenarios being presented.

                                  The author never draws or states a conclusion that pipelined implementations outperform bulk processing. The bulk processing example being used for comparison isn't even presented for review but the author stated 'simple'.
                                  >
                                  With a pipelined function you can replace several forall / bulk collect statements with only one DML operation, so how this is not about context switching?
                                  >
                                  This conclusion is not supported by the article either. The solution presented by the article does not replace 'several forall / bulk collect statements with only one DML operation' so how can you draw this conclusion? In fact the article clearly states the the author ADDED bulk collect in order to achieve the performance improvements cited in the article. Thus the article's solution uses a combination of a pipelined function to automate the parallelization of the insert and the bulk collect to reduce the context switching.
                                  >
                                  And if you need to use forall and bulk collect, you ARE transforming data as well, so how this is not about data transformation?
                                  >
                                  Billy never said the example was not about data transformation. You are misinterpreting what he said which was this
                                  >
                                  It is not pipelined tables versus bulk processing. Pipelines are about data transformation primarily. Bulk collection is about context switching primarily. There is no one approach versus another approach in this case.
                                  >
                                  So he stated, correctly, what each technology is primarily for. The 'pipeline' in pipelined functions is about using a function to transform data. That is, the function can be treated as a TABLE even though is it not a table. The actual data can come from anywhere and is TRANSFORMED to appear as if it were table data.

                                  Bulk collection (bulk processing) is used to reduce context switches between the SQL and PL/SQL engines. Some people, perhaps you, confuse the two because NEITHER ONE can be implemented without using PL/SQL code and any you write for pipelining or bulk processing will likely have some sort of transformation in it. Those transformations are not an integral part of either technology, however.
                                  Take your time and read the article. Maybe you will learn something new today.
                                  >
                                  Right back at you! Reread the entire thread in context and maybe YOU will learn something new today.

                                  And be sure to let us know how your communique with the ACE program management turns out.
                                  • 14. Re: Pipe line function
                                    Solomon Yakobson
                                    Paulo Petruzalek wrote:

                                    Even if that you say is truth (I'm not saying that it is or it's not), there are better ways to say that. Like, for instance, you could explain why a pipelined function would be a bad idea in that case and why you would use a ref cursor instead.
                                    I have no idea what in Billy's post ticked you off. Just re-read Billy's post:

                                    This is not how a pipeline should be used - <font color=red>moving data from the SQL engine into the PL/SQL engine and then back to the SQL engine. Without adding justification (such as doing complex data transformation) to sending that data via a PL/SQL engine detour</font>.

                                    All Billy is saying why would one write a table function (pipelined or not) that issues a select, collects results into a nested table, passes nested table back to SQL which applies table operator to it to convert it into SQL table, fetches from it an produces absolutely same result set as we would by simply issuing that original select statement to begin with? This obviously needs justification. And one of them, as Biily noted, is some complex transformation where PL/SQL is more suitable than SQL.

                                    And I would add the following. What is main advantage of pipelined function? If regular table function builds complete collection and only then returns it to SQL which means SQL waits all that time while collection is being built and also memory used by collection can be substantial. Pipelined function allows you to build just one row and pipe it to SQL right away and while SQL is working with that piped row pipelined dunction continues and builds next row. Therefore by using bulk collect OP negates the sole idea behind pipelined function. And if, as Billy noted, using table function in OP given code needs justification, using pipelined table function the way OP does it makes absolutely no sense.

                                    SY.
                                    1 2 Previous Next