ORA-01007: variable not in select list - IR_TO_XLSX line 1808

[hpysta]

Hi Pavel,

since deploying the newest version 3.13.3 of this plug-in the error message in the title appears sporadically, but after changing to a new APEX session the error disappears and the plug-in works well again, so I guess it is related to the session in some way but I don't see currently the reason for this since there are no global variables in your packages on the plug-in side. Can you please check if you can reproduce the error and possibly fix it?

Thank you and best regards, István

[glebovpavel]

Hi István,

could you please collect and send me a debug information like described in page 3 Instructions.pdf ?

Thank you,

Pavel

[hpysta]

Hi Pavel,

finally I could reproduce the error - since it happened sporadically, it was not easy to do. But despite setting the "Download Type (IR)" to "Debug TXT" the exception message appears before I could have downloaded the log.txt file:

ORA-20001: ORA-01007: variable not in select list ORA-06512: at "SYS.DBMS_SQL", line 1673 ORA-06512: at "IR_TO_XLSX", line 1808 ORA-06512: at "IR_TO_XLSX", line 2043 ORA-06512: at "IR_TO_XLSX", line 2176 ORA-06512: at "IR_TO_MSEXCEL", line 335

ORA-06512: at "IR_TO_MSEXCEL", line 355 ORA-06512: at line 4 ORA-06512: at "SYS.DBMS_SQL", line 1825 ORA-06512: at "APEX_050100.WWV_FLOW_DYNAMIC_EXEC", line 1880 ORA-06512: at "APEX_050100.WWV_FLOW_DYNAMIC_EXEC", line 1895 ORA-06512: at "APEX_050100.WWV_FLOW_DYNAMIC_EXEC", line 936 ORA-06512: at "APEX_050100.WWV_FLOW_PLUGIN", line 1330

Can you please check?

Thank you and best regards, István

[hpysta]

Hi Pavel,

can you please give me feedback about this issue?

Thank you and best regards, István

[glebovpavel]

Hi István,

very interesting, on my system 18.1 i can't reproduce this error, because the APEX behavior was changed and i never come into if-statement on IR_TO_XLSX", line 1808.

Could you please replace -- substantive strings in sql-queries can have bind variables too -- these variables are not in v_report.binds -- and need to be binded separately if not v_binded then
DBMS_SQL.BIND_VARIABLE (v_cur,v_bind_var_name,v(v_bind_var_name)); log('Bind variable ('||i||')'||v_bind_var_name||'<'||v(v_bind_var_name)||'>');
end if;

to the

-- substantive strings in sql-queries can have bind variables too -- these variables are not in v_report.binds -- and need to be binded separately if not v_binded then
begin DBMS_SQL.BIND_VARIABLE (v_cur,v_bind_var_name,v(v_bind_var_name)); exception when others then raise_application_error(-20001,'v_bind_var_name = '||v_bind_var_name); end;
end if;

on your system? That should get a little more information. Usually this "if" statement used in very special cases.

If you get a v_bind_var_name, could you please check you query - probably APEX incorrect recognize some text as bind variable?

Regards, Pavel

[hpysta]

Hi Pavel,

the exception line 1808 in the IR_TO_XLSX which throws exception is: dbms_sql.define_column(v_cur, i, v_char_dummy,32767); I surrounded it with the code: begin dbms_sql.define_column(v_cur, i, v_char_dummy,32767); exception when others then raise_application_error(-20001, 'col_type:'||to_char(l_report.desc_tab(i).col_type) ||',col_max_len:'||to_char(l_report.desc_tab(i).col_max_len) ||',col_name:'||l_report.desc_tab(i).col_name ||',col_name_len:'||to_char(l_report.desc_tab(i).col_name_len) ||',col_schema_name:'||l_report.desc_tab(i).col_schema_name ||',col_schema_name_len:'||to_char(l_report.desc_tab(i).col_schema_name_len) ||',col_precision:'||to_char(l_report.desc_tab(i).col_precision) ||',col_scale:'||to_char(l_report.desc_tab(i).col_scale) ||',col_charsetid:'||to_char(l_report.desc_tab(i).col_charsetid) ||',col_charsetform:'||to_char(l_report.desc_tab(i).col_charsetform) ||',col_null_ok:'||(CASE WHEN l_report.desc_tab(i).col_null_ok THEN 'YES' ELSE 'NO' END) ); end; end if; l_report.desc_tab is returned from dbms_sql.describe_columns2 And the column exists at the index where this ORA-01007 is thrown by dbms_sql.define_column In my opinion this must be a bug in dbms_sql.define_column since the column exists. I will investigate on the web if I find similar issues, and also will create a PLSQL example code to reproduce this exception.

Best regards, István

[glebovpavel]

@hpysta István, one question: do you have in your IR_TO_XLSX on line 1808 dbms_sql.define_column(v_cur, i, v_char_dummy,32767); or DBMS_SQL.BIND_VARIABLE (v_cur,v_bind_var_name,v(v_bind_var_name)); statement?

[hpysta]

Hi Pavel,

I have dbms_sql.define_column(v_cur, i, v_char_dummy,32767); in line 1808 in the IR_TO_XLSX package body.

I have checked the version 3.13.3 I have downloaded from here, and the same is in the file IR_TO_XLSX.pkb

Best regards, István

[glebovpavel]

Hi István, Which datatype has this column? Yo should get it using your raise_application_error statement.... On idea: could you try to replace v_char_dummy varchar2(1); into v_char_dummy varchar2(32767); ?

Regards, Pavel

[hpysta]

Hi Pavel,

I came to the conclusion that this is a bug in DBMS_SQL, but since it happens only occasionally I don't create a service request in Oracle Support. The workaround which has solved the issue is that I have put this line inside the for loop before any dbms_sql.define_column happens: dbms_sql.describe_columns2(v_cur, dummy_colls_count, dummy_desc_tab); After having this line in the code the exception does not happen anymore.

Best regards, István

[glebovpavel]

Hi István, thank you VERY much for such investigation. Could you please sent me a little more lines of code (or make pull request), because for me is still not really clear what did you change?

Regards, Pavel

[hpysta]

Hi Pavel,

thanks for merging the code :) Please note that I did not incorporate the change into the file "install_all_packages.sql".

Best regards, István

[glebovpavel]

Thank you too. I make all the required changes together with fixing a javascript - bug.

[glebovpavel]

Fixed in 3.15