Dear Community,
I upgraded my database/apex from Oracle 11g XE/APEX5 to Oracle 21c XE/APEX 23.2 and everything worked fine except one problem I am not able to solve:
When I insert a new dataset in my database there is a trigger that just sends a new mail with the apex_mail.send function. That's working since years without a problem. Since the update I get an ORA-06502 error with exactly that apex_mail.send function when I inserted the dataset in APEX. When I insert the same dataset in my SQL Developer I get no error and the mail is sent (like all the years before).
There is nothing crazy going on in the PLSQL Trigger and I have no idea how to debug that. Do you have any suggestions?
I suppose there is something going on in the apex_mail.send function when the insert-statement is triggered from APEX that does not happen when triggered from SQL Developer.
I also tried to send a mail just by pressing an button that triggers a dynamic action (serverside-plsql code with the same code as in the trigger) in APEX and that's working
I also tried to send a mail from apex within a trigger on another table and I get the same error as above.
Trigger:
create or replace TRIGGER "T_ZS_ABWESENHEIT_T1"
before insert or update on "T_ZS_ABWESENHEIT"
for each row
declare
my_id number;
begin
my_id := APEX_MAIL.SEND (
p_to => 'test@test.at', --IN VARCHAR2,
p_from => 'test@test.com' , --IN VARCHAR2,
p_body => null, --IN [ VARCHAR2 | CLOB ],
p_body_html => 'mybody', --IN [ VARCHAR2 | CLOB ] DEFAULT NULL,
p_subj => 'mytest', --IN VARCHAR2 DEFAULT NULL,
p_cc => null, --IN VARCHAR2 DEFAULT NULL,
p_bcc => null, --IN VARCHAR2 DEFAULT NULL,
p_replyto => 'test@test.com' --IN VARCHAR2 )
);
end;
APEX debug log when executing the statement:
0.02600 0.00000
bind values for row 1.
4
0.02600 0.00400
executing DML statement for row 1.
4
0.03000 0.00000
Exception in "do_execute_dml, row 1":
Error Stack: ORA-06502: PL/SQL: numerischer oder Wertefehler: Form von assoziativem Array ist nicht mit Session-Parametern konsistent
ORA-06512: in "APEX_230200.WWV_FLOW_DYNAMIC_EXEC", Zeile 1326
ORA-06512: in "APEX_230200.WWV_FLOW_LANG", Zeile 1654
ORA-06512: in "APEX_230200.WWV_FLOW_MAIL", Zeile 547
ORA-06512: in "APEX_230200.WWV_FLOW_MAIL", Zeile 889
ORA-06512: in "APEX_230200.WWV_FLOW_MAIL_API", Zeile 154
ORA-06512: in "PDB.T_ZS_ABWESENHEIT_T1", Zeile 62
ORA-04088: Fehler bei der Ausführung von Trigger 'PDB.T_ZS_ABWESENHEIT_T1'
ORA-06512: in "SYS.DBMS_SYS_SQL", Zeile 2164
ORA-06512: in "SYS.WWV_DBMS_SQL_APEX_230200", Zeile 810
ORA-06512: in "APEX_230200.WWV_FLOW_DYNAMIC_EXEC", Zeile 1321
Backtrace: ORA-06512: in "APEX_230200.WWV_FLOW_DYNAMIC_EXEC", Zeile 1326
ORA-06512: in "APEX_230200.WWV_FLOW_LANG", Zeile 1654
ORA-06512: in "APEX_230200.WWV_FLOW_MAIL", Zeile 547
ORA-06512: in "APEX_230200.WWV_FLOW_MAIL", Zeile 889
ORA-06512: in "APEX_230200.WWV_FLOW_MAIL_API", Zeile 154
ORA-06512: in "PDB.T_ZS_ABWESENHEIT_T1", Zeile 62
ORA-06512: in "SYS.DBMS_SYS_SQL", Zeile 2164
ORA-06512: in "SYS.WWV_DBMS_SQL_APEX_230200", Zeile 810
ORA-06512: in "APEX_230200.WWV_FLOW_DYNAMIC_EXEC", Zeile 1321
ORA-06512: in "APEX_230200.WWV_FLOW_EXEC_LOCAL", Zeile 2884
2
0.03000 0.00000
DML returned SQL code: -6502
4
0.03000 0.00100
Add error onto error stack: ORA-06502: PL/SQL: numerischer oder Wertefehler: Form von assoziativem Array ist nicht mit Session-Parametern konsistent
2
0.03100 0.00000
Hiding error additional_info, as it contains ORA error message: ORA-06502: PL/SQL: numerischer oder Wertefehler: Form von assoziativem Array ist nicht mit Session-Parametern konsistent
1
0.03100 0.00000
Error: ORA-06502: PL/SQL: numerischer oder Wertefehler: Form von assoziativem Array ist nicht mit Session-Parametern konsistent
- Additional info: ORA-06502: PL/SQL: numerischer oder Wertefehler: Form von assoziativem Array ist nicht mit Session-Parametern konsistent
- Display location: INLINE_IN_NOTIFICATION
- Association type:
- Item name:
- Region id:
- Column alias:
- Row:
- Model instance id:
- Model record id:
- Internal error: false
- Common runtime error: false
- APEX error code:
- SQL code: -6502
- SQL error: ORA-06502: PL/SQL: numerischer oder Wertefehler: Form von assoziativem Array ist nicht mit Session-Parametern konsistent
- Backtrace:
- Statement: "T_ZS_ABWESENHEIT"
- Component: APEX_APPLICATION_PAGE_PROCESS Process Row of T_ZS_ABWESENHEIT (717306853705148346)
1
0.03100 0.00100
Exception in "Process Row of T_ZS_ABWESENHEIT":
Error Stack: ORA-06510: PL/SQL: Unbehandelte benutzerdefinierte Ausnahme
ORA-06512: in "APEX_230200.WWV_FLOW_PROCESS", Zeile 681
ORA-06512: in "APEX_230200.WWV_FLOW_REGION", Zeile 1111
Backtrace: ORA-06512: in "APEX_230200.WWV_FLOW_PROCESS", Zeile 681
ORA-06512: in "APEX_230200.WWV_FLOW_REGION", Zeile 1111
ORA-06512: in "APEX_230200.WWV_FLOW_FORM_REGION", Zeile 1278
2
0.03200 0.00000
Exception in "Process Row of T_ZS_ABWESENHEIT":
Error Stack: ORA-06510: PL/SQL: Unbehandelte benutzerdefinierte Ausnahme
ORA-06512: in "APEX_230200.WWV_FLOW_FORM_REGION", Zeile 1291
ORA-06512: in "APEX_230200.WWV_FLOW_PROCESS", Zeile 681
ORA-06512: in "APEX_230200.WWV_FLOW_REGION", Zeile 1111
ORA-06512: in "APEX_230200.WWV_FLOW_FORM_REGION", Zeile 1278
ORA-06512: in "APEX_230200.WWV_FLOW_FORM_REGION", Zeile 2362
ORA-06512: in "APEX_230200.WWV_FLOW_PROCESS_NATIVE", Zeile 1466
ORA-06512: in "APEX_230200.WWV_FLOW_PLUGIN", Zeile 3634
Backtrace: ORA-06512: in "APEX_230200.WWV_FLOW_FORM_REGION", Zeile 1291
ORA-06512: in "APEX_230200.WWV_FLOW_PROCESS", Zeile 681
ORA-06512: in "APEX_230200.WWV_FLOW_REGION", Zeile 1111
ORA-06512: in "APEX_230200.WWV_FLOW_FORM_REGION", Zeile 1278
ORA-06512: in "APEX_230200.WWV_FLOW_FORM_REGION", Zeile 2362
ORA-06512: in "APEX_230200.WWV_FLOW_PROCESS_NATIVE", Zeile 1466
ORA-06512: in "APEX_230200.WWV_FLOW_PLUGIN", Zeile 3634
ORA-06512: in "APEX_230200.WWV_FLOW_PROCESS", Zeile 192
2
0.03200 0.00000
< APEX_APPLICATION_PAGE_PROCESS Process Row of T_ZS_ABWESENHEIT
3
0.03200 0.00000
...Inline Errors detected, performing rollback
I also was looking for other tables that are sending mails and which are triggered from APEX and there are some that are working fine (trigger sends a mail using apex_mail.send without problem).
Thanks a lot for your help!
Jürgen