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!