Skip to Main Content
Feature Request FR-4713
Product Area APIs
Status OPEN

2 Voters

Add functions to apex_data_parser that return apex_t_varchar2

jonathan.wintermeyer Public · Nov 12 2025

Idea Summary
Add a function or functions that can return a row of data as an apex_t_varchar2 type from an apex_t_parser_table type and/or apex_t_parser_row type. Even maybe a function that will return a single value as varchar2(4000).

Use Case
This is helpful when you are trying to parse files and the number of columns are dynamic. Pivoting on the apex_t_parser_table type is cumbersome because there are 322 columns. Also, selecting all 300 col### columns each time is a lot of extra. It is useful to just be able to loop over the columns by number until you hit a null value or some other value and you can be reasonably sure you are done parsing that row.

Preferred Solution (Optional)
Currently, the functions in the comments are the solutions. Although, the APEX developers are probably capable of developing better ones. Attached them in the comments since they were too big to fit.

This idea is open.

Comments

Comments

  • jonathan.wintermeyer OP 9 days ago 
    -- Function to return a single value from an apex_t_parser_table type
FUNCTION get_data_parse_value( 
    p_parser_table IN APEX_T_PARSER_TABLE,
    p_row_number IN PLS_INTEGER,
    p_col_number IN PLS_INTEGER ) RETURN VARCHAR2
IS
    l_dynamic_sql    VARCHAR2(32767);
    l_col_name       VARCHAR2(8);
    l_col_value      VARCHAR2(4000);
BEGIN
    IF p_col_number NOT BETWEEN 1 AND 300 THEN
        raise_application_error( -20002, 'Column number outside limit.' );
    END IF;
    IF p_row_number < 1 THEN
        raise_application_error( -20003, 'Row number less than 1.' );
    END IF;
    IF p_col_number < 1 THEN
        raise_application_error( -20004, 'Column number less than 1.' );
    END IF;
    
    l_col_name := 'col' || TRIM( TO_CHAR( p_col_number, '000' ));
    l_dynamic_sql := 'SELECT ' || l_col_name || ' FROM TABLE( :1 ) WHERE line_number = :2';
    
    BEGIN
        EXECUTE IMMEDIATE l_dynamic_sql INTO l_col_value USING p_parser_table, p_row_number;
    EXCEPTION WHEN NO_DATA_FOUND THEN
        l_col_value := NULL;
    END;
    
    RETURN l_col_value;
EXCEPTION WHEN OTHERS THEN
    -- Log error
    RAISE;
END get_data_parse_value;
  • jonathan.wintermeyer OP 9 days ago 
    -- Function to return a single value from an apex_t_parser_row type
FUNCTION get_data_parse_value( 
    p_parser_row IN APEX_T_PARSER_ROW,
    p_col_number IN PLS_INTEGER ) RETURN VARCHAR2
IS
    l_dynamic_sql    VARCHAR2(32767);
    l_col_name       VARCHAR2(8);
    l_col_value      VARCHAR2(4000);
BEGIN
    IF p_col_number NOT BETWEEN 1 AND 300 THEN
        raise_application_error( -20002, 'Column number outside limit.' );
    END IF;
    IF p_col_number < 1 THEN
        raise_application_error( -20004, 'Column number less than 1.' );
    END IF;
    
    l_col_name := 'col' || TRIM( TO_CHAR( p_col_number, '000' ));
    l_dynamic_sql := 'SELECT ' || l_col_name || ' FROM TABLE( apex_t_parser_table(:1) )';
    
    BEGIN
        EXECUTE IMMEDIATE l_dynamic_sql INTO l_col_value USING p_parser_row;
    EXCEPTION WHEN NO_DATA_FOUND THEN
        l_col_value := NULL;
    END;
    
    RETURN l_col_value;
EXCEPTION WHEN OTHERS THEN
    -- Log error
    RAISE;
END get_data_parse_value;
  • jonathan.wintermeyer OP 9 days ago 
    -- Function to return the values from a row in an apex_t_parser_table type
FUNCTION get_data_parse_values( 
    p_parser_table IN APEX_T_PARSER_TABLE,
    p_row_number IN PLS_INTEGER ) RETURN APEX_T_VARCHAR2
IS
    l_col_values    apex_t_varchar2;
BEGIN
    IF p_row_number < 1 THEN
        raise_application_error( -20003, 'Row number less than 1.' );
    END IF;
    
    BEGIN
        SELECT
            apex_t_varchar2( col001,col002,col003,col004,col005,col006,col007,col008,col009,col010,
                             col011,col012,col013,col014,col015,col016,col017,col018,col019,col020,
                             col021,col022,col023,col024,col025,col026,col027,col028,col029,col030,
                             col031,col032,col033,col034,col035,col036,col037,col038,col039,col040,
                             col041,col042,col043,col044,col045,col046,col047,col048,col049,col050,
                             col051,col052,col053,col054,col055,col056,col057,col058,col059,col060,
                             col061,col062,col063,col064,col065,col066,col067,col068,col069,col070,
                             col071,col072,col073,col074,col075,col076,col077,col078,col079,col080,
                             col081,col082,col083,col084,col085,col086,col087,col088,col089,col090,
                             col091,col092,col093,col094,col095,col096,col097,col098,col099,col100,
                             col101,col102,col103,col104,col105,col106,col107,col108,col109,col110,
                             col111,col112,col113,col114,col115,col116,col117,col118,col119,col120,
                             col121,col122,col123,col124,col125,col126,col127,col128,col129,col130,
                             col131,col132,col133,col134,col135,col136,col137,col138,col139,col140,
                             col141,col142,col143,col144,col145,col146,col147,col148,col149,col150,
                             col151,col152,col153,col154,col155,col156,col157,col158,col159,col160,
                             col161,col162,col163,col164,col165,col166,col167,col168,col169,col170,
                             col171,col172,col173,col174,col175,col176,col177,col178,col179,col180,
                             col181,col182,col183,col184,col185,col186,col187,col188,col189,col190,
                             col191,col192,col193,col194,col195,col196,col197,col198,col199,col200,
                             col201,col202,col203,col204,col205,col206,col207,col208,col209,col210,
                             col211,col212,col213,col214,col215,col216,col217,col218,col219,col220,
                             col221,col222,col223,col224,col225,col226,col227,col228,col229,col230,
                             col231,col232,col233,col234,col235,col236,col237,col238,col239,col240,
                             col241,col242,col243,col244,col245,col246,col247,col248,col249,col250,
                             col251,col252,col253,col254,col255,col256,col257,col258,col259,col260,
                             col261,col262,col263,col264,col265,col266,col267,col268,col269,col270,
                             col271,col272,col273,col274,col275,col276,col277,col278,col279,col280,
                             col281,col282,col283,col284,col285,col286,col287,col288,col289,col290,
                             col291,col292,col293,col294,col295,col296,col297,col298,col299,col300 )
                INTO l_col_values
          FROM TABLE( p_parser_table )
        WHERE line_number = p_row_number;
    EXCEPTION WHEN NO_DATA_FOUND THEN
        l_col_values := apex_t_varchar2();
    END;
    
    RETURN l_col_values;
EXCEPTION WHEN OTHERS THEN
    -- Log error
    RAISE;
END get_data_parse_values;
  • jonathan.wintermeyer OP 9 days ago 
    -- Function to return the values from an apex_t_parser_row type
FUNCTION get_data_parse_values( 
    p_parser_row IN APEX_T_PARSER_ROW ) RETURN APEX_T_VARCHAR2
IS
    l_col_values    apex_t_varchar2;
BEGIN
    BEGIN
        SELECT
            apex_t_varchar2( col001,col002,col003,col004,col005,col006,col007,col008,col009,col010,
                             col011,col012,col013,col014,col015,col016,col017,col018,col019,col020,
                             col021,col022,col023,col024,col025,col026,col027,col028,col029,col030,
                             col031,col032,col033,col034,col035,col036,col037,col038,col039,col040,
                             col041,col042,col043,col044,col045,col046,col047,col048,col049,col050,
                             col051,col052,col053,col054,col055,col056,col057,col058,col059,col060,
                             col061,col062,col063,col064,col065,col066,col067,col068,col069,col070,
                             col071,col072,col073,col074,col075,col076,col077,col078,col079,col080,
                             col081,col082,col083,col084,col085,col086,col087,col088,col089,col090,
                             col091,col092,col093,col094,col095,col096,col097,col098,col099,col100,
                             col101,col102,col103,col104,col105,col106,col107,col108,col109,col110,
                             col111,col112,col113,col114,col115,col116,col117,col118,col119,col120,
                             col121,col122,col123,col124,col125,col126,col127,col128,col129,col130,
                             col131,col132,col133,col134,col135,col136,col137,col138,col139,col140,
                             col141,col142,col143,col144,col145,col146,col147,col148,col149,col150,
                             col151,col152,col153,col154,col155,col156,col157,col158,col159,col160,
                             col161,col162,col163,col164,col165,col166,col167,col168,col169,col170,
                             col171,col172,col173,col174,col175,col176,col177,col178,col179,col180,
                             col181,col182,col183,col184,col185,col186,col187,col188,col189,col190,
                             col191,col192,col193,col194,col195,col196,col197,col198,col199,col200,
                             col201,col202,col203,col204,col205,col206,col207,col208,col209,col210,
                             col211,col212,col213,col214,col215,col216,col217,col218,col219,col220,
                             col221,col222,col223,col224,col225,col226,col227,col228,col229,col230,
                             col231,col232,col233,col234,col235,col236,col237,col238,col239,col240,
                             col241,col242,col243,col244,col245,col246,col247,col248,col249,col250,
                             col251,col252,col253,col254,col255,col256,col257,col258,col259,col260,
                             col261,col262,col263,col264,col265,col266,col267,col268,col269,col270,
                             col271,col272,col273,col274,col275,col276,col277,col278,col279,col280,
                             col281,col282,col283,col284,col285,col286,col287,col288,col289,col290,
                             col291,col292,col293,col294,col295,col296,col297,col298,col299,col300 )
                INTO l_col_values
          FROM TABLE( apex_t_parser_table( p_parser_row ));
    EXCEPTION WHEN NO_DATA_FOUND THEN
        l_col_values := apex_t_varchar2();
    END;
    
    RETURN l_col_values;
EXCEPTION WHEN OTHERS THEN
    -- Log error
    RAISE;
END get_data_parse_values;
  • eric.olson OP 3 days ago

    I have to handle Excel files where the data is nicely formatted in a table, with one row per sample analyzed, but the number of columns is not known beforehand. The easiest way to do that is to look at the header row and stop once the first blank cell is reached. This feature would make that easier by making the data available in an array. Otherwise, it's a lot of:

    if x.col002 is null then
    number_of_cols := 1;
elsif x.col003 is null then
    number_of_cols := 2;
...