ORA-01722: invalid number in IG

prosvet2 commented 4 years ago

APEX version: 19.2 Plugin settings: Popup report with or without columns configuration.

The plugin works fine on form items. However, there are problems when trying to use it on IG column.

When the IG region is alone on the page, the page does not load, with error log mentioning ORA-01722: invalid number somewhere inside APEX packages.
When IG is a detail one in master-detail page, once the row is accessed, I get the message: "Ajax call returned server error ORA-01722: invalid number". The popup is there, but the line data is not loaded.

I have added logging in APEX_ENHANCED_LOV_ITEM, but no output when the error happens. I have activated the debug in enhancedLovItem.js, but once again, impossible to catch the error. I have tried to create APEX_ENHANCED_LOV_ITEM package in my apex.oracle.com to be able to share the case, but got "ORA-01031: insufficient privileges ORA-06512: at "SYS.WWV_DBMS_SQL_APEX_190200", line 592 ORA-06512: at "SYS.DBMS_SYS_SQL", line 1658 ORA-06512: at "SYS.WWV_DBMS_SQL_APEX_190200", line 578 ORA-06512: at "APEX_190200.WWV_FLOW_DYNAMIC_EXEC", line 2057"

So, unfortunately I cannot reproduce the case on apex.oracle.com.

bostrowski commented 4 years ago

@prosvet2

as it states in roadmap the support for IG is not yet done. The possibility to use the plugin in an interactive grid was my mistake when setting configuration for the plugin.

good thing is that I'm finishing release v1.1.0 which supports IG. Stay tuned, I will be tweeting about it.

prosvet2 commented 4 years ago

Great! Thank you!

prosvet2 commented 4 years ago

The error still persists with the v1.1.0 :-( However the "Changes you made may not be saved" message is gone :-)

michele-pauluzzi commented 4 years ago

I finded out that invalid number error, usually, is related with the r column in the select not been a varchar2, if you add to_char(r) to the return value column, it will fix it.

prosvet2 commented 4 years ago

Unfortunately not. I have used to_char and also set the DB column type to VARCHAR2, but the error is still there.

bostrowski commented 4 years ago

@prosvet2 v1.1.0 is not yet released. I'm still preparing this release and I will announce the release at twitter.

bostrowski commented 4 years ago

@prosvet2, @michele-pauluzzi Could you please recreate your use case scenario at apex.oracle.com using emp / dept table? I would like to investigate it before releasing v1.1.0.

and send me login credentials (developer access) to ostrowski.bartosz@gmail.com

michele-pauluzzi commented 4 years ago

I'm sorry but i dont remember how to replicate the error. I checked in the old logs and find out the error

"- error_backtrace: ORA-06512: a "SYS.DBMS_SQL", line 1199
ORA-06512: a "A00.APEX_ENHANCED_LOV_ITEM", line 55
ORA-06512: a "A00.APEX_ENHANCED_LOV_ITEM", line 726
ORA-06512: a "A00.APEX_ENHANCED_LOV_ITEM", line 1089
ORA-06512: a line 4
ORA-06512: a "SYS.DBMS_SYS_SQL", line 1926
ORA-06512: a "SYS.WWV_DBMS_SQL_APEX_190100", line 599
ORA-06512: a "APEX_190100.WWV_FLOW_DYNAMIC_EXEC", line 2486
ORA-06512: a "APEX_190100.WWV_FLOW_DYNAMIC_EXEC", line 1500
ORA-06512: a "APEX_190100.WWV_FLOW_PLUGIN", line 1318
"


  function getBindedRefCursor(
    pi_sql in varchar2
  ) return sys_refcursor as
    v_apex_items_names    DBMS_SQL.VARCHAR2_TABLE;
    v_cursor              pls_integer;
    v_status              number;
  begin
    v_apex_items_names := WWV_FLOW_UTILITIES.GET_BINDS( pi_sql );

    -- open v_cursor;
    v_cursor := dbms_sql.open_cursor;

    dbms_sql.parse (v_cursor, pi_sql, dbms_sql.native); <-- error in this row

    -- bind items
    for i in 1..v_apex_items_names.count loop

      if v_apex_items_names(i) = ':SEARCH_STRING' then
        dbms_sql.bind_variable (v_cursor, v_apex_items_names(i), g_ajax_search_string );
      else
        dbms_sql.bind_variable (v_cursor, v_apex_items_names(i), v( trim(both ':' from v_apex_items_names(i)) ) );
      end if;

    end loop;

    v_status := dbms_sql.execute(v_cursor);

    return dbms_sql.to_refcursor(v_cursor);  
  end getBindedRefCursor;

The solution is to change the query from

select display d, return r from table

to

select display d, to_char(return) r from table

where return column is a number

bostrowski commented 4 years ago

casting id column is not a solution.

@prosvet2 provided me an example app which allowed me to find out I have invalid implementation of meta_data procedure.

@michele-pauluzzi I would like you to test fix I have done for @prosvet2. Please contact me via mail.

Pretius / Pretius-APEX-Enhanced-Lov-Item

ORA-01722: invalid number in IG #25