Polymorphic Table Functions (PTFs) with variables

View All Scripts Login to Run Script

Script Name Polymorphic Table Functions (PTFs) with variables
Description PTF's accept variables for use during parse or execution. This included scalar datatypes and PTF specific variables called Pseudo-Operators.
Area PL/SQL General / PL/SQL Procedures, Functions, Packages
Contributor Darryl Hurley
Created Wednesday December 26, 2018

Statement 1

Create a PTF package that accepts a a varchar2 parameter

package with variable

CREATE OR REPLACE PACKAGE poly_pkg AS  
  FUNCTION describe ( p_tbl IN OUT DBMS_TF.TABLE_T,  
                      p_var NUMBER )  
                      RETURN DBMS_TF.DESCRIBE_T;  
END;

Package created.

Statement 2

Create a package body that accepts a varchar2 parameter

package body with variable

CREATE OR REPLACE PACKAGE BODY poly_pkg AS  
  
  FUNCTION describe ( p_tbl IN OUT DBMS_TF.TABLE_T,  
                      p_var NUMBER )  
                      RETURN DBMS_TF.DESCRIBE_T AS  
  BEGIN  
    -- simply display the value passed in  
    DBMS_OUTPUT.PUT_LINE('VAR = ' || p_var);  
    RETURN NULL;  
  END;  
  
END;

Package Body created.

Statement 3

Create a PTF that accepts a varchar2 parameter

function with parameter

CREATE OR REPLACE FUNCTION poly_func(p_tbl IN TABLE,  
                                     p_var NUMBER )  
  RETURN TABLE PIPELINED  
  ROW POLYMORPHIC USING poly_pkg;

Function created.

Statement 4

demo table

CREATE TABLE table1 
( col1 NUMBER, 
  col2 NUMBER )

Table created.

Statement 5

demo data

INSERT INTO table1 
VALUES(19,99)

1 row(s) inserted.

Statement 6
The variable passed in is not used at all it is just displayed with DBMS_OUTPUT
demo query
```
SELECT col1 
  FROM poly_func(table1,1)
```
```
COL1
19

VAR = 1
```

COL1
19

Statement 7

Create a package with the COLUMNS_T Pseudo-Operator

package with Pseudo-Operator

CREATE OR REPLACE PACKAGE poly_pkg AS  
  
  FUNCTION describe ( p_tbl IN OUT DBMS_TF.TABLE_T,  
                      p_cols DBMS_TF.COLUMNS_T )  
                      RETURN DBMS_TF.DESCRIBE_T;  
  
END;

Package created.

Statement 8

Create package body using the columns Pseudo-Operator

package body with Pseudo-Operator

CREATE OR REPLACE PACKAGE BODY poly_pkg AS  
  
  FUNCTION describe ( p_tbl IN OUT DBMS_TF.TABLE_T,  
                      p_cols DBMS_TF.COLUMNS_T )  
                      RETURN DBMS_TF.DESCRIBE_T IS  
  BEGIN  
    FOR counter IN 1..p_tbl.column.count LOOP  
      DBMS_OUTPUT.PUT_LINE('Object Column ' ||  
           p_tbl.column(counter).description.name);  
    END LOOP;  
    FOR counter IN 1..p_cols.count LOOP  
      DBMS_OUTPUT.PUT_LINE('Passed Column ' ||  
           p_cols(counter));  
    END LOOP;  
    RETURN NULL;  
  END;  
  
END;

Package Body created.

Statement 9

Create a function with the columns Pseudo-Operator

function with columns Pseudo-Operator

CREATE OR REPLACE FUNCTION poly_func(p_tbl IN TABLE, 
                                     p_cols DBMS_TF.COLUMNS_T ) 
  RETURN TABLE PIPELINED 
  ROW POLYMORPHIC USING poly_pkg;

Function created.

Statement 10
The column is not actually used in this demo it is just displayed with DBMS_OUTPUT.
demo query with columns Pseudo-Operator
```
SELECT *  
  FROM poly_func(table1,columns(col2))
```
```
COL1 COL2
19 99

Object Column "COL1"
Object Column "COL2"
Passed Column "COL2"
```

COL1	COL2
19	99

Additional Information

Database on OTN SQL and PL/SQL Discussion forums
Oracle Database
Download Oracle Database