Oracle APEX 设置全局变量时出错

Question

APEX 4.1

使用交互式报表时，当我尝试将数据插入表（ID 为主键的分布表）时，出现以下错误

无法设置全局变量ORA-06502：PL/SQL：数字或值错误：字符到数字转换错误

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

数据库中有一个名为 GLOBALVARS 的包来设置变量。

包的变量为

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

会话变量设置为

GUSID – （这是 ID） （12371）GUSERID – DM_USERIDGUSER – 用户名格罗尔 – DM

我无法禁用此 GLOBALVARS，因为它被很多进程使用

你能告诉我如何克服这个错误吗

Answer 1

这应该告诉你问题出在哪里。 在"设置 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;