advanced pl/sql – using collections and pipelined functions- reference for the new oracle dba


 


This is for the advanced pl/sql developers and DBA’s. This article gives a detailed example on how to use pl/sql collections and pipelined functions. As a new oracle dba you might read it to get an initial understanding of the syntax and various pl/sql terminolgies like types and pipelined functions.

Read Consistency of Collections with Pipelined Functions [ID 558198.1]  

 
  Modified 24-APR-2008     Type BULLETIN     Status PUBLISHED  
       

In this Document
  Purpose
  Scope and Application
  Read Consistency of Collections with Pipelined Functions
  References


Applies to:

PL/SQL – Version: 9.2.0.1.0 to 11.1.0.6.0
Oracle Server – Enterprise Edition – Version: 9.2.0.8.0 to 11.1.0.6.0
Information in this document applies to any platform.

Purpose

This article discuss how read consistency of the collection is maintained when using pipelined function with an example. This feature is not documented till 11.1.0.7.0.

Scope and Application

This article is intended for experience PLSQL programmer.You need to have good knowledge of pipelined functions and collection datatypes.

Read Consistency of Collections with Pipelined Functions

Read consistency in Oracle :

Read consistency, as supported by Oracle, does the following:

  • Guarantees that the set of data seen by a statement is consistent with respect to a single point in time and does not change during statement execution (statement-level read consistency)
  • Ensures that readers of database data do not wait for writers or other readers of the same data
  • Ensures that writers of database data do not wait for readers of the same data
  • Ensures that writers only wait for other writers if they attempt to update identical rows in concurrent transactions.

The simplest way to think of Oracle’s implementation of read consistency is to imagine each user operating a private copy of the database, hence the multiversion consistency model.

Read Consistency on database table vs PLSQL table with pipelined function :

    Consider that a cursor is opened on a database table, oracle will maintain the image of the table "as of" the cursor was opened.The SCN (System Change Number) marked at the time of the opening of the cursor will be maintained.Whenever a row would be returned, the row would come from that "as of" SCN marked on the table data. Hence, even if we go ahead & change the table data, it should not affect the row-returning engine, because the engine will always look at the table data from that "as of " point in time.

    In the same way, when the cursor is opened on a collection type and data fetched using pipelined, the SCN mechanism is not built into the plsql architecture.The row returning mechanism being iterative, the collection data seems to be always referenced as point-in-time…. i.e. "as of latest".Hence the recent image of the table will be maintained by the cursor. SQL when used to query in memory collections, the data in collections is neither transactional nor shared(It is private to a session). If a session dies, then the memory collection is gone. Also, if you make any changes to collection and the transaction rolls back, the collection changes will not rolled back.This is how the collections are build in PLSQL.

This particular feature of PLSQL function is demonstarted in the below given example.

Sample Code :

CREATE OR REPLACE TYPE test_type AS TABLE OF NUMBER;
/

CREATE OR REPLACE PACKAGE TEST_PACKAGE
IS

TYPE typ_rec IS RECORD (a1 NUMBER);

TYPE typ_tab IS TABLE OF typ_rec;

TYPE t_ref IS REF CURSOR RETURN typ_rec;

FUNCTION test_pipelined RETURN test_type PIPELINED;

PROCEDURE test_proc (po_data OUT t_ref);

end;
/

CREATE OR REPLACE PACKAGE BODY TEST_PACKAGE
is

plsql_table test_type;

FUNCTION test_pipelined RETURN test_type
PIPELINED
AS
BEGIN
FOR i IN 1 .. plsql_table.COUNT
LOOP
PIPE ROW (plsql_table(i));
END LOOP;
RETURN;
END test_pipelined;

PROCEDURE test_proc (po_data OUT t_ref)
AS
BEGIN
    plsql_table := test_type();
    plsql_table.EXTEND;
    plsql_table(plsql_table.last) := 5;
    --Cursor opened on nested table and data access using pipelined function
    OPEN po_data FOR
SELECT * FROM TABLE(test_pipelined());
--Data inserted into nested table after cursor opening
plsql_table.EXTEND();
plsql_table(2) := 10;
END test_proc;

end;
/

Executing the testcase :
----------------------
var v_5 refcursor;
exec test_package.test_proc(:v_5);
print v_5;

Output:
--------
SQL> print v_5;

COLUMN_VALUE
------------
           5
          10

References

BUG:6773284 – SCOPE OF PIPELINED FUNCTION WHILE USING REF CURSOR
BUG:6860601 – PIPELINED FUNCTIONS DATA CONSISTENCY


 

Related


Products


  • Oracle Database Products > Oracle Database > Oracle Database > Oracle Server – Enterprise Edition
  • Oracle Database Products > Oracle Database > Application Development > PL/SQL

 

Author: admin