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.
- create or replace type MsgType as table of varchar2(4000);
- /
-
- create or replace function f_pipeline_test
- return MsgType
- PIPELINED
- as
- begin
- for i in 1 .. 10
- loop
- pipe row( 'Iteration ' || i || ' at ' || systimestamp );
- dbms_lock.sleep(1);
- end loop;
- pipe row( 'All done!' );
- return;
- end;
- /
-
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.
- SQL> set arraysize 1
- SQL> select * from table( f_pipeline_test );
-
- COLUMN_VALUE
- --------------------------------------------------------------------------------
- Iteration 1 at 14-FEB-08 02.13.18.273988000 PM +08:00
- Iteration 2 at 14-FEB-08 02.13.19.275988000 PM +08:00
- Iteration 3 at 14-FEB-08 02.13.20.277767000 PM +08:00
- Iteration 4 at 14-FEB-08 02.13.21.279591000 PM +08:00
- Iteration 5 at 14-FEB-08 02.13.22.281366000 PM +08:00
- Iteration 6 at 14-FEB-08 02.13.23.283189000 PM +08:00
- Iteration 7 at 14-FEB-08 02.13.24.283965000 PM +08:00
- Iteration 8 at 14-FEB-08 02.13.25.285785000 PM +08:00
- Iteration 9 at 14-FEB-08 02.13.26.286570000 PM +08:00
- Iteration 10 at 14-FEB-08 02.13.27.288387000 PM +08:00
- All done!
-
- 11 rows selected.
-
If you want to perform DML operations in pipeline, you must use autonomous transactions; otherwise, a ORA-14551 error is reported.
- create or replace function f_pipeline_testdml
- return MsgType
- PIPELINED
- as
- begin
- for i in 1 .. 10
- loop
- insert into test values(1);
- pipe row( 'insert into test values( ' || i || ') success at ' || systimestamp );
- dbms_lock.sleep(1);
- end loop;
- pipe row( 'All done!' );
- return;
- end;
- /
-
- SQL> select * from table( f_pipeline_testdml );
- select * from table( f_pipeline_testdml )
- *
- ERROR at line 1:
- ORA-14551: cannot perform a DML operation inside a query
- ORA-06512: at "NING.F_PIPELINE_TESTDML", line 8create or replace function f_pipeline_testdml
- return MsgType
- PIPELINED
- as
- pragma autonomous_transaction;
- begin
- for i in 1 .. 10
- loop
- insert into test values(1);
- commit;
- pipe row( 'insert values ' || i || ' success at ' || systimestamp );
- dbms_lock.sleep(1);
- end loop;
- pipe row( 'All done!' );
- return;
- end;
- /
-
- SQL> select * from table( f_pipeline_testdml );
-
- COLUMN_VALUE
- --------------------------------------------------------------------------------
- insert values 1 success at 14-FEB-08 02.16.47.855158000 PM +08:00
- insert values 2 success at 14-FEB-08 02.16.48.865559000 PM +08:00
- insert values 3 success at 14-FEB-08 02.16.49.867377000 PM +08:00
- insert values 4 success at 14-FEB-08 02.16.50.873154000 PM +08:00
- insert values 5 success at 14-FEB-08 02.16.51.874942000 PM +08:00
- insert values 6 success at 14-FEB-08 02.16.52.880781000 PM +08:00
- insert values 7 success at 14-FEB-08 02.16.53.882543000 PM +08:00
- insert values 8 success at 14-FEB-08 02.16.54.894348000 PM +08:00
- insert values 9 success at 14-FEB-08 02.16.55.896153000 PM +08:00
- insert values 10 success at 14-FEB-08 02.16.56.901904000 PM +08:00
- All done!
-
- 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.
- create or replace function f_pipeline_testdml
- return MsgType
- PIPELINED
- as
- pragma autonomous_transaction;
- begin
- for i in 1 .. 10
- loop
- insert into test values(1);
- pipe row( 'insert values ' || i || ' success at ' || systimestamp );
- dbms_lock.sleep(1);
- end loop;
- pipe row( 'All done!' );
- commit;
- return;
- end;
- /
-
- SQL> select * from table( f_pipeline_testdml );
- 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