Oracle APEX Error with setting GlobalVariables

Question

APEX 4.1

Using an Interactive report, when I try to insert data into a table (Distribution table with ID as primary key), I get the following error

Could not set GLOBALVARS ORA-06502: PL/SQL: numeric or value error: character to number conversion error

I debugged the application and saved a log of the activity. I highlighted the error in RED.
0.07397 0.00126 Session State: Save form items and p_arg_values 4 
0.07523 0.00109 ...Session State: Save "P47_SMAPPROVER" - saving same value: "" 4 
0.07632 0.00094 ...Session State: Save "P47_PROTOCOLNUMBER" - saving same value: "CQAB149B9999" 4 
0.07726 0.00092 ...Session State: Save "P47_ID" - saving same value: "12746" 4 
0.07818 0.00976 ...Session State: Save "P47_CTLAPPDATE" - saving same value: "" 4 
0.08794 0.00099 ...Session State: Save "P47_ORIGIN" - saving same value: "ICPR" 4 
0.08892 0.01919 ...Session State: Save "P47_ORIGIN_T" - saving same value: "" 4 
0.10811 0.00080 Processes - point: ON_SUBMIT_BEFORE_COMPUTATION 4 
0.10891 0.00138 ...Process "Set GLOBALVARS" - Type: PLSQL 4 
0.11030 0.00239 ...Execute Statement: begin begin GLOBALVARS.GUSID := :GUSID; GLOBALVARS.GUSER := :GUSER; GLOBALVARS.GROLE := :GROLE; GLOBALVARS.GUSERID := :USERID; end; end; 4 
0.11269 0.00104 Add error onto error stack 4 
0.11373 0.00095 ...Error data: 4 
0.11468 0.00093 ......message: Could not set GLOBALVARS; 4 
0.11561 0.00093 ......additional_info: ORA-06502: PL/SQL: numeric or value error: character to number conversion error 4 
0.11654 0.00096 ......display_location: ON_ERROR_PAGE 4 
0.11750 0.00099 ......is_internal_error: false 4 
0.11849 0.00093 ......ora_sqlcode: -6502 4 
0.11942 0.00094 ......ora_sqlerrm: ORA-06502: PL/SQL: numeric or value error: character to number conversion error 4 
0.12036 0.00095 ......error_backtrace: ORA-06512: at "SYS.WWV_DBMS_SQL", line 904 ORA-06512: at "APEX_040100.WWV_FLOW_DYNAMIC_EXEC", line 618 ORA-06512: at "APEX_040100.WWV_FLOW_PROCESS", line 128 4 
0.12131 0.00096 ......component.type: APEX_APPLICATION_PROCESSES 4 
0.12227 0.00111 ......component.id: 1113626474420504307 4 
0.12338 0.00124 ......component.name: Set GLOBALVARS 4 
0.12461 0.00099 ...Show Error on Error Page 4 
0.12560 0.00528 ......Performing rollback 4 
0.13088 0.00215 Processes - point: AFTER_ERROR_HEADER 4 
0.13303 0.00226 Processes - point: BEFORE_ERROR_FOOTER 4 
0.13529 - End Page Processing

There is a package in Database called GLOBALVARS that sets the variables.

The variables of the package are

GUSID     number;
GUSERID   varchar2(20);
GUSER     varchar2(60);
GROLE     varchar2(30);

The session variables are set as

GUSID – (This is the id) (12371) GUSERID – DM_USERID GUSER – USERNAME GROLE – DM

I cannot disable this GLOBALVARS as it is used by a lot of process

Can you please advise how I can get through this error

Seeing as GUSID is the only NUMBER, are you certain that value being put in there is numeric. Is :GUSID a page or application variable? Try to place this in your debug log (apex_debug.message) to make sure, or take a look at the session info before submitting. If it is a number, try to do an explicit TO_NUMBER before setting it. — Tom
Thanks for the response. I checked session variables to see the value sent to GUSID. GUSID is getting a value &12371. Its the same in other environments (test and production). If I disable, comment the GUSID, I dont see the error, but I can't disable it as it is referenced in a lot of places — user1940212
I will re-check the session variables to ensure whether the colon ; is in there, but the value passed is '&amp along with id, i.e. 12371'. — user1940212
show where you provide a value to GUSID. If it is an item, mention the source or default, or code where you set it. If &amp. is in the value, then obviously trying to put this in a number variable will fail and give the error. — Tom

Olafur Tryggvason Olafur Tryggvason · Accepted Answer · 2014-11-10T22:58:56

This should tell you where the problem is. Run this in the process "set GlobalVars".

begin
    begin
        globalvars.gusid := to_number (:gusid);
    exception
        when others then
            raise_application_error (-20001, ':gusid is not a number');
    end;

    if :guser != substr (:guser, 1, 60) then
        raise_application_error (-20002, ':guser is > 60 chars');
    end if;

    globalvars.guser := :guser;

    if :grole != substr (:grole, 1, 30) then
        raise_application_error (-20003, ':grole is > 30 chars');
    end if;

    globalvars.grole := :grole;

    if :userid != substr (:userid, 1, 20) then
        raise_application_error (-20004, ':userid is > 20 chars');
    end if;

    globalvars.guserid := :userid;
end;

Oracle APEX Error with setting GlobalVariables

1 Answers