PTF’s Gonna Rick-Roll Ya!

The DUAL pseudo-table has been part of the Oracle database forever. It is typically used when a query is required but a real table does not apply. For example the following query that loads the current date into a variable.

SELECT SYSDATE new_date
  FROM DUAL;

Use of the DUAL table is rarely required nowadays because functions can be called directly something like this:

:new_date := SYSDATE

The DUAL table contains a single column named DUMMY and it resides in the SYS schema. It only returns a single row.

DESC dual
Name   Type
------ -----------
DUMMY  VARCHAR2(1)

DESC sys.dual
Name   Type
------ -----------
DUMMY  VARCHAR2(1) 

SELECT *
  FROM dual;
D
-
X

Use of the DUAL table in present day code is discouraged because it requires an unnecessary database query. But sometimes developers fall back on old habits and cant resist it. One way to break that habit is to return implausible but entertaining results. How about a Rick-Roll implemented as a Polymorphic Table Function (PTF)!?

CREATE OR REPLACE PACKAGE BODY poly_pkg AS

  FUNCTION describe (p_tbl IN OUT DBMS_TF.TABLE_T )
    RETURN DBMS_TF.DESCRIBE_T AS
    v_new_col DBMS_TF.COLUMN_METADATA_T;
    v_new_cols DBMS_TF.COLUMNS_NEW_T;
  BEGIN
    -- hide all columns selected
    FOR counter IN 1..p_tbl.column.count LOOP
      p_tbl.column(counter).pass_through := FALSE;
    END LOOP;
    -- add the rickroll column
    v_new_col := DBMS_TF.COLUMN_METADATA_T( 
             name => 'RICKROLL' );
    v_new_cols := DBMS_TF.COLUMNS_NEW_T( 1 => v_new_col);
    RETURN DBMS_TF.DESCRIBE_T ( new_columns => v_new_cols,
                                row_replication => true);
  END;

  PROCEDURE fetch_rows IS
    l_row_set DBMS_TF.row_set_t;
    l_new_col DBMS_TF.tab_varchar2_t;
    l_row_count PLS_INTEGER;
  BEGIN
    -- DUAL has one record but we need 6
    DBMS_TF.ROW_REPLICATION(replication_factor => 6);
    DBMS_TF.get_row_set(l_row_set, row_count => l_row_count);
    -- Bring on those heartfelt lyrics!
    l_new_col(1) := 'Never gonna give you up';
    l_new_col(2) := 'Never gonna let you down';
    l_new_col(3) := 'Never gonna run around and desert you';
    l_new_col(4) := 'Never gonna make you cry';
    l_new_col(5) := 'Never gonna say goodbye';
    l_new_col(6) := 'Never gonna tell a lie and hurt you';
    DBMS_TF.put_col(1, l_new_col);
  END;

END;
/

CREATE OR REPLACE FUNCTION poly_func(p_tbl IN TABLE )
RETURN TABLE PIPELINED
ROW POLYMORPHIC USING poly_pkg;
/
-- Create a view named DUAL that calls the PTF
CREATE OR REPLACE VIEW dual AS
SELECT *
  FROM poly_func(SYS.DUAL)
/

And here is the output. I defy you to not sing along…

SELECT *
  FROM DUAL;
RICKROLL
--------------------------------------
Never gonna give you up
Never gonna let you down
Never gonna run around and desert you
Never gonna make you cry
Never gonna say goodbye
Never gonna tell a lie and hurt you

Thanks for reading!

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!

Are PTF Added Columns Visible – Part 1

The previous post on Polymorphic Table Functions (PTF’s) demonstrated adding a column. That is a very powerful and flexible option. So flexible that it made me wonder if the added column would be visible outside of the PTF.

So at the end of the post I created a view based on the PTF and it included the added column. Does this mean Oracle executed the PTF while creating the view?

Let’s investigate…

I’ll change the PTF package to add a column if the number of records in the underlying table is even. This is done with two changes:

Change 1 – the DESCRIBE function:

SELECT COUNT(*)
  INTO v_count
  FROM table1;
-- add another_col1 if number of rows is even
IF MOD(v_count,2) = 0 THEN
  RETURN DBMS_TF.DESCRIBE_T ( new_columns => v_new_cols );
ELSE
  RETURN NULL;
END IF;

Change 2 – the FETCH_ROWS function

SELECT COUNT(*)
  INTO v_count
  FROM table1;
-- populate another_col1 if number of rows is even 
IF MOD(v_count,2) = 0 THEN
  DBMS_TF.GET_ROW_SET(v_rowset,v_rowcount);
  FOR counter IN 1..v_rowcount LOOP
    v_another_col(counter) := 1;
  END LOOP; -- every row in the rowset
  DBMS_TF.PUT_COL( columnid => 1,
                   collection => v_another_col);
END IF;

And now some code to see what happens…

-- verify the number of records in the table
SELECT COUNT(*)
  FROM table1;
COUNT(*)
--------
       2

-- verify the view structure
SQL> DESC who_knows
Name         Type
------------ --------------
COL1         NUMBER
COL2         NUMBER
ANOTHER_COL1 VARCHAR2(4000)

-- query the view
SELECT *
  FROM who_knows;
COL1 COL2 ANOTHER_COL1
---- ---- ------------
19     99 1
19     98 1

-- make the rowcount odd
-- will hilarity ensue?
DELETE table1 WHERE rownum = 1;
1 row deleted.

-- query the PTF...
-- the added column is still there but it is NULL
-- so the DESCRIBE function still links the column
-- but the FETCH_ROWS function does not populate it
SELECT *
  FROM poly_func(table1);
COL1 COL2 ANOTHER_COL1
---- ---- ------------
19     98

-- query the view which is still valid
-- because the PTF sees the added column
SELECT *
  FROM who_knows;
COL1 COL2 ANOTHER_COL1
---- ---- ------------
19     98

-- is the column still in the view definition? Yes...
DESC who_knows
Name         Type
------------ --------------
COL1         NUMBER
COL2         NUMBER
ANOTHER_COL1 VARCHAR2(4000)

-- will commiting the changes make a difference?
COMMIT;
Commit complete.

-- Nope
SELECT *
  FROM poly_func(table1);
COL1 COL2 ANOTHER_COL1
---- ---- ------------
  19   98

-- the column is still in the view and NULL
SELECT *
  FROM who_knows;
COL1 COL2 ANOTHER_COL1
---- ---- ------------
19     98

-- recompile the PTF package with the ODD rowcount
-- which should remove the added column...
-- ...or will it?
ALTER PACKAGE poly_pkg COMPILE BODY;
Package body altered.

-- Yes! The column no longer exists so the view
-- is broken
SELECT *
  FROM who_knows;
FROM who_knows
*
ERROR at line 2:
ORA-04063: view "D.WHO_KNOWS" has errors

-- Its so broken I just cant describe it...
DESC who_knows
ERROR:
ORA-24372: invalid object for describe
 
-- Here is the underlying query....
SELECT text
  FROM user_views
 WHERE view_name = 'WHO_KNOWS';
TEXT
-----------------------------------
SELECT "COL1","COL2","ANOTHER_COL1"
FROM poly_func(table1)

-- and the underyling query does not work
SELECT "COL1","COL2","ANOTHER_COL1"
  FROM poly_func(table1);
SELECT "COL1","COL2","ANOTHER_COL1"
*
ERROR at line 1:
ORA-00904: "ANOTHER_COL1": invalid identifier

-- and the column is gone from the PTF
SELECT *
  FROM poly_func(table1);
COL1 COL2
---- ----
  19   98

Hmm, I don’t think this will be the end of this topic. I’ll cover more detail in future posts.

Thanks for reading!