Bulk Load CSTORE Values

This post is another example Compilation State Management with CSTORE values. It loads 10 strings of CSTORE values, attaches them to the cursor and then returns them as part of the eventual query. The difference from previous posts is this one retrieves the CSTORE values in bulk.

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

The package body.

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_col1 DBMS_TF.COLUMN_METADATA_T;
    v_new_cols DBMS_TF.COLUMNS_NEW_T;
    v_chr_t DBMS_TF.CSTORE_CHR_T;
  BEGIN
    p_tbl.column(1).pass_through := FALSE;
    v_new_col1 := DBMS_TF.COLUMN_METADATA_T(
          type    => DBMS_TF.TYPE_VARCHAR2,
          name    => 'CHR_T',
          max_len => 15 );
    v_new_cols := DBMS_TF.COLUMNS_NEW_T( 1 => v_new_col1 );
    --
    -- Load 10 string values into the CSTORE_CHR_T
    -- table and then...
    --
    FOR counter IN 1..10 LOOP
      v_chr_t(counter) := 'CHR ' || counter;
    END LOOP;
    --
    -- ...attach it to the cursor
    ---
    RETURN(DBMS_TF.DESCRIBE_T( row_replication => TRUE,
                               cstore_chr => v_chr_t,
                               new_columns => v_new_cols ));
  END;

  /*----------------------------------------------------*/
  PROCEDURE fetch_rows IS
  /*----------------------------------------------------*/
    v_rowset DBMS_TF.ROW_SET_T;
    v_added DBMS_TF.COLUMN_DATA_T;
    v_chr_t DBMS_TF.CSTORE_CHR_T;
  BEGIN
    --
    -- Retrieve the CSTORE_CHR_T values in bulk
    -- attached during the DESCRIBE call...
    DBMS_TF.CSTORE_GET(v_chr_t);
    IF NVL(v_chr_t.COUNT,0) > 0 THEN
      --
      -- Set the replication factor to the
      -- number of rows in the CSTORE_CHR_T table...
      DBMS_TF.ROW_REPLICATION(v_chr_t.COUNT);
      --
      -- add each STORE_CHR_T row to the rowset...
      FOR counter IN 1..v_chr_t.COUNT LOOP
        v_added.tab_varchar2(counter) := v_chr_t(counter);
      END LOOP;
    ELSE
      DBMS_TF.ROW_REPLICATION(0);
    END IF;
    -- and attach new rowset to the main one
    v_rowset(1) := v_added;
    DBMS_TF.PUT_ROW_SET( rowset => v_rowset);
  END;

END;

The function:

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

The query and results.

SELECT *
  FROM poly_func(dual);
CHR_T
---------------
CHR 1
CHR 2
CHR 3
CHR 4
CHR 5
CHR 6
CHR 7
CHR 8
CHR 9
CHR 10

10 rows selected.

Thanks for reading.