PTF Error Handling

So what happens to un-handled exceptions in Polymorphic Table Function’s (PTF’s)? I’m glad you asked. This post covers that topic by introducing an exception with the following code:

RAISE_APPLICATION_ERROR(-20000,'Badness');

When added to the DESCRIBE function it produces this error at run time.

ORA-62565: The Describe method failed with error(s).
ORA-20000: Badness

OPEN, FETCH_ROWS and CLOSE shows errors as:

ORA-20000: Badness
ORA-06512: at "D.POLY_PKG", line 17

Note that it may show up multiple times for the CLOSE.

Also note that OPEN, FETCH_ROWS and CLOSE will not throw exceptions if no rows are processed.

That’s the simple part. But what if an error happens while processing rows?

I’ll add some logging to FETCH_ROWS like this:

PROCEDURE fetch_rows IS
  v_rowset DBMS_TF.ROW_SET_T;
  v_rowcount PLS_INTEGER;
BEGIN
  DBMS_OUTPUT.PUT_LINE('Start');
  DBMS_TF.GET_ROW_SET(v_rowset,v_rowcount);
  DBMS_OUTPUT.PUT_LINE('RC:' || v_rowcount);
  FOR counter IN 1..v_rowcount LOOP
    DBMS_OUTPUT.PUT_LINE('COUNTER:' || counter);
  END LOOP;
END;

And a successful query of three records produces this result.

COL1
----
  19
  19
  19
Start
RC:3
COUNTER:1
COUNTER:2
COUNTER:3

The logging shows that FETCH_ROWS started, queried a rowset with 3 rows and then looped through each row.

Now I’ll force a failure and trap the exception like this:

PROCEDURE fetch_rows IS
  v_rowset DBMS_TF.ROW_SET_T;
  v_rowcount PLS_INTEGER;
BEGIN
  DBMS_OUTPUT.PUT_LINE('Start');
  DBMS_TF.GET_ROW_SET(v_rowset,v_rowcount);
  DBMS_OUTPUT.PUT_LINE('RC:' || v_rowcount);
  FOR counter IN 1..v_rowcount LOOP
    DBMS_OUTPUT.PUT_LINE('COUNTER:' || counter);
    IF counter = 2 THEN
      RAISE_APPLICATION_ERROR(-20000,'Badness');
    END IF;
  END LOOP;
EXCEPTION
  WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE('EXCEPTION');
END;

What result will I get? Will the number of rows returned change?

COL1
----
  19
  19
  19
Start
RC:3
COUNTER:1
COUNTER:2
EXCEPTION

The logging shows that FETCH_ROWS started, queried a rowset with 3 rows and then looped through the first 2 rows. Then the exception was raised and trapped. But the only processing that stopped was within FETCH_ROWS. Thus three records were still returned.

Thanks for reading!