Oracle Pipeline Function usage

Source: Internet
Author: User

Oracle pipeline functions are a special type of functions. oracle pipeline functions must return a set of values. The following describes the syntax of oracle pipeline functions for your reference.

In a common function, the information output by dbms_output must be returned to the client once the server executes the complete function. If you need to output some information about the function Execution Process in real time on the client, you can use the pipeline function (pipeline function) after oracle9i ).

The keyword PIPELINED indicates that this is an oracle pipeline function. the return value type of the oracle pipeline function must be a set. In the function, the pipe row statement is used to return a single element of the set, the function ends with an empty RETURN statement to indicate that it has been completed.

 
 
  1. create or replace type MsgType as table of varchar2(4000);  
  2. /  
  3.  
  4. create or replace function f_pipeline_test  
  5. return MsgType  
  6. PIPELINED  
  7. as  
  8. begin  
  9.    for i in 1 .. 10  
  10.    loop  
  11.    pipe row( 'Iteration ' || i || ' at ' || systimestamp );  
  12.    dbms_lock.sleep(1);  
  13.    end loop;  
  14.    pipe row( 'All done!' );  
  15.    return;  
  16. end;  
  17. /  
  18.  

Run this function in SQL * plus. First, set arraysize to 1. Otherwise, the server will return information to the client based on the default value of 15, which will affect the test result.

 
 
  1. SQL> set arraysize 1  
  2. SQL> select * from table( f_pipeline_test );  
  3.  
  4. COLUMN_VALUE  
  5. --------------------------------------------------------------------------------  
  6. Iteration 1 at 14-FEB-08 02.13.18.273988000 PM +08:00  
  7. Iteration 2 at 14-FEB-08 02.13.19.275988000 PM +08:00  
  8. Iteration 3 at 14-FEB-08 02.13.20.277767000 PM +08:00  
  9. Iteration 4 at 14-FEB-08 02.13.21.279591000 PM +08:00  
  10. Iteration 5 at 14-FEB-08 02.13.22.281366000 PM +08:00  
  11. Iteration 6 at 14-FEB-08 02.13.23.283189000 PM +08:00  
  12. Iteration 7 at 14-FEB-08 02.13.24.283965000 PM +08:00  
  13. Iteration 8 at 14-FEB-08 02.13.25.285785000 PM +08:00  
  14. Iteration 9 at 14-FEB-08 02.13.26.286570000 PM +08:00  
  15. Iteration 10 at 14-FEB-08 02.13.27.288387000 PM +08:00  
  16. All done!  
  17.  
  18. 11 rows selected.  
  19.  

If you want to perform DML operations in pipeline, you must use autonomous transactions; otherwise, a ORA-14551 error is reported.

 
 
  1. create or replace function f_pipeline_testdml  
  2. return MsgType  
  3. PIPELINED  
  4. as  
  5. begin  
  6.    for i in 1 .. 10  
  7.    loop  
  8.    insert into test values(1);  
  9.    pipe row( 'insert into test values( ' || i || ') success at ' || systimestamp );  
  10.    dbms_lock.sleep(1);  
  11.    end loop;  
  12.    pipe row( 'All done!' );  
  13.    return;  
  14. end;  
  15. /  
  16.  
 
 
  1. SQL> select * from table( f_pipeline_testdml );  
  2. select * from table( f_pipeline_testdml ) 
 
 
  1.  *  
  2. ERROR at line 1:  
  3. ORA-14551: cannot perform a DML operation inside a query  
  4. ORA-06512: at "NING.F_PIPELINE_TESTDML", line 8create or replace function f_pipeline_testdml  
  5. return MsgType  
  6. PIPELINED  
  7. as  
  8. pragma autonomous_transaction;  
  9. begin  
  10.    for i in 1 .. 10  
  11.    loop  
  12.    insert into test values(1);  
  13.    commit;  
  14.    pipe row( 'insert values ' || i || ' success at ' || systimestamp );  
  15.    dbms_lock.sleep(1);  
  16.    end loop;  
  17.    pipe row( 'All done!' );  
  18.    return;  
  19. end;  
  20. /  
  21.  
  22. SQL> select * from table( f_pipeline_testdml );  
  23.  
  24. COLUMN_VALUE  
  25. --------------------------------------------------------------------------------  
  26. insert values 1 success at 14-FEB-08 02.16.47.855158000 PM +08:00  
  27. insert values 2 success at 14-FEB-08 02.16.48.865559000 PM +08:00  
  28. insert values 3 success at 14-FEB-08 02.16.49.867377000 PM +08:00  
  29. insert values 4 success at 14-FEB-08 02.16.50.873154000 PM +08:00  
  30. insert values 5 success at 14-FEB-08 02.16.51.874942000 PM +08:00  
  31. insert values 6 success at 14-FEB-08 02.16.52.880781000 PM +08:00  
  32. insert values 7 success at 14-FEB-08 02.16.53.882543000 PM +08:00  
  33. insert values 8 success at 14-FEB-08 02.16.54.894348000 PM +08:00  
  34. insert values 9 success at 14-FEB-08 02.16.55.896153000 PM +08:00  
  35. insert values 10 success at 14-FEB-08 02.16.56.901904000 PM +08:00  
  36. All done!  
  37.  
  38. 11 rows selected.  

In oracle9205 and later versions, when using autonomous transactions in the pipeline function, you must commit or roll back the transaction before pipe row; otherwise, a ORA-06519 error is reported.

 
 
  1. create or replace function f_pipeline_testdml  
  2. return MsgType  
  3. PIPELINED  
  4. as  
  5. pragma autonomous_transaction;  
  6. begin  
  7.    for i in 1 .. 10  
  8.    loop  
  9.    insert into test values(1);  
  10.    pipe row( 'insert values ' || i || ' success at ' || systimestamp );  
  11.    dbms_lock.sleep(1);  
  12.    end loop;  
  13.    pipe row( 'All done!' );  
  14.    commit;  
  15.    return;  
  16. end;  
  17. /  
  18.  
  19. SQL> select * from table( f_pipeline_testdml );  
  20. select * from table( f_pipeline_testdml )  

*
ERROR at line 1:
ORA-06519: active autonomous transaction detected and rolled back
ORA-06512: at "NING. F_PIPELINE_TESTDML", line 10
This is because Bug 9205 is fixed in 2711518, which leads to a change in the behavior of autonomous transactions. If the system is upgraded from a version earlier than 9205 to a later version, ensure that the behavior of the pipeline function is consistent with that of the previous version. oracle provides a 10946 event to set compatibility with the previous version, if you use the select for update cursor in the pipeline function, you must set the event to return to previous features, otherwise commit will cause a ORA-1002 error even before pipe row.

Alter system set event = "10946 trace name context forever, level 8" scope = spfile;
 

Use of oracle custom functions

Oracle functions for Calculating Time Difference

Oracle date functions

Syntax for creating an Oracle package

Oracle TRIM function syntax

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.