PTF’S – Adding Columns and Rows Deep Dive Part 2

Populating new columns in PTF’s is done using PL/SQL tables defined in the DBMS_TF package. There is one PL/SQL table type for every datatype. For example here is the one for VARCHAR2 data.

TYPE TAB_VARCHAR2_T IS TABLE OF VARCHAR2(32767)
  INDEX BY PLS_INTEGER;

This means it holds multiple string values of up to 32767 characters.

The association between data values and the columns they will occupy is defined in the COLUMN_DATA_T PL/SQL record.

The PL/SQL table and record entries are manipulated using standard PL/SQL as shown below:

DECLARE
  v_column_data  DBMS_TF.COLUMN_DATA_T;
  v_tab_varchar2 DBMS_TF.TAB_VARCHAR2_T;
BEGIN
  v_tab_varchar2(1) := 'ABC';
  v_tab_varchar2(2) := 'DEF';
  v_tab_varchar2(3) := 'GHI';
  v_column_data.tab_varchar2 := v_tab_varchar2;
  v_column_data.tab_number := DBMS_TF.TAB_NUMBER_T( 1 => 1,
                                                    2 => 2,
                                                    3 => 3 );
  FOR counter IN 1..3 LOOP
    DBMS_OUTPUT.PUT_LINE( 
          v_column_data.tab_varchar2(counter) || ' ' ||
          v_column_data.tab_number(counter));
  END LOOP;
  v_column_data.tab_number.DELETE(2);
  FOR counter IN 1..3 LOOP
    BEGIN
      DBMS_OUTPUT.PUT_LINE( 
          v_column_data.tab_varchar2(counter) || ' ' ||
          v_column_data.tab_number(counter));
    EXCEPTION
      WHEN NO_DATA_FOUND THEN NULL;
    END;
  END LOOP;
  FOR counter IN 1..3 LOOP
    IF v_column_data.tab_varchar2.EXISTS(counter) THEN
      DBMS_OUTPUT.PUT( 
          v_column_data.tab_varchar2(counter) || ' ');
    END IF;
    IF v_column_data.tab_number.EXISTS(counter) THEN
      DBMS_OUTPUT.PUT(
          v_column_data.tab_number(counter) );
    END IF;
    DBMS_OUTPUT.PUT_LINE('');
  END LOOP;
END;

Note the need to handle exceptions when accessing rows with no values. If not handled this error is thrown:

ORA-06502: PL/SQL: numeric or value error:
           character to number conversion error

Here are the results of the code:

ABC 1
DEF 2
GHI 3
ABC 1
GHI 3
ABC 1
DEF
GHI 3

Here is the full list of available PL/SQL tables.

TAB_VARCHAR2_T
TAB_NUMBER_T
TAB_DATE_T
TAB_BINARY_FLOAT_T
TAB_BINARY_DOUBLE_T
TAB_RAW_T
TAB_CHAR_T
TAB_CLOB_T
TAB_BLOB_T
TAB_TIMESTAMP_T;
TAB_TIMESTAMP_TZ_T
TAB_INTERVAL_YM_T
TAB_INTERVAL_DS_T
TAB_TIMESTAMP_LTZ_T
TAB_BOOLEAN_T 
TAB_ROWID_T 
TAB_NATURALN_T

The COLUMN_DATA_T associates the column to its data. It has this structure:

TYPE COLUMN_DATA_T IS RECORD
  ( description            COLUMN_METADATA_T, 
    tab_varchar2           TAB_VARCHAR2_T,
    tab_number             TAB_NUMBER_T,
    tab_date               TAB_DATE_T,
    tab_binary_float       TAB_BINARY_FLOAT_T,
    tab_binary_double      TAB_BINARY_DOUBLE_T,
    tab_raw                TAB_RAW_T,
    tab_char               TAB_CHAR_T,
    tab_clob               TAB_CLOB_T,
    tab_blob               TAB_BLOB_T,
    tab_timestamp          TAB_TIMESTAMP_T,
    tab_timestamp_tz       TAB_TIMESTAMP_TZ_T,
    tab_interval_ym        TAB_INTERVAL_YM_T,
    tab_interval_ds        TAB_INTERVAL_DS_T,    
    tab_timestamp_ltz      TAB_TIMESTAMP_LTZ_T,
    tab_rowid              TAB_ROWID_T);

Within the PTF you can populate any of the PL/SQL tables even if they are not returned. So how does the PTF decide which one to return? I’m glad you asked because I’ll cover it in the next post.

Thanks for reading!