Closed gierasm closed 3 years ago
Hi, We also had this error. The root cause was the way the cursor was built in IR_TO_XLSX.init_t_report procedure. Search that procedure for the part where the cursor is wrapped in: SELECT * FROM (cursor) where rownum <= :APXWS_MAX_ROW_CNT'
Once I removed the SELECT * I did not have the error anymore. So I replaced it with the below:
IF instr(l_report.report.sql_query,':APXWS_MAX_ROW_CNT') = 0 THEN
-- plug_in version: l_report.report.sql_query := 'SELECT * FROM ('|| l_report.report.sql_query ||') where rownum <= :APXWS_MAX_ROW_CNT';
--our change to fix the error ORA-01007: variable not in select list caused by the SELECT * first time we try the download
l_report.report.sql_query := CASE
WHEN INSTR(l_report.report.sql_query,'where 1=1')>0
THEN REPLACE(l_report.report.sql_query, 'where 1=1', 'where 1=1 and rownum <= :APXWS_MAX_ROW_CNT ' )
ELSE l_report.report.sql_query
END;
END IF;
I hope this also works for you.
Best regards, Violeta
Violeta, Thank you for the help. It did work.
Pavel, is there anyway we can get this fix in the next version of this plugin? I do not like changing plugin code because it makes upgrades more complicated.
Thanks, Susan
Fixed in 3.26
I have an on-click of a button that downloads an interactive report to excel using this version 3.25 of the plugin. The apex version that I am running is 19.1
The first time I click the button I get: ora_sqlerrm: ORA-20001: Version:3.25 ORA-01007: variable not in select list ORA-06512: at "SYS.DBMS_SQL", line 1596 <> ORA-06512: at "APPL_VIRUS.IR_TO_XLSX", line 1897 < dbms_sql.define_column(v_cur, i, v_number_dummy); > ORA-06512: at "APPL_VIRUS.IR_TO_XLSX", line 2115
The second time I click the button (after removing the error message) it downloads the report to excel without any problem.
Can you help me in fixing this issue?
Thanks for your help, Susan