2023年7月13日 21:07:48go评论64阅读模式

英文:

Apex 22.1 How to pass filters to OData Rest Service

问题

很抱歉，似乎有一些PL/SQL代码中的问题导致了错误。根据错误信息，有一些未初始化的集合的引用。你可能需要仔细检查你的PL/SQL代码，确保所有的集合都已正确初始化，并且过滤器参数正确传递给OData服务。

此外，你还可以查看APEX文档和OData插件的文档，以确保你正确配置了OData插件和相关的数据源。这可能需要更深入的调试和配置来解决问题。

请注意，由于错误信息中提到了APEX版本，可能与APEX的特定版本和配置有关，所以检查文档和社区支持也是一个好主意。

英文:

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

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.

Imported OData plugin from
https://github.com/oracle/apex/tree/22.2/plugins/rest-source/odata-rest-apis
Added https://services.odata.org/TripPinRESTierService/People as
REST Data Source
Added an LOV without filters against this source and all people were
shown in a popup list
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

答案1

得分: 0

谢谢您报告这个问题。这是OData插件的过滤器代码中的一个错误。我会尽快修复它，并提供应用程序和插件的更新版本。

如果您不想等待，您可以在PLG_ODATA_FILTERS PL/SQL包中修复此问题。只需将第95行的循环包装在IF块中，以检查变量是否不为NULL。

之后，_仅_提供实际的过滤器 (CodeType eq 135)，而不是由插件构建的$filter URL参数。

英文:

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 &amp;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.

通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。

如何将筛选器传递给OData Rest服务的Apex 22.1版本。

问题

答案1

在Oracle APEX中，将按钮添加到一个数据库表列的方法如下：

Calculation in one field 计算在一个领域中

使用JavaScript（DA）更改Oracle APEX交互式网格的行背景颜色。

如何在按下一个按钮后先更新，然后发送电子邮件？

What's the correct way to type hint an empty list as a literal in python?

如何在Highcharts Gantt中更改本地化的星期名称

如何在同一个流中使用多个过滤器和映射函数？

如何使用Map/Set来将代码优化到O(n)？

.NET MAUI Android在GitHub Actions上构建失败，错误代码为1。

如何在Playwright视觉比较中屏蔽多个定位器？

在C++中，可以使用可变模板参数来检索类型的内部类型。

selenium.common.exceptions.StaleElementReferenceException: Message: stale element reference: stale element not found

Creating and opening a URL to log in to Website via Basic Auth with Robot Framework/Selenium (Python)

AG Grid 在上下文菜单中以大文本形式打开

发表评论