Overcoming PL/SQL Output Restrictions in ODP.NET and BI Tools

Working in locked-down Oracle environments where you lack CREATE permissions can be frustrating. When your client interface (like ODP.NET or a BI Data Dictionary tool) doesn't support DBMS_OUTPUT.PUT_LINE or DBMS_SQL.RETURN_RESULTS, fetching dynamic or procedural tabular data seems almost impossible.

If you've attempted inline WITH FUNCTION queries returning a SYS_REFCURSOR and ended up with unexportable (DATASET) cells, or tried PL/SQL record tables that yielded useless "x rows affected" buffers, you are not alone. Luckily, there are clean, modern techniques to output tabular data without creating database objects or relying on standard print buffers.

Method 1: Inline Functions Returning JSON or XML (Recommended for Oracle 12c+)

Since Oracle 12c, you can define inline PL/SQL functions within a standard SQL WITH clause. While returning a SYS_REFCURSOR directly produces nested dataset objects in many BI clients, returning formatted JSON or XML from the inline function allows you to immediately flatten the results back into tabular columns using JSON_TABLE or XMLTABLE.

Using JSON_TABLE with an Inline Function

WITH FUNCTION get_report_data(p_id IN NUMBER) RETURN CLOB IS
    v_json CLOB;
BEGIN
    SELECT JSON_ARRAYAGG(
             JSON_OBJECT('DataValue1' VALUE DataValue1, 
                         'DataValue2' VALUE DataValue2)
             RETURNING CLOB
           )
    INTO v_json
    FROM MyTable
    WHERE ID = p_id;
    
    RETURN v_json;
END;
SELECT j.DataValue1, j.DataValue2
FROM DUAL,
     JSON_TABLE(get_report_data(100), 
                '$[*]'
                COLUMNS (
                    DataValue1 VARCHAR2(50) PATH '$.DataValue1',
                    DataValue2 VARCHAR2(50) PATH '$.DataValue2'
                )
     ) j;

Why this works: The inline function constructs a JSON string containing your dataset, which the SQL engine parses back into regular rows and columns. ODP.NET and BI tools recognize this as a simple relational SELECT statement, making it completely exportable.

Method 2: Unnesting Built-in System Collections (SYS.ODCIVARCHAR2LIST)

If JSON functions aren't available or feel too heavy, you can use Oracle's built-in SYS.ODCIVARCHAR2LIST collection type without needing CREATE TYPE privileges. To output multiple columns, concatenate your field values with a delimiter (such as a pipe |) and split them in the outer SQL statement.

WITH FUNCTION get_delimited_data(p_id IN NUMBER) RETURN sys.odcivarchar2list IS
    v_list sys.odcivarchar2list := sys.odcivarchar2list();
BEGIN
    FOR r IN (SELECT DataValue1, DataValue2 FROM MyTable WHERE ID = p_id) LOOP
        v_list.EXTEND;
        v_list(v_list.COUNT) := r.DataValue1 || '|' || r.DataValue2;
    END LOOP;
    RETURN v_list;
END;
SELECT 
    REGEXP_SUBSTR(column_value, '[^|]+', 1, 1) AS DataValue1,
    REGEXP_SUBSTR(column_value, '[^|]+', 1, 2) AS DataValue2
FROM TABLE(get_delimited_data(100));

Method 3: Refactoring PL/SQL Loops into Pure SQL Generator CTEs

In BI reporting, developers often use PL/SQL loops to iterate over date ranges (daily, monthly, yearly). However, PL/SQL loops slow down query execution compared to set-based operations and introduce client output issues.

Instead of looping in PL/SQL, generate date ranges dynamically using standard SQL CTEs combined with LATERAL or CROSS JOIN operations:

WITH date_range AS (
    -- Generate last 30 days dynamically
    SELECT TRUNC(SYSDATE) - LEVEL + 1 AS report_date
    FROM DUAL
    CONNECT BY LEVEL <= 30
)
SELECT 
    d.report_date,
    t.DataValue1,
    t.DataValue2
FROM date_range d
LEFT JOIN MyTable t 
    ON t.created_date >= d.report_date 
   AND t.created_date < d.report_date + 1;

Which Method Should You Choose?

  • Choose JSON_TABLE / XMLTABLE (Method 1) if you must execute complex procedural PL/SQL logic (e.g., condition evaluation, dynamic calculations) before outputting multi-column data.
  • Choose SYS.ODCIVARCHAR2LIST (Method 2) if you are working on older database versions where JSON tools are not available.
  • Choose Pure SQL (Method 3) whenever your goal is simply looping over date ranges or joining similar tables. Pure SQL executes faster, eliminates PL/SQL context switching, and guarantees direct compatibility with BI tools and ODP.NET.