Highlight condition causes exception when exporting IR results to XLSX

[hpysta]

Hi Pavel,

first of all: this is a great APEX plug-in, thanks for creating it.

I have discovered an issue. In case having a highlight condition in Actions / Format / Highlight then the XLSX export causes exception.

ORA-20001: get_current_row: string: p_id=67 ORA-01403: no data found ORA-06512: at "IR_TO_XLSX", line 1334 ORA-06512: at "IR_TO_XLSX", line 1355 ORA-06512: at "IR_TO_XLSX", line 1858 ORA-06512: at "IR_TO_XLSX", line 2043 ORA-06512: at "IR_TO_XLSX", line 2228 ORA-06512: at "IR_TO_MSEXCEL", line 322

Workaround: if this highlight condition pseudo-column is added to the output columns of the report then this exception does not occur.

Can you please investigate and fix the issue?

Thank you and best regards, István

[glebovpavel]

The same reason as #38 Fixed in 3.13

[hpysta]

Hi Pavel,

I have installed version 3.13 and this problem still exists resulting in the same error message. Can you please check it again and fix it?

Thank you and best regards, István

[glebovpavel]

Hi István,

could you please try to download and install https://github.com/glebovpavel/IR_to_MSExcel/releases/tag/v.3.13.1 again? I forgot to renew plugin itself....

Regards, Pavel

[hpysta]

Hi Pavel,

when installing the 3.13.1 plugin I get the error message "1 error has occurred NOT COMPATIBLE (Your export may contain calls not supported by your application version.)". The previous version 3.13 of the plugin could be imported without any error. Can you please check?

Thank you and best regards, István

[glebovpavel]

Hi István,

now should be compatible with 5.1

Regards, Pavel

[hpysta]

Hi Pavel,

thank you, now I was able to install the plug-in. However, the issue is still not resolved, I get the error messages below when I want to export the data into Excel in case the highlight column filter column is not shown on the report (when it's shown on the report then the export works):

original_additional_info: ORA-20001: ORA-20001: get_current_row: string: p_id=67 ORA-01403: no data found ORA-06512: at "APP_FOLIO_D.FLO_APEX_UTL_IR_TO_XLSX", line 1334 ORA-06512: at "APP_FOLIO_D.FLO_APEX_UTL_IR_TO_XLSX", line 1355 ORA-06512: at "APP_FOLIO_D.FLO_APEX_UTL_IR_TO_XLSX", line 1858 ORA-06512: at "APP_FOLIO_D.FLO_APEX_UTL_IR_TO_XLSX", line 2043 ORA-06512: at "APP_FOLIO_D.FLO_APEX_UTL_IR_TO_XLSX", line 2228 ORA-06512: at "APP_FOLIO_D.FLO_APEX_UTL_IR_TO_MSEXCEL", line 325

Can you please check the issue again?

Thank you and best regards, István

[glebovpavel]

Hi István,

Unfortunately i can't reproduce this error. Could you try to replace this error on https://apex.oracle.com/pls/apex/f?p=150293 and save this report as default?

Regards, Pavel

[hpysta]

Hi Pavel,

first of all, after analyzing the issue in my application I saw that it is more complex: the highlight condition is based on a computed column which is hidden. BUT using the application on your URL given in your last comment, the xls export went without problems even when the computation column was hidden. I will investigate further on my side what is the cause of not working properly.

Best regards, István

[hpysta]

Hi Pavel,

I have debugged on my side and I have found the cause:

In IR_TO_XLSX.init_t_report the function apex_ir.get_report gets the sql_query for the report without the computation column in case it is hidden. So it is a standard APEX API which works like this.

However I am wondering why it works on the test instance you showed me. My APEX version is 5.1.3.00.05, can you check what is the version on your side? Might be that Oracle has changed the API in a newer version to include the hidden computational columns in the sql_query too.

Thank you and best regards, István

[glebovpavel]

Hi István,

Today i can test only at 5.1.4.00.08, it works without PL/SQL errors, i got Excel-file, but highlights based on hidden calculated column are not shown. This hidden column don't present in SQL-Query.

Could you pleas additionally check that you use the last version of PL/SQL packages?

Regards, Pavel

[hpysta]

Hi Pavel,

yes, I had the latest version, but I also have redeployed the packages of the plug-in in the DB, and I still receive the errors.

I am curious about your findings: if a computation column is set to be hidden then I would like to know if the standard API apex_ir.get_report gets the sql with or without this computation column in your APEX version 5.4?

During debug I have logged the value for sql_query into a separate table, after in IR_TO_XLSX.init_t_report the function apex_ir.get_report has get it. The value needs a column with datatype long to be stored.

Best regards, István

[glebovpavel]

Hi @hpysta István,

During investigations i found that version 3.13 was assembled with errors. I put the last version into master https://github.com/glebovpavel/IR_to_MSExcel/archive/master.zip and add a message on a GitHUB page. The changes should not affect computations and hidden columns, but APEX itself is very complex and you can try new version if you have a time.

Regards, Pavel

[hpysta]

Hi Pavel,

thanks for the new version. As soon as we will change to a higher version of APEX I will give feedback about my findings regarding your plug-in.

Best regards, István

[stephhend]

Hi,

I installed the latest version https://github.com/glebovpavel/IR_to_MSExcel/archive/master.zip but I am still receiving the following error.

Can you help?

Error in PLSQL code raised during plug-in processing.

Contact your application administrator. Technical Info (only visible for developers)

is_internal_error: true
apex_error_code: WWV_FLOW_PLUGIN.RUN_PLSQL_ERR
ora_sqlcode: -20001
ora_sqlerrm: ORA-20001: ORA-06502: PL/SQL: numeric or value error: character to number conversion error ORA-06512: at "APEX_MA.IR_TO_XLSX", line 1270 ORA-06512: at "APEX_MA.IR_TO_XLSX", line 1289 ORA-06512: at "APEX_MA.IR_TO_XLSX", line 1762 ORA-06512: at "APEX_MA.IR_TO_XLSX", line 1929 ORA-06512: at "APEX_MA.IR_TO_XLSX", line 2099 ORA-06512: at "APEX_MA.IR_TO_MSEXCEL", line 306
component.type: APEX_APPLICATION_PAGE_DA_ACTS
component.id: 1717688679702666513
component.name: PLUGIN_AT.FRT.GPV_IR_TO_MSEXCEL
error_backtrace:

ORA-06512: at "APEX_MA.IR_TO_MSEXCEL", line 336
ORA-06512: at line 4
ORA-06512: at "SYS.DBMS_SYS_SQL", line 2107
ORA-06512: at "SYS.WWV_DBMS_SQL_APEX_190100", line 599
ORA-06512: at "APEX_190100.WWV_FLOW_DYNAMIC_EXEC", line 2486
ORA-06512: at "APEX_190100.WWV_FLOW_DYNAMIC_EXEC", line 1500
ORA-06512: at "APEX_190100.WWV_FLOW_PLUGIN", line 1383

error_statement:

begin declare

begin
wwv_flow_plugin_api.g_dynamic_action_ajax_result := IR_TO_MSEXCEL.ajax (p_dynamic_action => wwv_flow_plugin_api.g_dynamic_action,p_plugin => wwv_flow_plugin_api.g_plugin );end;
end;

Thanks!

Stephanie

[huy91-hub]

@stephhend : Have you to find out a way to solve that problem. At first time, I had a same problem, but I followed instruction of glebovpave about to Grant acess, it was done successfully.