Usage of Oracle Pipeline functions

Oracle pipeline functions are a special class of functions, and the Oracle pipe function return value type must be a collection, which describes the syntax of the Oracle pipeline function.

In a normal function, the information that is output using dbms_output needs to be returned to the client one time after the server executes the entire function. If you need some information during the execution of real-time output functions of the client, you can use the pipe function (pipeline functions) after oracle9i.

The keyword pipelined indicates that this is an Oracle pipeline function, the return value type of the Oracle pipe function must be a collection, in the function, the pipe row statement is used to return a single element of the collection, and the function ends with an empty return statement to indicate that it has completed.

1 Create or ReplaceType Msgtype as Table  of varchar2(4000);2 /3 4 Create or Replace functionF_pipeline_testreturnMsgtype5pipelined as6 begin7    forIinch 1..TenLoop8     PipeRow'Iteration' ||I|| ' at' ||systimestamp);9Sys.dbms_lock.sleep (1);Ten   EndLoop; One   PipeRow'All done!'); A   return; - End; - /

If the above function does not prompt the error: "error:pls-00201: Must explain the identifier ' Dbms_lock '", it is due to the reasons for authorization, can be resolved by the following scenarios:

Conn System/bitservice@ttonline as sysdba; Grant Execute  on  to TT;

In order to demonstrate the performance of the pipeline function in Sql*plus, we need to arraysize to 1, otherwise the server will return information to the client according to the default 15来, which will affect our test results.

Set 1 Select *  from Table (f_pipeline_test);

If you want to perform DML operations in pipeline, you must use an autonomous transaction, or you will report a ORA-14551 error.

In oracle9205 and later versions, an autonomous transaction is used in the pipeline function, and the transaction must be committed or rolled back before the pipe row, otherwise a ORA-06519 error is reported.