Polymorphic Table Function (PTF) Exception Demo

View All Scripts Login to Run Script

Script Name Polymorphic Table Function (PTF) Exception Demo
Description PTF's can throw exceptions. This script demonstrates when they will (and when they wont) pass to a calling program. An error is raised using this line in each section of the PTF package "RAISE_APPLICATION_ERROR(-20000,'Badness')". Then two queries are executed on the PTF - one would return rows and the other would not.
Area PL/SQL General / PL/SQL Procedures, Functions, Packages
Contributor Darryl Hurley
Created Friday December 28, 2018

Statement 1

CREATE TABLE table1 
( col1 NUMBER, 
  col2 NUMBER )

Table created.

Statement 2

INSERT INTO table1 
VALUES(19,99)

1 row(s) inserted.

Statement 3

CREATE OR REPLACE PACKAGE poly_pkg AS 
 
  FUNCTION describe (p_tbl IN OUT DBMS_TF.TABLE_T ) 
    RETURN DBMS_TF.DESCRIBE_T; 
  PROCEDURE open; 
  PROCEDURE fetch_rows; 
  PROCEDURE close; 
 
END;

Package created.

Statement 4

CREATE OR REPLACE PACKAGE BODY poly_pkg AS 
 
  FUNCTION describe (p_tbl IN OUT DBMS_TF.TABLE_T ) 
    RETURN DBMS_TF.DESCRIBE_T AS 
  BEGIN 
    RAISE_APPLICATION_ERROR(-20000,'Badness'); 
  END; 
 
  PROCEDURE open IS 
  BEGIN 
    NULL; 
  END; 
 
  PROCEDURE fetch_rows IS 
  BEGIN 
    NULL; 
  END; 
 
  PROCEDURE close IS 
  BEGIN 
    NULL; 
  END; 
 
END;

Package Body created.

Statement 5

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

Function created.

Statement 6

Exceptions in DESCRIBE will cause query failure whether rows would be returned or not.

describe when rows would be returned

SELECT *  
  FROM poly_func(table1)

ORA-62565: The Describe method failed with error(s). 
ORA-20000: Badness
ORA-06512: at "SQL_ZGXWKTIWODLUERJTBBCWBYZCC.POLY_PKG", line 6
ORA-06512: at line 22

More Details: https://docs.oracle.com/error-help/db/ora-62565

Statement 7

Exceptions in DESCRIBE will cause query failure whether rows would be returned or not.

describe when rows would not be returned

SELECT *  
  FROM poly_func(table1)  
 WHERE col1 = 88

ORA-62565: The Describe method failed with error(s). 
ORA-20000: Badness
ORA-06512: at "SQL_ZGXWKTIWODLUERJTBBCWBYZCC.POLY_PKG", line 6
ORA-06512: at line 22

More Details: https://docs.oracle.com/error-help/db/ora-62565

Statement 8

CREATE OR REPLACE PACKAGE BODY poly_pkg AS 
 
  FUNCTION describe (p_tbl IN OUT DBMS_TF.TABLE_T ) 
    RETURN DBMS_TF.DESCRIBE_T AS 
  BEGIN 
    RETURN NULL; 
  END; 
 
  PROCEDURE open IS 
  BEGIN 
    RAISE_APPLICATION_ERROR(-20000,'Badness'); 
  END; 
 
  PROCEDURE fetch_rows IS 
  BEGIN 
    NULL; 
  END; 
 
  PROCEDURE close IS 
  BEGIN 
    NULL; 
  END; 
 
END;

Package Body created.

Statement 9
Exceptions in OPEN will cause query failure when rows would be returned.
open when rows would be returned
```
SELECT *  
  FROM poly_func(table1)
```
```
ORA-20000: Badness
```
More Details: https://docs.oracle.com/error-help/db/ora-20000
Statement 10
Exceptions in OPEN will NOT cause query failure when rows would NOT be returned.
open when rows would not be returned
```
SELECT *  
  FROM poly_func(table1)  
 WHERE col1 = 88
```
```
no data found
```

Statement 11

CREATE OR REPLACE PACKAGE BODY poly_pkg AS 
 
  FUNCTION describe (p_tbl IN OUT DBMS_TF.TABLE_T ) 
    RETURN DBMS_TF.DESCRIBE_T AS 
  BEGIN 
    RETURN NULL; 
  END; 
 
  PROCEDURE open IS 
  BEGIN 
    NULL; 
  END; 
 
  PROCEDURE fetch_rows IS 
  BEGIN 
    RAISE_APPLICATION_ERROR(-20000,'Badness'); 
    NULL; 
  END; 
 
  PROCEDURE close IS 
  BEGIN 
    NULL; 
  END; 
 
END;

Package Body created.

Statement 12
Exceptions in FETCH will cause query failure when rows would be returned.
fetch when rows would be returned
```
SELECT *  
  FROM poly_func(table1)
```
```
ORA-20000: Badness
```
More Details: https://docs.oracle.com/error-help/db/ora-20000
Statement 13
Exceptions in FETCH will NOT cause query failure when rows would NOT be returned.
fetch when rows would not be returned
```
SELECT *  
  FROM poly_func(table1)  
 WHERE col1 = 88
```
```
no data found
```

Statement 14

CREATE OR REPLACE PACKAGE BODY poly_pkg AS 
 
  FUNCTION describe (p_tbl IN OUT DBMS_TF.TABLE_T ) 
    RETURN DBMS_TF.DESCRIBE_T AS 
  BEGIN 
    RETURN NULL; 
  END; 
 
  PROCEDURE open IS 
  BEGIN 
    NULL; 
  END; 
 
  PROCEDURE fetch_rows IS 
  BEGIN 
    NULL; 
  END; 
 
  PROCEDURE close IS 
  BEGIN 
    RAISE_APPLICATION_ERROR(-20000,'Badness'); 
  END; 
 
END;

Package Body created.

Statement 15
Exceptions in CLOSE will cause query failure when rows would be returned.
close when rows would be returned
```
SELECT *  
  FROM poly_func(table1)
```
```
ORA-20000: Badness
```
More Details: https://docs.oracle.com/error-help/db/ora-20000
Statement 16
Exceptions in OPEN will NOT cause query failure when rows would NOT be returned.
close when rows would not be returned
```
SELECT *  
  FROM poly_func(table1)  
 WHERE col1 = 88
```
```
no data found
```

Additional Information

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