Forum Stats

  • 3,817,367 Users
  • 2,259,322 Discussions
  • 7,893,760 Comments

Discussions

APEX_DATA_PARSER.PARSE throws ORA-06553: PLS-306

Toufiq - Hexagon PPM
Toufiq - Hexagon PPM Member Posts: 21 Red Ribbon
edited May 17, 2021 11:13AM in APEX Discussions

Hi,

I am currently on APEX 20.2.

I am trying to parse an XLSX file using APEX_DATA_PARSER.PARSE utility.

This is how I am trying to accomplish this:

DECLARE
  l_blob     BLOB;
  l_filename   VARCHAR2 (1000);
  l_context   apex_exec.t_context;
  l_parameters  apex_exec.t_parameters;
BEGIN
  SELECT blob_content, filename
   INTO l_blob, l_filename
   FROM blob_table;

  apex_exec.ADD_PARAMETER (l_parameters, 'blob', l_blob);
  apex_exec.ADD_PARAMETER (l_parameters, 'filename', l_filename);

  l_context :=
    apex_exec.open_query_context (
      p_location     => apex_exec.c_location_local_db,
      p_sql_query    => q'#SELECT *
      FROM  TABLE(
        APEX_DATA_PARSER.parse(
          p_content     => :blob,
          p_file_name => :filename
     ))#',
      p_sql_parameters  => l_parameters);
  apex_json.open_object;
  apex_json.write_context (p_name => 'results', p_context => l_context);
  apex_json.close_object;
END;

The response is

sqlerrm:ORA-06553: PLS-306: wrong number or types of arguments in call to 'PARSE'


However, if I use APEX_DATA_PARSER.parse outside APEX_EXEC call, this works fine without issues.

Also the above code works fine in latest APEX 21.1 (on apex.oracle.com)

I am wondering if this is a known issue? If yes, is there any workaround or patch available?

Thankyou.

-- Toufiq

Tagged:

Best Answer

  • Toufiq - Hexagon PPM
    Toufiq - Hexagon PPM Member Posts: 21 Red Ribbon
    Answer ✓

    Got a reply from @Carsten Czarski-Oracle

    His reply :

    The APEX_EXEC infrastructure reuses a lot of internal APEX infrastructure, especially when processing bind variables. And in APEX session state, everything is a VARCHAR2. For that reason, up to APEX 20.2, you can only use Bind Variables with APEX_EXEC which can be converted to a VARCHAR2, which is not the case for a BLOB.


    Thus you see the error.


    For 21.1, a few of these restrictions have been lifted, so that the code can work now.


    If you're not on APEX 21.1 yet, my recommendation would be to work without APEX_EXEC - is there a specific reason why you wanted to use APEX_EXEC for this?


    My requirement can be accomplished without APEX_EXEC as below:

    DECLARE
        l_blob       BLOB;
        l_filename   VARCHAR2 (1000);
        l_cursor     SYS_REFCURSOR;
    BEGIN
        SELECT blob_content, filename
          INTO l_blob, l_filename
          FROM blob_table;
    
    
        OPEN l_cursor FOR
            SELECT *
              FROM TABLE (
                       apex_data_parser.parse (p_content     => l_blob,
                                               p_file_name   => l_filename));
    
    
        apex_json.open_object;
        apex_json.write ('results', l_cursor);
        apex_json.close_object;
    END;
    


Answers

  • Toufiq - Hexagon PPM
    Toufiq - Hexagon PPM Member Posts: 21 Red Ribbon
    Answer ✓

    Got a reply from @Carsten Czarski-Oracle

    His reply :

    The APEX_EXEC infrastructure reuses a lot of internal APEX infrastructure, especially when processing bind variables. And in APEX session state, everything is a VARCHAR2. For that reason, up to APEX 20.2, you can only use Bind Variables with APEX_EXEC which can be converted to a VARCHAR2, which is not the case for a BLOB.


    Thus you see the error.


    For 21.1, a few of these restrictions have been lifted, so that the code can work now.


    If you're not on APEX 21.1 yet, my recommendation would be to work without APEX_EXEC - is there a specific reason why you wanted to use APEX_EXEC for this?


    My requirement can be accomplished without APEX_EXEC as below:

    DECLARE
        l_blob       BLOB;
        l_filename   VARCHAR2 (1000);
        l_cursor     SYS_REFCURSOR;
    BEGIN
        SELECT blob_content, filename
          INTO l_blob, l_filename
          FROM blob_table;
    
    
        OPEN l_cursor FOR
            SELECT *
              FROM TABLE (
                       apex_data_parser.parse (p_content     => l_blob,
                                               p_file_name   => l_filename));
    
    
        apex_json.open_object;
        apex_json.write ('results', l_cursor);
        apex_json.close_object;
    END;