Search code examples
oracle-apex

Apex 22.1 How to pass filters to OData Rest Service

I have installed OData Rest Service plugin and defined REST Data Source against this REST source type. The .net based OData Service is returning all rows of a DB table. This table contains multiple LOVs differentiated by a Type column.

Then added a LOV using this REST Data Source, added a Popup Lov on the page and it shows all rows. So far so good.

OData filters are working via Browser as well.

Post Processing Type in LOV has an option of using SQL query, by this option filters/where can also be applied but the .net based OData Service will have to return all rows of the table and then APEX will filter desired results.

How can i pass OData filters from APEX so that only required data is returned from OData Service?

Edit 1

OData Service is working fine with filters on the browser enter image description here

I have already tried setting following filters one by one in External Filter under Advanced REST Data Source Attributes on LOV edit page but none of these worked

$filter=CodeType eq 135
?$filter=CodeType eq 135
$filter=CodeType%20eq%20135
?$filter=CodeType%20eq%20135
filter=CodeType eq 135

and the error is

l_error_pos=1, l_start_pos=1, match_pos=14

Exception in "begin declare

begin plg_odata_connector.fetch_web_source (p_plugin => wwv_flow_plugin_api.g_plugin,p_web_source => wwv_flow_plugin_api.g_web_source,p_params => wwv_flow_plugin_api.g_web_source_fetch_params,p_result => wwv_flow_plugin_api.g_web_source_fetch_result );end; end;": Error Stack: ORA-06531: Reference to uninitialized collection ORA-06512: at "DEV1.PLG_ODATA_FILTERS", line 117 ORA-06512: at "DEV1.PLG_ODATA_FILTERS", line 1087 ORA-06512: at "DEV1.PLG_ODATA_CONNECTOR", line 839 ORA-06512: at line 4 ORA-06512: at "SYS.DBMS_SYS_SQL", line 2164 ORA-06512: at "SYS.WWV_DBMS_SQL_APEX_220100", line 856 Backtrace: ORA-06512: at "DEV1.PLG_ODATA_FILTERS", line 117 ORA-06512: at "DEV1.PLG_ODATA_FILTERS", line 1087 ORA-06512: at "DEV1.PLG_ODATA_CONNECTOR", line 839 ORA-06512: at line 4 ORA-06512: at "SYS.DBMS_SYS_SQL", line 2164 ORA-06512: at "SYS.WWV_DBMS_SQL_APEX_220100", line 856 ORA-06512: at "APEX_220100.WWV_FLOW_DYNAMIC_EXEC", line 2452 ORA-06512: at "DEV1.PLG_ODATA_FILTERS", line 117 ORA-06512: at "DEV1.PLG_ODATA_FILTERS", line 1087 ORA-06512: at "DEV1.PLG_ODATA_CONNECTOR", line 839 ORA-06512: at line 4 ORA-06512: at "SYS.DBMS_SYS_SQL", line 2164 ORA-06512: at "SYS.WWV_DBMS_SQL_APEX_220100", line 856 ORA-06512: at "APEX_220100.WWV_FLOW_DYNAMIC_EXEC", line 2452

Error: Error in PLSQL code raised during plug-in processing.

Edit 2

Tried the same on Apex 23.1.1(https://apex.oracle.com), failed with different errors.

  1. Imported OData plugin from https://github.com/oracle/apex/tree/22.2/plugins/rest-source/odata-rest-apis
  2. Added https://services.odata.org/TripPinRESTierService/People as REST Data Source
  3. Added an LOV without filters against this source and all people were shown in a popup list
  4. Added UserName%20eq%20%27sandyosborn%27 as an External Filter which resulted errors. Partial Full Trace is

module_id: 39660548962068387941 operation_id: 39660549197756387942 url: https://services.odata.org/TripPinRESTierService query_string: http_method: GET db_operation: 1 get_web_source_operation p_db_operation=>1,p_perform_init=>false,p_preserve_headers=>false PLG_ODATA_CONNECTOR.plugin_attributes Exit plugin_attributes PLG_ODATA_CONNECTOR. Exit get_requested_columns. return=FirstName,UserName Web Source Attributes Info

attribute_01: People attribute_02: attribute_03: option_orderby:option_select:option_search:option_client_driven_paging attribute_04: none attribute_05: N attribute_06: count_none Web Source Fetch Params Info

requested_columns: [FirstName,UserName] max_rows: 101 first_row: 1 fetch_all_rows: false fixed_page_size: request_context: external_filters: UserName%20eq%20%27sandyosborn%27 Web Source Fetch Params Info 2

order_bys: true requested_columns: true filters: true primary_key_values: false initial_request: true Original Query String is: Fetching Loop Counter (Page) = 1 ODATA Filter Format Creation incoming with ext Filter = UserName%20eq%20%27sandyosborn%27 PLG_ODATA_FILTERS.odata_filter_format Total Filters Count = 0 PLG_ODATA_FILTERS.get_sanitized_external_filter Not Sanitized External Filter=UserName%20eq%20%27sandyosborn%27 Exception in "begin declare

begin plg_odata_connector.fetch_web_source (p_plugin => wwv_flow_plugin_api.g_plugin,p_web_source => wwv_flow_plugin_api.g_web_source,p_params => wwv_flow_plugin_api.g_web_source_fetch_params,p_result => wwv_flow_plugin_api.g_web_source_fetch_result );end; end;": Error Stack: ORA-06531: Reference to uninitialized collection ORA-06512: at "WKSP_TESTBRM.PLG_ODATA_FILTERS", line 117 ORA-06512: at "WKSP_TESTBRM.PLG_ODATA_FILTERS", line 1087 ORA-06512: at "WKSP_TESTBRM.PLG_ODATA_CONNECTOR", line 839 ORA-06512: at line 4 ORA-06512: at "SYS.DBMS_SYS_SQL", line 2120 ORA-06512: at "SYS.WWV_DBMS_SQL_APEX_230100", line 806 Backtrace: ORA-06512: at "WKSP_TESTBRM.PLG_ODATA_FILTERS", line 117 ORA-06512: at "WKSP_TESTBRM.PLG_ODATA_FILTERS", line 1087 ORA-06512: at "WKSP_TESTBRM.PLG_ODATA_CONNECTOR", line 839 ORA-06512: at line 4 ORA-06512: at "SYS.DBMS_SYS_SQL", line 2120 ORA-06512: at "SYS.WWV_DBMS_SQL_APEX_230100", line 806 ORA-06512: at "APEX_230100.WWV_FLOW_DYNAMIC_EXEC", line 2603 ORA-06512: at "WKSP_TESTBRM.PLG_ODATA_FILTERS", line 117 ORA-06512: at "WKSP_TESTBRM.PLG_ODATA_FILTERS", line 1087 ORA-06512: at "WKSP_TESTBRM.PLG_ODATA_CONNECTOR", line 839 ORA-06512: at line 4 ORA-06512: at "SYS.DBMS_SYS_SQL", line 2120 ORA-06512: at "SYS.WWV_DBMS_SQL_APEX_230100", line 806 ORA-06512: at "APEX_230100.WWV_FLOW_DYNAMIC_EXEC", line 2603 setting g_primary_language := en Add error onto error stack: Error in PLSQL code raised during plug-in processing. ... get_component -> APEX_APPLICATION_PAGE_ITEMS P1_NEW Error: Error in PLSQL code raised during plug-in processing.

  • Additional info: Contact your application administrator. Details about this incident are available via debug id "1537413071".
  • Display location: ON_ERROR_PAGE
  • Association type:
  • Item name:
  • Region id:
  • Column alias:
  • Row:
  • Model instance id:
  • Model record id:
  • Internal error: true
  • Common runtime error: false
  • APEX error code: WWV_FLOW_PLUGIN.RUN_PLSQL_ERR
  • SQL code: -6531
  • SQL error: ORA-06531: Reference to uninitialized collection ORA-06512: at "APEX_230100.WWV_FLOW_CODE_EXEC_PLSQL", line 126 ORA-06512: at "APEX_230100.WWV_FLOW_DYNAMIC_EXEC", line 2643 ORA-06512: at "WKSP_TESTBRM.PLG_ODATA_FILTERS", line 117 ORA-06512: at "WKSP_TESTBRM.PLG_ODATA_FILTERS", line 1087 ORA-06512: at "WKSP_TESTBRM.PLG_ODATA_CONNECTOR", line 839 ORA-06512: at line 4 ORA-06512: at "SYS.DBMS_SYS_SQL", line 2120 ORA-06512: at "SYS.WWV_DBMS_SQL_APEX_230100", line 806 ORA-06512: at "APEX_230100.WWV_FLOW_DYNAMIC_EXEC", line 2603 ORA-06512: at "APEX_230100.WWV_FLOW_CODE_EXEC_PLSQL", line 97 ORA-06512: at "APEX_230100.WWV_FLOW_CODE_EXEC_PLSQL", line 399 ORA-06512: at "APEX_230100.WWV_FLOW_CODE_EXEC", line 553
  • Backtrace: ORA-06512: at "WKSP_TESTBRM.PLG_ODATA_FILTERS", line 117 ORA-06512: at "WKSP_TESTBRM.PLG_ODATA_FILTERS", line 1087 ORA-06512: at "WKSP_TESTBRM.PLG_ODATA_CONNECTOR", line 839 ORA-06512: at line 4 ORA-06512: at "SYS.DBMS_SYS_SQL", line 2120 ORA-06512: at "SYS.WWV_DBMS_SQL_APEX_230100", line 806 ORA-06512: at "APEX_230100.WWV_FLOW_DYNAMIC_EXEC", line 2603 ORA-06512: at "APEX_230100.WWV_FLOW_CODE_EXEC_PLSQL", line 126 ORA-06512: at "APEX_230100.WWV_FLOW_DYNAMIC_EXEC", line 2643 ORA-06512: at "WKSP_TESTBRM.PLG_ODATA_FILTERS", line 117 ORA-06512: at "WKSP_TESTBRM.PLG_ODATA_FILTERS", line 1087 ORA-06512: at "WKSP_TESTBRM.PLG_ODATA_CONNECTOR", line 839 ORA-06512: at line 4 ORA-06512: at "SYS.DBMS_SYS_SQL", line 2120 ORA-06512: at "SYS.WWV_DBMS_SQL_APEX_230100", line 806 ORA-06512: at "APEX_230100.WWV_FLOW_DYNAMIC_EXEC", line 2603 ORA-06512: at "APEX_230100.WWV_FLOW_CODE_EXEC_PLSQL", line 97 ORA-06512: at "APEX_230100.WWV_FLOW_CODE_EXEC_PLSQL", line 399 ORA-06512: at "APEX_230100.WWV_FLOW_CODE~
  • Statement: begin declare

begin plg_odata_connector.fetch_web_source (p_plugin => wwv_flow_plugin_api.g_plugin,p_web_source => wwv_flow_plugin_api.g_web_source,p_params => wwv_flow_plugin_api.g_web_source_fetch_params,p_result => wwv_flow_plugin_api.g_web_source_fetch_result );end; end;

  • Component: APEX_APPLICATION_PAGE_ITEMS P1_NEW (35162212600458618115) ...Show Error on Error Page ......Performing rollback do_rollback reset p_keep_sticky_headers=>true add_header p_key=>Apex-Error-Code,p_name=>Apex-Error-Code,p_value=>WWV_FLOW_PLUGIN.RUN_PLSQL_ERR,p_overwrite=>true,p_is_sticky=>true add_header p_key=>Apex-Error,p_name=>Apex-Error,p_value=>Error in PLSQL code raised during plug-in processing.,p_overwrite=>true,p_is_sticky=>true write_headers p_close_header_section=>true ... no header that is known to sys.htp - emit Content-Type text/html ... get_component -> APEX_APPLICATION_PAGE_ITEMS P1_NEW Exception in "P1_NEW": Error Stack: ORA-20987: APEX - Error in PLSQL code raised during plug-in processing. - Contact your application administrator. Details about this incident are available via debug id "1537413071". ORA-06512: at "APEX_230100.WWV_FLOW_EXEC_WEB_SRC", line 2322 ORA-06512: at "APEX_230100.WWV_FLOW_EXEC_WEB_SRC", line 2238 ORA-06512: at "APEX_230100.WWV_FLOW_ERROR", line 1075 ORA-06512: at "APEX_230100.WWV_FLOW_ERROR", line 1523 ORA-06512: at "APEX_230100.WWV_FLOW_PLUGIN", line 2193 ORA-06512: at "APEX_230100.WWV_FLOW_PLUGIN", line 4095 ORA-06512: at "APEX_230100.WWV_FLOW_PLUGIN", line 4140 ORA-06512: at "APEX_230100.WWV_FLOW_EXEC_WEB_SRC", line 2226 Backtrace: ORA-06512: at "APEX_230100.WWV_FLOW_EXEC_WEB_SRC", line 2322 ORA-06512: at "APEX_230100.WWV_FLOW_EXEC_WEB_SRC", line 2238 ORA-06512: at "APEX_230100.WWV_FLOW_ERROR", line 1075 ORA-06512: at "APEX_230100.WWV_FLOW_ERROR", line 1523 ORA-06512: at "APEX_230100.WWV_FLOW_PLUGIN", line 2193 ORA-06512: at "APEX_230100.WWV_FLOW_PLUGIN", line 4095 ORA-06512: at "APEX_230100.WWV_FLOW_PLUGIN", line 4140 ORA-06512: at "APEX_230100.WWV_FLOW_EXEC_WEB_SRC", line 2226 ORA-06512: at "APEX_230100.WWV_FLOW_EXEC_WEB_SRC", line 2602 ... get_component -> APEX_APPLICATION_PAGE_ITEMS P1_NEW Exception in "P1_NEW": Error Stack: ORA-20987: APEX - Error in PLSQL code raised during plug-in processing. - Contact your application administrator. Details about this incident are available via debug id "1537413071". ORA-06512: at "APEX_230100.WWV_FLOW_EXEC_WEB_SRC", line 2636 ORA-06512: at "APEX_230100.WWV_FLOW_EXEC_WEB_SRC", line 2322 ORA-06512: at "APEX_230100.WWV_FLOW_EXEC_WEB_SRC", line 2238 ORA-06512: at "APEX_230100.WWV_FLOW_ERROR", line 1075 ORA-06512: at "APEX_230100.WWV_FLOW_ERROR", line 1523 ORA-06512: at "APEX_230100.WWV_FLOW_PLUGIN", line 2193 ORA-06512: at "APEX_230100.WWV_FLOW_PLUGIN", line 4095 ORA-06512: at "APEX_230100.WWV_FLOW_PLUGIN", line 4140 ORA-06512: at "APEX_230100.WWV_FLOW_EXEC_WEB_SRC", line 2226 ORA-06512: at "APEX_230100.WWV_FLOW_EXEC_WEB_SRC", line 2602 ORA-06512: at "APEX_230100.WWV_FLOW_EXEC_WEB_SRC", line 2653 ORA-06512: at "APEX_230100.WWV_FLOW_EXEC", line 838 ORA-06512: at "APEX_230100.WWV_FLOW_EXEC", line 1144 ORA-06512: at "APEX_230100.WWV_FLOW_LOV", line 1745 Backtrace: ORA-06512: at "APEX_230100.WWV_FLOW_EXEC_WEB_SRC", line 2636 ORA-06512: at "APEX_230100.WWV_FLOW_EXEC_WEB_SRC", line 2322 ORA-06512: at "APEX_230100.WWV_FLOW_EXEC_WEB_SRC", line 2238 ORA-06512: at "APEX_230100.WWV_FLOW_ERROR", line 1075 ORA-06512: at "APEX_230100.WWV_FLOW_ERROR", line 1523 ORA-06512: at "APEX_230100.WWV_FLOW_PLUGIN", line 2193 ORA-06512: at "APEX_230100.WWV_FLOW_PLUGIN", line 4095 ORA-06512: at "APEX_230100.WWV_FLOW_PLUGIN", line 4140 ORA-06512: at "APEX_230100.WWV_FLOW_EXEC_WEB_SRC", line 2226 ORA-06512: at "APEX_230100.WWV_FLOW_EXEC_WEB_SRC", line 2602 ORA-06512: at "APEX_230100.WWV_FLOW_EXEC_WEB_SRC", line 2653 ORA-06512: at "APEX_230100.WWV_FLOW_EXEC", line 838 ORA-06512: at "APEX_230100.WWV_FLOW_EXEC", line 1144 ORA-06512: at "APEX_230100.WWV_FLOW_LOV", line 1745 ORA-06512: at "APEX_230100.WWV_FLOW_NATIVE_ITEM", line 3573 close_context context_type=>1 JSON for internal error already written stop_apex_engine Final commit do_commit
Solution

  • Thank you for reporting this. This is a bug in the filter code of the OData Plug-In. I will fix this as soon as possible, and provide an updated version of the app and the plug-in.

    If you don't want to wait for that, you can fix the issue in the PLG_ODATA_FILTERS PL/SQL package. Simply wrap the loop in line 95 into an IF block to check whether variable is NOT NULL.

    :
    -- Loop to replace the &items. with the sanitized values and return the String
    if l_items_sanitized is not null then
        for i in 1 .. l_items_sanitized.count loop
        :
        end loop; 
    end if;
:

    After that, only provide the actual filter (CodeType eq 135), and not the $filter URL parameter, which is constructed by the Plug-In.