Pipelined functions have been available in Oracle for several versions and years since 9i Release 1 and there are several related articles on oracle developer. This latest article summarises some of the issues we might encounter when using pipelined functions in our applications. Readers wishing to familiarise themselves with pipelined functions should read this oracle developer. One of the major benefits of pipelined functions is that they can be parallelised. This means that Oracles parallel query mechanism can be exploited to execute PLSQL code in parallel, providing excellent performance gains. For a pipelined function to be parallel enabled, however, it must take its source dataset from a ref cursor parameter rather than have a static cursor defined inside the PLSQL. HP25 The HP25 started on the drawing board as a scientific calculator with a much larger set of functions than the HP21. By the time it was done, it was that and. Color Oracle is a free color blindness simulator for Window, Mac and Linux. It takes the guesswork out of designing for color blindness by showing you in real time. Pass Oracle 1Z0051 exam. Practice questions and Quizzes with full explanation. Interactive eBook and study guide for Oracle Database 11g SQL. Prepare for Oracle certification program with uCertifys interactive courses, exercises, flashcards, quizzes, testprep and videos. Learn and validate your career. Such a function would be used as follows in pseudo code. CURSORSELECT FROM stagingtable lt input rowsource. The cursor parameter in a parallel enabled pipelined function can be defined as either a weak or strong refcursor. The first issue we will see is that when we use a cursor variable to pass to the pipelined function instead of the direct CURSOR function call as above, Oracles parallel DML fails. It should be stated at the outset that this problem only occurs when we are using parallel DML to load a table from the resultset of a pipelined function i. This is important, however, because parallel DML from pipelined functions is how Oracle most commonly demonstrates the technology in its articles and documentation. We will setup a small example of a parallel insert from a parallel pipelined function. We will begin by creating a source and target table of the same structure for simplicity as follows. SQL CREATE TABLE sourcetable x PRIMARY KEY, y. SELECT ROWNUM, CASTNULL AS INTEGER. FROM dual. 7 CONNECT BY ROWNUM lt 1. SQL CREATE TABLE targettable. SELECT 6 FROM sourcetable. We will now create the types required for our pipelined function an object type to define a record and a collection type for buffering arrays of this record, as follows. Quick Sizing Tool Sapphire. SQL CREATE TYPE targettablerow AS OBJECT. INT, y INT. SQL CREATE TYPE targettablerows. AS TABLE OF targettablerow. To complete our setup, we will create a parallel enabled pipelined function in a package as follows. The cursor parameter in this example is defined as a SYSREFCURSOR built in weak refcursor type. SQL CREATE OR REPLACE PACKAGE etlpkg AS. FUNCTION pipelinedfx pcursor IN SYSREFCURSOR. RETURN targettablerows PIPELINED. PARALLELENABLE PARTITION pcursor BY ANY. END etlpkg. Package created. MacImg1.jpg' alt='Oracle Simulator' title='Oracle Simulator' />If you want to play courses directly over the network online, you do not need to install the SCM. Click Play this Course on the summary page. If you want to. The function, implemented in the package body below, simply pipes out the input dataset. Obviously, this is not a true representation of what pipelined functions are needed for, but it keeps the example short and simple. SQL CREATE OR REPLACE PACKAGE BODY etlpkg AS. FUNCTION pipelinedfx pcursor IN SYSREFCURSOR. RETURN targettablerows PIPELINED. PARALLELENABLE PARTITION pcursor BY ANY IS. TYPE cursorntt IS TABLE OF sourcetableROWTYPE. LOOP. 1. 3 FETCH pcursor BULK COLLECT INTO ntsrcdata LIMIT 1. FOR i IN 1. ntsrcdata. COUNT LOOP. 1. 6 PIPE ROW targettablerow. END LOOP. 2. 1 EXIT WHEN pcursorNOTFOUND. END LOOP. 2. 4 CLOSE pcursor. RETURN. 2. 7 END pipelinedfx. END etlpkg. Package body created. We will now test the function by inserting its output into the target table we created earlier. We enable both parallel query and parallel DML to avoid having to use any hints we already parallel enabled both the source and target tables. Note how we use the CURSOR function to supply the refcursor parameter to our pipelined function. SQL ALTER SESSION ENABLE PARALLEL QUERY. Session altered. SQL ALTER SESSION ENABLE PARALLEL DML. Session altered. 3 INSERT INTO targettable. SELECT 5 FROM TABLE. CURSORSELECT FROM sourcetable. DBMSOUTPUT. PUTLINE SQLROWCOUNT rows inserted. ROLLBACK. 1. 00. 00 rows inserted. PLSQL procedure successfully completed. As we are using a refcursor parameter in our pipelined function, we might wish to use a cursor variable instead of a direct CURSOR function call. For example, our source data cursor might be large and complex. Therefore, we might wish to avoid having to embed a large SQL statement in a packaged function call. The following example demonstrates, however, that this will not work with parallel DML statements. This is true of all versions up to and including 1. SQL DECLARE. 3 rc SYSREFCURSOR. OPEN RC FOR SELECT FROM sourcetable. INSERT INTO targettable. SELECT 1. 1 FROM TABLEetlpkg. DBMSOUTPUT. PUTLINE SQLROWCOUNT rows inserted. ROLLBACK. ERROR at line 1. ORA 1. 28. 01 error signaled in parallel query server P0. ORA 0. 10. 08 not all variables bound. ORA 0. 65. 12 at line 9. The cursor variable method works in serial mode. If we disable parallel DML, the statement succeeds as follows. SQL ALTER SESSION DISABLE PARALLEL DML. Session altered. SQL DECLARE. SYSREFCURSOR. 7 OPEN RC FOR SELECT FROM sourcetable. INSERT INTO targettable. SELECT 1. 1 FROM TABLEetlpkg. DBMSOUTPUT. PUTLINE SQLROWCOUNT rows inserted. ROLLBACK. 1. 00. 00 rows inserted. PLSQL procedure successfully completed. This issue has been recorded as bug 5. One of the uses for pipelined functions is to replace row by row insertsupdates with a piped rowsource that is bulk loaded. For example, the following traditional PLSQL based ETL technique is slow and inefficient. FOR rec IN SELECT FROM sourcedata LOOP. A variables. INSERT INTO tableA VALUES. Assuming the prepare tableA variables stage in the above pseudo code is sufficiently complex to warrant the use of PLSQL, pipelined functions can be used to exploit bulk SQL techniques as follows. INSERT INTO tableA. FROM TABLE pipelinedfx CURSORSELECT FROM sourcetable. We can achieve some good performance gains by adopting this technique, especially if we use parallel enabled pipelined functions. There is, however, an issue with wide records where the efficiency of pipelined functions degrades quite dramatically to the point where the row by row alternative is faster. To demonstrate this issue, we are going to manufacture some variable width records and simply compare the time it takes to load one table using row by row inserts and bulk insert from a pipelined function. We will begin by creating a source and target table with just three columns to demonstrate the type of comparison we will be making. SQL CREATE TABLE src. SELECT xxxxxxxxxx AS c. AS c. 2. 6, xxxxxxxxxx AS c. FROM dual. 8 CONNECT BY ROWNUM lt 5. SQL CREATE TABLE tgt. SELECT 4 FROM src. WHERE ROWNUM lt 1. We will now create an ETL package containing the two methods of loading that we wish to compare. The specification is as follows. SQL CREATE PACKAGE etlpkg AS. PROCEDURE rowbyrow. PROCEDURE bulkfrompipeline. TYPE pipedrows IS TABLE OF tgtROWTYPE. FUNCTION pipelinedfxpcursor IN SYSREFCURSOR. RETURN pipedrows PIPELINED. END etlpkg. Package created. Note that we have defined a PLSQL based collection type for our pipelined function.