Pipelined table functions
Some posts take on a life of their own. This is one of those. When I presented the concept at the Utah Oracle User’s Group (UTOUG) Training Days 3/12-3/13/2009 it became clear the community could benefit from more detail about table and pipelined table functions. Also, since the subject is beyond a straightforward implementation of technique, it now uses folding to separate subtopics (see here for more detail on how to read the blog page).
This approach is useful when you’re trying to implement third party program. Third party programs often overused index-by or Associative Array PL/SQL data types as formal parameters and return type. You can generally wrap these modules in pipelined table functions. Then, you can implement them in any SQL programming context. This appproach lets you access them through Java, PHP, or other OCI8 programming languages.
The primary purpose of a pipelined table function lets you retrieve PL/SQL record collection structures in a SQL context. PL/SQL record structures are (a) any collection that contains a record structure, and (b) system reference cursors. While system reference cursors have a SQL*Plus structure for letting you dump their contents at the command line, there is no convenient single step to convert them to a SQL context. You must first read a system reference cursors in a for loop to convert them to a SQL context. You do that second step with a pipelined table function.
There’s a nightmare of strong opinion about the term PL/SQL Table because their more aptly called index-by or Associative Array collections. Unfortunately, it was Oracle’s documentation that gave the community the term. Its unfortunate that it stuck because it can be misleading. A PL/SQL Table isn’t a table in any sense of the word because it doesn’t have a SQL context. A PL/SQL table, index-by table, or Associative Array is a two-dimensional memory structure in the SGA, and it is accessible in a PL/SQL scope or through external programming languages that use the OCI8 libraries.
Pipelined table functions are slower than other approaches, but they are easy to implement. That’s why a number of folks use them. The alternative to a pipelined function is typically a complex query. Complex queries may include advanced joins, selective aggregation, or DECODE/CASE logic statements. Many programmers choose to perform data conditioning inside PL/SQL blocks because they’re more comfortable with the procedure constructs. The same programmers also plan to return tables to only other PL/SQL programming units. After they’ve written the logic and they discover that the program also needs to run in a SQL context, they write pipelined table function to wrap the table function or return variable logic.
There are two general situations where code reuse requires a pipelined table function. They are a function that returns a PL/SQL only context variable and a procedure that returns a pass-by-reference formal parameter that is a PL/SQL only context variable (more on that in another PL/SQL formal parameter modes blog post). The second and far less frequent scenario is where you require a pipelined table function as part of the process to convert a system reference cursor.
The blog page describes and provides some small examples of these types of problems. It is organized as shown. You can also find examples in Oracle Database 11g PL/SQL Programming
Package Specification Definition ↓
You must define PL/SQL data types inside a package specification to make them reusable in your schema. They become package level data types. Package level data types can be used by any local schema program unit, like stored functions, procedures, packages, or objects. You can implement these without special grants and privileges. You must grant execute privileges to other scheme that need to access these type definitions, and you should generally adopt a definer’s right implementation to simpify access to these structures.
A package specification publishes data types, and function and procedure specifications. You implement functions and procedures in package bodies. As such, the function and procedure definitions are forward references or stubs to future implementations. However, you can implement a package specification that exclusively defines PL/SQL data types. This example does that.
The following package specification creates a PL/SQL record structure, three PL/SQL record structure collections, a weakly typed reference cursor, and a strongly typed reference cursor. It doesn’t create any function or procedure stubs, which typically means there will only be a package specification.
This package specification supports the balance of examples in this blog page.
CREATE OR REPLACE PACKAGE utility_type IS -- Define a PL/SQL record structure TYPE common_lookup_record IS RECORD ( common_lookup_id NUMBER , common_lookup_type VARCHAR2(30) , common_lookup_meaning VARCHAR2(255)); -- Define a PL/SQL record structure based on an Object-relational varray. TYPE common_lookup_varray IS VARRAY(10) OF common_lookup_record; -- Define a PL/SQL record structure based on an Object-relational nested table. TYPE common_lookup_table IS TABLE OF common_lookup_record; -- Define a PL/SQL record structure based on a PL/SQL associative array collection. TYPE common_lookup_plsql_table IS TABLE OF common_lookup_record INDEX BY BINARY_INTEGER; -- Define a weakly typed system reference cursor, really not needed because of SYS_REFCURSOR. TYPE weakly_typed_cursor IS REF CURSOR; -- Define a strongly typed system reference cursor linked to a package specification record type. TYPE strongly_typed_cursor IS REF CURSOR RETURN utility_type.common_lookup_record; END utility_type; / |
You can also define a strongly typed system reference cursor by linking it to a catalog table or view. The syntax for that is shown below by linking the type to the common_lookup_record table.
-- Define a strongly typed system reference cursor linked to a catalog table or view. TYPE strongly_typed_cursor IS REF CURSOR RETURN common_lookup_record%ROWTYPE; |
You’ll use this package specification in all examples for this blog page. If it conflicts with something you have, changing it will require that you do so throughout the other examples.
PL/SQL Table Functions & Procedures ↓
A PL/SQL table function returns a collection of a record structure. They have an exclusively PL/SQL program scope, which means you’ll trigger an error if you try to use them inside a SQL context. The error also occurs when you nest a call to a PL/SQL table function inside another function call that returns a SQL data type. It is important to let you know that while the Varray and Nested Table collections may be SQL data types, they’re PL/SQL data types when they contain a record structure. Index-by or Associative Arrays are always PL/SQL data types whether they contain a scalar or record structure variable.
This section demonstrates the syntax for all three types of table functions and pass-by-reference procedures. The Varray is probably the most effective in this context simply because it has a defined size in the SGA, and supports direct assignment. Varrays impose a fixed limit on the number of their elements, which is why they have a fixed size in memory.
A Varray Example ↓
A Varray is the most like an array in a normal programming language. You define a maximum size when you define a Varray and can’t exceed that maximum value during runtime. They are always indexed by sequential integers and there can never be any gaps in the sequence. Varrays can be either SQL or PL/SQL variables. They are SQL data types when they contain a scalar SQL data type as an element, and they are PL/SQL data types when they contain a record structure or object type element. The object type is a user-defined type (UDT).
Sample Varray Table Function
The example table function returns a collection defined by the utility_type package specification. You can define it in the same schema as the package specification, or in any schema that has EXECUTE privilege on the package.
CREATE OR REPLACE FUNCTION get_common_lookup_varray ( table_name VARCHAR2 , column_name VARCHAR2 ) RETURN utility_type.common_lookup_varray IS -- Define a dynamic cursor that takes two formal parameters. CURSOR c (table_name_in VARCHAR2, table_column_name_in VARCHAR2) IS SELECT common_lookup_id , common_lookup_type , common_lookup_meaning FROM common_lookup WHERE common_lookup_table = UPPER(table_name_in) AND common_lookup_column = UPPER(table_column_name_in); -- Declare a counter variable. counter INTEGER := 1; -- Declare a package collection data type as the table function return type. list UTILITY_TYPE.COMMON_LOOKUP_VARRAY := utility_type.common_lookup_varray(); BEGIN -- Assign the cursor return values to a record collection. FOR i IN c(table_name, column_name) LOOP list.EXTEND; list(counter) := i; counter := counter + 1; END LOOP; -- Return the record collection. RETURN list; END get_common_lookup_varray; / |
The function takes a table and column name as formal parameters. It uses the table and column name as actual parameters to call a dynamic cursor. The cursor finds all possible IDs, types, and meanings for the target table and column. The list collection type is a user-defined type (UDT), and it requires a constructor to initialize it before assigning it any values. You should note that a Varray constructor sets the variable’s size in a PL/SQL scope and makes it directly assignable. The cursor for loop assigns each returned row as an element in the list after it extends space for the object collection. The elements in the Varray are indexed by sequential integers.
You can call this table function in a PL/SQL scope like this:
DECLARE -- Define a collection base on a package specification collection type. list UTILITY_TYPE.COMMON_LOOKUP_VARRAY; BEGIN -- Assign the result of the function to a locally scoped variable. list := get_common_lookup_varray('ITEM','ITEM_TYPE'); -- Read through the collection and reference the elements of the record structure. FOR i IN 1..list.COUNT LOOP DBMS_OUTPUT.put('['||list(i).common_lookup_id||']'); DBMS_OUTPUT.put('['||list(i).common_lookup_type||']'); DBMS_OUTPUT.put_line('['||list(i).common_lookup_meaning||']'); END LOOP; END; / |
The anonymous block test program defines a local variable based on the package specification collection data type. Once inside the execution block, the result value of the get_common_lookup_varray table function call is assigned to a local collection variable. There is no local initialization of the object type required because you’re assigning an instance of an object type, which means you’re assigning a reference from one program scope to another. The reference points to a already constructed and sized object instance returned by the get_common_lookup_varray function. Varray structures have a physical size, which lets them be directly assigned from one program unit scope to another. The same cannot be said for the Nested Table or Associative Array record collection types.
A Varray can raise the following type of exception when the return set is larger than the maximum defined for the type in the package specification. This indicates that there is risk when you use a Varray data type in this type of programming context.
DECLARE * ERROR at line 1: ORA-06532: Subscript outside OF LIMIT ORA-06512: at "PLSQL.GET_COMMON_LOOKUP_VARRAY", line 25 ORA-06512: at line 6 |
You need access to the package specification or some very accurate document that describes the PL/SQL record collection structure because you need to know the nested field names of the record structure. When you convert the PL/SQL structure to a SQL structure with a pipelined function, you have access to the catalog definition implicitly.
You should also note that describing a package specification doesn’t yield internally defined data types. You must query the TEXT column of the ALL_SOURCE view to find this information. There’s another way but it’s more complex.
Varray Pass-by-Reference Procedure
The example table procedure passes a collection as an OUT mode parameter. It uses the same definition as the function, and it is found in the utility_type package specification. You can define it in any program found in the same schema as the package specification, or in any schema that has EXECUTE privilege on that package.
CREATE OR REPLACE PROCEDURE set_common_lookup_varray ( table_name IN VARCHAR2 , column_name IN VARCHAR2 , list OUT UTILITY_TYPE.COMMON_LOOKUP_VARRAY ) IS -- Define a dynamic cursor that takes two formal parameters. CURSOR c (table_name_in VARCHAR2, table_column_name_in VARCHAR2) IS SELECT common_lookup_id , common_lookup_type , common_lookup_meaning FROM common_lookup WHERE common_lookup_table = UPPER(table_name_in) AND common_lookup_column = UPPER(table_column_name_in); -- Declare a counter variable. counter INTEGER := 1; BEGIN -- Initialize collection data type as the table function return type. list := utility_type.common_lookup_varray(); -- Assign the cursor return values to a record collection. FOR i IN c(table_name, column_name) LOOP list.EXTEND; list(counter) := i; counter := counter + 1; END LOOP; END set_common_lookup_varray; / |
The procedure takes a table, column name, and a record collection variable as formal parameters. It uses the table and column name actual parameters to call a dynamic cursor. The cursor finds all possible IDs, types, and meanings for that table and column. Those values are then assigned to the list record collection variable, which is defined in the calling scope. The cursor for loop assigns each returned row as an element in the list. The elements are indexed by sequential integers.
You can call this table procedure in a PL/SQL scope like this:
DECLARE -- Define a collection base on a package specification collection type. list UTILITY_TYPE.COMMON_LOOKUP_VARRAY := utility_type.common_lookup_varray(); BEGIN -- Assign the result of the procedure to a locally scoped variable. set_common_lookup_varray('ITEM','ITEM_TYPE',list); -- Read through the collection and reference the elements of the record structure. FOR i IN 1..list.COUNT LOOP DBMS_OUTPUT.put('['||list(i).common_lookup_id||']'); DBMS_OUTPUT.put('['||list(i).common_lookup_type||']'); DBMS_OUTPUT.put_line('['||list(i).common_lookup_meaning||']'); END LOOP; END; / |
You define a local variable based on the package specification’s collection data type. Once inside the execution block, you call the set_common_lookup_varray table procedure by passing a reference to a like collection variable. The procedure returns an initialized and populated collection variable. You can find a pipelined function that processes this procedure later in this blog page.
You create a range for loop that navigates elements in the collection to print them. In the loop, you refer to the elements by their index value and the columns by their record structure names. This means you’ll need privileges to access the package specification, or you’ll need access to a document describing it. You should note that describing a package specification doesn’t yield internally defined data types.
A Nested Table Example ↓
A Nested Table is the most like a list in a normal programming language. You can’t define a maximum size for one because you can extend their size indefinitely or until you exceed your SGA memory constraints. Nested tables are indexed by integers and index values must initially be sequential. Gaps in index values may occur during use but that only makes navigating them more complex. Nested tables can be either SQL or PL/SQL variables. They are SQL data types when they contain a scalar SQL data type as an element, and they are PL/SQL data types when they contain a record collection structure or object type element. Object types are user-defined types (UDTs).
Sample Nested Table Function
The example table function returns a collection type that is defined by the utility_type package specification. You can define it in program units within the same schema as the package specification, or in program units where the schema has the EXECUTE privilege for the package.
CREATE OR REPLACE FUNCTION get_common_lookup_table ( table_name VARCHAR2 , column_name VARCHAR2 ) RETURN utility_type.common_lookup_table IS -- Define a dynamic cursor that takes two formal parameters. CURSOR c (table_name_in VARCHAR2, table_column_name_in VARCHAR2) IS SELECT common_lookup_id , common_lookup_type , common_lookup_meaning FROM common_lookup WHERE common_lookup_table = UPPER(table_name_in) AND common_lookup_column = UPPER(table_column_name_in); -- Declare a counter variable. counter INTEGER := 1; -- Declare a package collection data type as the table function return type. list UTILITY_TYPE.COMMON_LOOKUP_TABLE := utility_type.common_lookup_table(); BEGIN -- Assign the cursor return values to a record collection. FOR i IN c(table_name, column_name) LOOP list.EXTEND; list(counter) := i; counter := counter + 1; END LOOP; -- Return the record collection. RETURN list; END get_common_lookup_table; / |
The function takes a table and column name as its formal parameters. It uses the table and column name as actual parameters to call a dynamic cursor. The cursor finds all possible IDs, types, and meanings for that table and column. The list record collection type is a user-defined type (UDT), and it requires a constructor to initialize it before assigning any values to it. The cursor for loop assigns each returned row as an element in the list after it extends space in the object collection. The elements are indexed by sequential integers.
You would suspect that calling this table function in a PL/SQL scope like the following would work but it doesn’t. It doesn’t work because you can’t assign the contents of one nested table to another when they are in different program scopes. Actually, the problem is that PL/SQL doesn’t let you assign references from collection variable to another unless they are both Varray data types.
DECLARE -- Define a collection base on a package specification collection type. list UTILITY_TYPE.COMMON_LOOKUP_TABLE; BEGIN -- Assign the result of the function to a locally scoped variable. list := get_common_lookup_table('ITEM','ITEM_TYPE'); -- Read through the collection and reference the elements of the record structure. FOR i IN 1..list.COUNT LOOP DBMS_OUTPUT.put('['||list(i).common_lookup_id||']'); DBMS_OUTPUT.put('['||list(i).common_lookup_type||']'); DBMS_OUTPUT.put_line('['||list(i).common_lookup_meaning||']'); END LOOP; END; / |
The assignment attempt generates the error below. You should note that an ORA-06533 error code is one greater than the normal ORA-06532 error code for exceeding an index value limit (that is a subscript value).
DECLARE * ERROR at line 1: ORA-06533: Subscript beyond COUNT ORA-06512: at "PLSQL.GET_COMMON_LOOKUP_TABLE", line 25 ORA-06512: at line 6 |
The assignment of the function result to the list record collection variable appears illegal when using a Nested Table, but I couldn’t find anything on it in the documentation. If you know where I missed it, please let me know. It throws an ORA-06533 exception because it you can’t assign the record collection result from the function to another like variable type when it is defined in an external scope. The Varray differs because Oracle know its maximum physical size before attempting to make the assignment. The Associative Array also differs but probably due to the fact that it isn’t an object type.
The correct way (or the way it works is) to perform this operation through a pass-by-reference variable. The next section shows how to use a pass-by-reference variable in a procedure. For your reference, you can also implement pass-by-reference variables in functions.
Nested Table Pass-by-Reference Procedure
The example table procedure passes a collection as an OUT mode parameter. It uses the same definition as the function, as found in the utility_type package specification. You can define a variable of this type in any program unit within the same schema as the package specification. You can also use it in other program units that are in other scheme, provided those scheme have the EXECUTE privilege on that package.
CREATE OR REPLACE PROCEDURE set_common_lookup_table ( table_name IN VARCHAR2 , column_name IN VARCHAR2 , list OUT UTILITY_TYPE.COMMON_LOOKUP_TABLE ) IS -- Define a dynamic cursor that takes two formal parameters. CURSOR c (table_name_in VARCHAR2, table_column_name_in VARCHAR2) IS SELECT common_lookup_id , common_lookup_type , common_lookup_meaning FROM common_lookup WHERE common_lookup_table = UPPER(table_name_in) AND common_lookup_column = UPPER(table_column_name_in); -- Declare a counter variable. counter INTEGER := 1; BEGIN -- Initialize collection data type as the table function return type. list := utility_type.common_lookup_table(); -- Assign the cursor return values to a record collection. FOR i IN c(table_name, column_name) LOOP list.EXTEND; list(counter) := i; counter := counter + 1; END LOOP; END set_common_lookup_table; / |
The procedure takes a table, column name, and a record collection as formal parameters. It uses the table and column name as actual parameters to call a dynamic cursor. The cursor finds all possible types and meanings for that table and column. The procedure assigns those values to the list parameter variable, which is defined in the calling scope. The cursor for loop assigns each returned row as an element in the record collection. The elements are indexed by sequential integers.
You can call this table procedure in a PL/SQL scope like this:
DECLARE -- Define a collection base on a package specification collection type. list UTILITY_TYPE.COMMON_LOOKUP_TABLE := utility_type.common_lookup_table(); BEGIN -- Assign the result of the procedure to a locally scoped variable. set_common_lookup_table('ITEM','ITEM_TYPE',list); -- Read through the collection and reference the elements of the record structure. FOR i IN 1..list.COUNT LOOP DBMS_OUTPUT.put('['||list(i).common_lookup_id||']'); DBMS_OUTPUT.put('['||list(i).common_lookup_type||']'); DBMS_OUTPUT.put_line('['||list(i).common_lookup_meaning||']'); END LOOP; END; / |
The test program defines a local variable based on the package specification collection data type. Inside the execution block, it calls the set_common_lookup_table table procedure, and it passes a reference to an equivalent record collection variable. The procedure returns an initialized and populated collection variable to the externally scoped reference variable. This is key because a direct assignment between two programming scopes doesn’t work, as you saw in the previous table function example.
You should note that the sample program refers to the columns based on their record structure names. This means you’ll need to have access to the package specification, or some accurate document that describes it. You should note that describing a package specification doesn’t yield internally defined data types.
An Associative Array Example ↓
An Associative Array is unique to PL/SQL. In previous versions of the database they were called PL/SQL tables or index-by tables. They don’t have an object constructor like the Varray or Nested Table types. You can’t define a maximum size. You can extend size indefinitely or until you exceed your memory constraints. You can have gaps in the indexing sequence with these data types because they perform more like hashes than lists. You can also index them by number or string values, and the index values do not need to be sequential. Associative Arrays are exclusively PL/SQL data types. They have no SQL context whether they contain a scalar, record structure, or object type as their base element.
Associative Array Table Function
The example table function returns a collection defined in the utility_type package specification. You can define a like variable in the same schema as the package specification, or in any schema that has the EXECUTE privilege for the package.
CREATE OR REPLACE FUNCTION get_common_lookup_plsql_table ( table_name VARCHAR2 , column_name VARCHAR2 ) RETURN utility_type.common_lookup_plsql_table IS -- Define a dynamic cursor that takes two formal parameters. CURSOR c (table_name_in VARCHAR2, table_column_name_in VARCHAR2) IS SELECT common_lookup_id , common_lookup_type , common_lookup_meaning FROM common_lookup WHERE common_lookup_table = UPPER(table_name_in) AND common_lookup_column = UPPER(table_column_name_in); -- Declare a counter variable. counter INTEGER := 1; -- Define a package collection data type as the table function return type. list UTILITY_TYPE.COMMON_LOOKUP_PLSQL_TABLE; BEGIN -- Assign the cursor return values to a record collection. FOR i IN c(table_name, column_name) LOOP list(counter) := i; counter := counter + 1; END LOOP; -- Return the record collection. RETURN list; END get_common_lookup_plsql_table; / |
The function takes a table and column name as formal parameters. It uses the table and column name as actual parameters to call a dynamic cursor. The cursor finds all possible types and meanings for that table and column. The cursor for loop assigns each returned row as an element in the list. The elements are indexed by sequential integers.
You can call this table function in a PL/SQL scope like this:
DECLARE -- Define a collection base on a package specification collection type. list UTILITY_TYPE.COMMON_LOOKUP_PLSQL_TABLE; BEGIN -- Assign the result of the function to a locally scoped variable. list := get_common_lookup_plsql_table('ITEM','ITEM_TYPE'); -- Read through the collection and reference the elements of the record structure. FOR i IN 1..list.COUNT LOOP DBMS_OUTPUT.put('['||list(i).common_lookup_id||']'); DBMS_OUTPUT.put('['||list(i).common_lookup_type||']'); DBMS_OUTPUT.put_line('['||list(i).common_lookup_meaning||']'); END LOOP; END; / |
As seen in other examples, you define a local variable based on the package specification collection data type. Inside the execution block, you assign the result value from the get_common_lookup_plsql_table table function to a local collection variable. This works when both types are native PL/SQL Associative Array record collection types. It readily assigns a reference from the function to the externally scoped program unit, unlike the case of the Nested Table seen earlier in the blog page.
It returns the following data:
[1013][DVD_FULL_SCREEN][DVD: Full Screen] [1014][DVD_WIDE_SCREEN][DVD: Wide Screen] [1015][GAMECUBE][Nintendo GameCube] [1016][PLAYSTATION2][PlayStation2] [1019][VHS_DOUBLE_TAPE][VHS: Double Tape] [1018][VHS_SINGLE_TAPE][VHS: Single Tape] [1017][XBOX][XBOX] |
As discussed previously in the blog page, the range for loop navigates elements in the collection. You refer to the elements by their index value and the columns by their record collection structure names. This means you’ll need access to the package specification, or an accurate document that describes it. You should note that describing a package specification doesn’t yield internally defined data types.
Associative Array Table Pass-by-Reference Procedure
The example table procedure includes a formal parameter that is a collection and has an OUT mode parameter. An OUT mode parameter means that you need to define a reference for a variable in the calling scope. The function returns a value to that reference when it completes successfully.
This procedure uses the a record collection definition that is the same as the preceding function. It is found in the utility_type package specification. Likewise, you can define another program unit that uses the same data type within the same schema as the package specification, and in any schema that has the EXECUTE privilege for the package.
CREATE OR REPLACE FUNCTION get_common_lookup_plsql_table ( table_name VARCHAR2 , column_name VARCHAR2 ) RETURN utility_type.common_lookup_plsql_table IS -- Define a dynamic cursor that takes two formal parameters. CURSOR c (table_name_in VARCHAR2, table_column_name_in VARCHAR2) IS SELECT common_lookup_id , common_lookup_type , common_lookup_meaning FROM common_lookup WHERE common_lookup_table = UPPER(table_name_in) AND common_lookup_column = UPPER(table_column_name_in); -- Declare a counter variable. counter INTEGER := 1; -- Define a package collection data type as the table function return type. list UTILITY_TYPE.COMMON_LOOKUP_PLSQL_TABLE; BEGIN -- Assign the cursor return values to a record collection. FOR i IN c(table_name, column_name) LOOP list(counter) := i; counter := counter + 1; END LOOP; -- Return the record collection. RETURN list; END get_common_lookup_plsql_table; / |
The procedure takes a table, column name, and record collection structure as formal parameters. It uses the table and column name as actual parameters to call a dynamic cursor. The cursor finds all possible types and meanings for that table and column. The procedure assigns those values to the list parameter, which is defined in the calling scope.
You can call this table procedure in a PL/SQL scope like this:
DECLARE -- Define a collection base on a package specification collection type. list UTILITY_TYPE.COMMON_LOOKUP_PLSQL_TABLE; BEGIN -- Assign the result of the procedure to a locally scoped variable. set_common_lookup_plsql_table('ITEM','ITEM_TYPE',list); -- Read through the collection and reference the elements of the record structure. FOR i IN 1..list.COUNT LOOP DBMS_OUTPUT.put('['||list(i).common_lookup_id||']'); DBMS_OUTPUT.put('['||list(i).common_lookup_type||']'); DBMS_OUTPUT.put_line('['||list(i).common_lookup_meaning||']'); END LOOP; END; / |
You define a local variable based on the package specification’s record collection data type. Inside the execution block, you pass the reference of the record collection to the set_common_lookup_plsql_table table procedure. The procedure returns a populated record collection when it completes successfully.
PL/SQL Pipelined Table Functions ↓
A PL/SQL pipelined table function returns a collection of a record structure as a SQL result set. A SQL result set, is the filtered result set of a query, which is also known as an aggregate table result set. You must return a Varray or Nested Table. Once translated into a SQL data type and context, the type inherits the definition of columns from the data catalog, where they’re stored when you compiled the package specification.
The prior section demonstrates how to return a record collection set as a Varray, Nested Table, or Associative Array. These types of return values are PL/SQL variables and can only be used in a PL/SQL programming context. This section demonstrates how to translate the PL/SQL data types into a aggregate table, which is equivalent to the return set from a query.
This section demonstrates the syntax for translating all three types of table functions in the prior section into aggregate table result sets. These examples are dependent on the earlier examples because they produce the required table function results, or return table results through a pass-by-reference variable.
A Varray Example ↓
A Varray is the most like an array in a normal programming language. You define a maximum size when you define a Varray and can’t exceed that maximum number of elements during runtime. Varrays are always indexed by sequential numbers and there can’t be any gaps in the index sequence values. Varrays can be either SQL or PL/SQL variables. They are a SQL data type when they contain a scalar SQL data type as elements, and a PL/SQL data type when they contain record structure or object type elements. Object types are user-defined type (UDT).
Sample Varray Table Function
The example table function takes a table and column name and returns a pipelined record collection as a SQL data type. At least that’s what you’d expect.
CREATE OR REPLACE FUNCTION get_common_lookup_sql_varray ( table_name VARCHAR2 , column_name VARCHAR2 ) RETURN utility_type.common_lookup_varray PIPELINED IS counter NUMBER := 1; collection UTILITY_TYPE.COMMON_LOOKUP_VARRAY; list UTILITY_TYPE.COMMON_LOOKUP_VARRAY := utility_type.common_lookup_varray(); BEGIN collection := get_common_lookup_varray(table_name,column_name); FOR i IN 1..collection.COUNT LOOP list.EXTEND; list(i) := collection(i); PIPE ROW(list(i)); END LOOP; RETURN; END get_common_lookup_sql_varray; / |
You query the result set like this:
SELECT get_common_lookup_sql_varray('ITEM','ITEM_TYPE') FROM dual; |
You should note that this doesn’t work because a Varray record structure can’t convert because it’s an array structure. It is however, a valid SQL data type. The preceding query outputs a list of system named PL/SQL objects encapsulated by a collection object because that is how it is stored in the SGA.
SYS_PLSQL_78972_31_1( SYS_PLSQL_78972_9_1(1013, 'DVD_FULL_SCREEN', 'DVD: Full Screen') , SYS_PLSQL_78972_9_1(1014, 'DVD_WIDE_SCREEN', 'DVD: Wide Screen') , SYS_PLSQL_78972_9_1(1015, 'GAMECUBE', 'Nintendo GameCube') , SYS_PLSQL_78972_9_1(1016, 'PLAYSTATION2', 'PlayStation2') , SYS_PLSQL_78972_9_1(1019, 'VHS_DOUBLE_TAPE', 'VHS: Double Tape') , SYS_PLSQL_78972_9_1(1018, 'VHS_SINGLE_TAPE', 'VHS: Single Tape') , SYS_PLSQL_78972_9_1(1017, 'XBOX', 'XBOX')) |
You need to preface the array return with the TABLE function to convert it, like the following SQL command.
COLUMN common_lookup_id FORMAT 9999 COLUMN common_lookup_type FORMAT A20 COLUMN common_lookup_meaning FORMAT A24 SELECT * FROM TABLE(get_common_lookup_plsql_table('ITEM','ITEM_TYPE')); |
This now renders the following output by querying the data catalog names from the PL/SQL package level structure.
COMMON_LOOKUP_ID COMMON_LOOKUP_TYPE COMMON_LOOKUP_MEANING ---------------- -------------------- ------------------------ 1013 DVD_FULL_SCREEN DVD: FULL Screen 1014 DVD_WIDE_SCREEN DVD: Wide Screen 1015 GAMECUBE Nintendo GameCube 1016 PLAYSTATION2 PlayStation2 1019 VHS_DOUBLE_TAPE VHS: DOUBLE Tape 1018 VHS_SINGLE_TAPE VHS: Single Tape 1017 XBOX XBOX 7 ROWS selected. |
Varray Pass-by-Reference Procedure
You can repeat this experiment with a pass-by-reference variable and produce the same results. The replacement function that uses a pass-by-reference model follows below.
CREATE OR REPLACE FUNCTION get_common_lookup_sql_varray ( table_name VARCHAR2 , column_name VARCHAR2 ) RETURN utility_type.common_lookup_varray PIPELINED IS -- Define one target collection, and declare one collection for the pipelined function. collection UTILITY_TYPE.COMMON_LOOKUP_VARRAY; list UTILITY_TYPE.COMMON_LOOKUP_VARRAY := utility_type.common_lookup_varray(); BEGIN -- Call the pass-by-reference procedure. set_common_lookup_varray(table_name,column_name,collection); -- Read the contents through a range for loop into the pipe. FOR i IN 1..collection.COUNT LOOP list.EXTEND; list(i) := collection(i); PIPE ROW(list(i)); counter := counter + 1; END LOOP; RETURN; END get_common_lookup_sql_varray; / |
More or less, this should demonstrate that you can use a Varray record collection structure for pipelined functions. Only the Associative Array record collections doesn’t work as a return type for a pipelined function.
A Nested Table Example ↓
A Nested Table is the most like a list in a normal programming language. You can’t define a maximum size but you can extend size indefinitely or until you exceed your memory constraints. Nested Tables are indexed by integers and index values must initially be sequential. Gaps may occur during use by deletion of elements. When gaps occur, you move from a densely populated to sparsely populated index. This change makes navigating Nested Tables more complex and should be avoided. If you’re going to delete elements, always choose an Associative Array record collection structure.
Nested tables can be either SQL or PL/SQL variables. They are a SQL data type when they contain a scalar SQL data type as an element, and a PL/SQL data type when they contain a record structure or object type. The object type is a user-defined type (UDT).
Nested Table Pass-by-Reference Procedure
As you saw in the earlier treatment of table functions, you can’t directly assign a Nested Table record collection structure to another variable. As a result, only a pass-by-reference semantic works for returning a Nested Table type.
The following function wraps a pass-by-reference procedure and returns a pipelined aggregate table from a Nested Table data type.
CREATE OR REPLACE FUNCTION get_common_lookup_sql_table ( table_name VARCHAR2 , column_name VARCHAR2 ) RETURN utility_type.common_lookup_table PIPELINED IS counter NUMBER := 1; collection UTILITY_TYPE.COMMON_LOOKUP_TABLE; list UTILITY_TYPE.COMMON_LOOKUP_TABLE := utility_type.common_lookup_table(); BEGIN set_common_lookup_table(table_name,column_name,collection); FOR i IN 1..collection.COUNT LOOP list.EXTEND; list(i) := collection(i); PIPE ROW(list(i)); counter := counter + 1; END LOOP; RETURN; END get_common_lookup_sql_table; / |
You can then query the function like this:
SELECT get_common_lookup_sql_table('ITEM','ITEM_TYPE') FROM dual; |
This yields the same PL/SQL memory structure that is returned by the Varray record collection structure.
You should note that this doesn’t work because a Nested Table record structure can’t naturally convert because it’s an array structure. The query outputs a list of system named PL/SQL objects encapsulated by a collection object because that appears to be how it is stored in the SGA.
SYS_PLSQL_78972_31_1( SYS_PLSQL_78972_9_1(1013, 'DVD_FULL_SCREEN', 'DVD: Full Screen') , SYS_PLSQL_78972_9_1(1014, 'DVD_WIDE_SCREEN', 'DVD: Wide Screen') , SYS_PLSQL_78972_9_1(1015, 'GAMECUBE', 'Nintendo GameCube') , SYS_PLSQL_78972_9_1(1016, 'PLAYSTATION2', 'PlayStation2') , SYS_PLSQL_78972_9_1(1019, 'VHS_DOUBLE_TAPE', 'VHS: Double Tape') , SYS_PLSQL_78972_9_1(1018, 'VHS_SINGLE_TAPE', 'VHS: Single Tape') , SYS_PLSQL_78972_9_1(1017, 'XBOX', 'XBOX')) |
You need to preface the array return with the TABLE function to convert it, like the following command.
COLUMN common_lookup_id FORMAT 9999 COLUMN common_lookup_type FORMAT A20 COLUMN common_lookup_meaning FORMAT A24 SELECT * FROM TABLE(get_common_lookup_plsql_table('ITEM','ITEM_TYPE')); |
This now renders the following output:
COMMON_LOOKUP_ID COMMON_LOOKUP_TYPE COMMON_LOOKUP_MEANING ---------------- -------------------- ------------------------ 1013 DVD_FULL_SCREEN DVD: FULL Screen 1014 DVD_WIDE_SCREEN DVD: Wide Screen 1015 GAMECUBE Nintendo GameCube 1016 PLAYSTATION2 PlayStation2 1019 VHS_DOUBLE_TAPE VHS: DOUBLE Tape 1018 VHS_SINGLE_TAPE VHS: Single Tape 1017 XBOX XBOX 7 ROWS selected. |
As you can see, the Nested Table like the Varray record collection datatypes works with pipelined table functions.
Another Simpler Nested Table Example ↓
This example shows how a Nested Table can work in this context, which is one where all components are in the same program unit.
This sample is interesting because it demonstrates a pipelined table function in a rather straightforward, but simple way. Naturally, there are better solutions then this if everything is in the same program unit.
CREATE OR REPLACE FUNCTION get_common_lookup ( table_name VARCHAR2 , column_name VARCHAR2 ) RETURN utility_type.common_lookup_table PIPELINED IS CURSOR c (table_name_in VARCHAR2, table_column_name_in VARCHAR2) IS SELECT common_lookup_id , common_lookup_type , common_lookup_meaning FROM common_lookup WHERE common_lookup_table = UPPER(table_name_in) AND common_lookup_column = UPPER(table_column_name_in); counter NUMBER := 1; list UTILITY_TYPE.COMMON_LOOKUP_TABLE := utility_type.common_lookup_table(); BEGIN FOR i IN c(table_name, column_name) LOOP list.EXTEND; list(counter) := i; PIPE ROW(list(counter)); counter := counter + 1; END LOOP; RETURN; END get_common_lookup; / |
This returns an aggregate table, which can be read by using the TABLE function. Reading this type of structure differs from how you use the TABLE function to read a scalar array of variables. You don’t use the COLUMN_VALUE argument, which is limited to reading a scalar array list. Provided you know the structure of the aggregate table, you can read the columns directly from the result of a pipelined table with the TABLE function.
COLUMN common_lookup_id FORMAT 999999 COLUMN common_lookup_type FORMAT A20 COLUMN common_lookup_meaning FORMAT A30 SELECT l.common_lookup_id , l.common_lookup_type , l.common_lookup_meaning FROM TABLE(get_common_lookup('ITEM','ITEM_TYPE')) l; |
The pipelined table function is the only way to read a table of record structures in a SQL statement. Pipelined table functions results are also limited to SQL scope only. That’s because their real benefit lies in translating PL/SQL collections into aggregate tables, which are SQL data types. Any attempt to read the aggregate table from a pipelined table function in another PL/SQL block raises a PLS-00653 error. While you can use a weakly typed cursor to mimic the same behavior through OCI8 in PHP, you can’t do so in SQL statements.
This function will work when called from a PHP program through OCI8, but won’t work in SQL:
CREATE OR REPLACE FUNCTION get_common_cursor ( table_name VARCHAR2 , column_name VARCHAR2 ) RETURN SYS_REFCURSOR IS lookups SYS_REFCURSOR; BEGIN OPEN lookups FOR SELECT common_lookup_id , common_lookup_type , common_lookup_meaning FROM common_lookup WHERE common_lookup_table = UPPER(table_name) AND common_lookup_column = UPPER(column_name); RETURN lookups; END get_common_cursor; / |
An attempt to read the get_common_cursor by the TABLE function yields an ORA-22905 error. The error says you can’t access rows from a non-nested table. You can read the cursor in SQL*Plus but it disallows using the result in another SQL statement. This function queries the cursor, which then dumps the contents as a set of subsequent results:
SELECT get_common_cursor('ITEM','ITEM_TYPE') FROM dual; |
If you’re using returning the cursor because you plan to use it through the OCI8, then you can wrap the function call in a pipelined table function for when you want to use it in SQL. The following converts the system reference cursor to an aggregate table structure:
CREATE OR REPLACE FUNCTION get_common_lookup ( c SYS_REFCURSOR ) RETURN utility_type.common_lookup_table PIPELINED IS counter NUMBER := 1; RECORD UTILITY_TYPE.COMMON_LOOKUP_RECORD; list UTILITY_TYPE.COMMON_LOOKUP_TABLE := utility_type.common_lookup_table(); BEGIN LOOP FETCH c INTO RECORD; EXIT WHEN c%NOTFOUND; list.EXTEND; list(counter) := RECORD; PIPE ROW(list(counter)); counter := counter + 1; END LOOP; RETURN; END get_common_lookup; / |
An Associative Array Example ↓
An associative array is unique to PL/SQL. In previous versions of the database they were called PL/SQL tables or index-by tables. They don’t have an object constructor like the Varray or Nested Table types. You can’t define a maximum size but you can extend size indefinitely or until you exceed your memory constraints. You can have gaps in the indexing sequence with these data types because the perform more like hashes than lists. This is why their indexes are called sparsely populated. You can also index them by string values, which may or may not be sequential. Associative arrays are only PL/SQL data tyeps. They have no SQL context whether they contain a scalar, record structure, or object type element. You can’t use an Associative Array as the return type of a pipelined function.
While you can’t return an Associative Array in pipelined mode, you can return it as a Nested Table record collection type. The only supported data types are the Varray and Nested Table data types.
Here’s a function that takes a transforms an Associative Array record collection from a PL/SQL to a SQL context. Like the Nested Table example, you can’t assign the contents of a PL/SQL collection to another collection because the language doesn’t support assigning references to Nested Table record collections.
Since imagining the failure isn’t easy for everyone, here’s a non-example. While it raises a PLS-00653 exception, the real error is a PLS-00382 exception. In this case the PLS-00382 is thrown because you can’t assign references in PL/SQL.
CREATE OR REPLACE FUNCTION get_common_lookup_plsql_table ( table_name VARCHAR2 , column_name VARCHAR2 ) RETURN utility_type.common_lookup_table PIPELINED IS counter NUMBER := 1; collection UTILITY_TYPE.COMMON_LOOKUP_PLSQL_TABLE; list UTILITY_TYPE.COMMON_LOOKUP_TABLE := utility_type.common_lookup_table(); BEGIN collection := get_common_lookup_plsql_table(table_name,column_name); FOR i IN 1..collection.COUNT LOOP list.EXTEND; list(counter) := collection(counter); PIPE ROW(list(counter)); counter := counter + 1; END LOOP; RETURN; END get_common_lookup_plsql_table; / |
The actual error stack would look like this:
Errors FOR FUNCTION GET_COMMON_LOOKUP_PLSQL_TABLE: LINE/COL ERROR -------- ----------------------------------------------------------------- 1/10 PLS-00653: aggregate/TABLE functions are NOT allowed IN PL/SQL scope 9/3 PL/SQL: Statement ignored 9/17 PLS-00382: expression IS OF wrong TYPE |
The correct way to write this requires a pass-by-reference construct. This function wraps the procedure presented in the Table Functions & Procedures section. You can also find a solution to wrapping a legacy PL/SQL tables of record structures in this more recent blog post.
CREATE OR REPLACE FUNCTION get_common_lookup_plsql_table ( table_name VARCHAR2 , column_name VARCHAR2 ) RETURN utility_type.common_lookup_table PIPELINED IS counter NUMBER := 1; collection UTILITY_TYPE.COMMON_LOOKUP_PLSQL_TABLE; list UTILITY_TYPE.COMMON_LOOKUP_TABLE := utility_type.common_lookup_table(); BEGIN set_common_lookup_plsql_table(table_name,column_name,collection); FOR i IN 1..collection.COUNT LOOP list.EXTEND; list(i) := collection(i); PIPE ROW(list(i)); counter := counter + 1; END LOOP; RETURN; END get_common_lookup_plsql_table; / |
You should note that a range for loop transfers the elements of the Associative Array to a Nested Table. You’re required to do this because only a Varray or Nested Table data structure are the only acceptable return data types for a pipelined function.
You need to preface the array return from the Nested Table data structure with the TABLE function to convert it, like the following command.
COLUMN common_lookup_id FORMAT 9999 COLUMN common_lookup_type FORMAT A20 COLUMN common_lookup_meaning FORMAT A24 SELECT * FROM TABLE(get_common_lookup_plsql_table('ITEM','ITEM_TYPE')); |
This now renders the following output:
COMMON_LOOKUP_ID COMMON_LOOKUP_TYPE COMMON_LOOKUP_MEANING ---------------- -------------------- ------------------------ 1013 DVD_FULL_SCREEN DVD: FULL Screen 1014 DVD_WIDE_SCREEN DVD: Wide Screen 1015 GAMECUBE Nintendo GameCube 1016 PLAYSTATION2 PlayStation2 1019 VHS_DOUBLE_TAPE VHS: DOUBLE Tape 1018 VHS_SINGLE_TAPE VHS: Single Tape 1017 XBOX XBOX 7 ROWS selected. |
As you can see, this works like the Nested Table example.
Subscribe via RSS
Valuable information.
Thanks
hunns
17 Sep 08 at 2:14 pm
Thanks for the extremly useful info. Currently trying to understand PL/SQL tables in order to understand how one get a HTML check box values to be used in PL/SQL.
usefulinfo
8 May 09 at 9:10 am
I found your article very informative. However I’m having trouble how to apply the concepts in an already working package.
The main function on the package outputs a ref cursor so it can be read as a result set in java. Now the problem comes as it depends on a type object defined as an sql type to return such ref cursor. This type object would have to be replaced executing it’s definition (I guess) if some column types of a cursor on the package change on the future.
What I want to do taking your articles is eliminate the dependency on the sql type object and create a plsql type object from a local record on the package. Now it’s impossible to return a ref cursor unless some concepts on your article are applyied.
By now, what you mean on the cursor:
I’m really confused what table would be “commn_lookup” on my application. I’m guessing the columns on this cursor are just columns on a collection which is “common_lookup”. But then I don’t know what would be common_lookup_table and common_lookup_column as there’s nothing like that defined on the structure of the collection. Would it be metadata? And how would I acces it?
And if I’m lost on what I have just catched yet. What would I solve the problem previusly mentioned? Thanks!
Gauss
5 Mar 10 at 5:55 pm
This shows you an alternative approach to work with system reference cursors.
How to wrap a system reference cursor and return a collection | MacLochlainns Weblog
7 Mar 10 at 9:17 pm
If I understood your question correctly, I’ve added a post that should help you here.
maclochlainn
7 Mar 10 at 9:20 pm
[…] wrap it in a Pipelined Table function […]
Converting a PL/SQL return type to SQL
16 Dec 10 at 1:41 am
I apologize but sometimes comments get approved on my iPhone and replying gets lost in the shuffle. The problem here is that Oracle doesn’t really support an Adapter pattern (example in PL/SQL). Unfortunately, these solutions are structurally coupled.
maclochlainn
16 Dec 10 at 1:58 am