hi @all,
On Oracle Database 23ai Free Release 23.0.0.0.0 - Develop, Learn, and Run for Free Version 23.9.0.25.07
the following merge statement works fine in SQL but crashes the session in PL/SQL
here the session the file is attached
SQL >@merge_crash_in_plsql.sql
SQL >--prepare dummy table
SQL >drop table t purge;
Table T dropped.
SQL >create table t as select object_id,object_name, object_type from user_objects;
Table T created.
SQL >alter table t add primary key (object_id);
Table T altered.
SQL >variable obj_id number;
SQL >begin
2 select object_id into :obj_id from t
3 fetch first 1 row only;
4 end;
5 /
PL/SQL procedure successfully completed.
SQL >select object_name,object_type from t where object_id = :obj_id;
OBJECT_NAME OBJECT_TYPE
______________ ______________
DEPT TABLE
1 row selected.
SQL >merge into t
2 on (t.object_id = :obj_id)
3 when matched then update set object_type = lower(object_type)
4 when not matched then insert (object_name, object_type, t.object_id) values ('X','Y',-1);
1 row merged.
SQL >select object_name,object_type from t where object_id = :obj_id;
OBJECT_NAME OBJECT_TYPE
______________ ______________
DEPT table
1 row selected.
SQL >rollback;
Rollback complete.
SQL >begin
2 merge into t
3 on (t.object_id = :obj_id)
4 when matched then update set object_type = lower(object_type)
5 when not matched then insert (object_name, object_type, t.object_id) values ('X','Y',-1);
6 end;
7 /
begin
*
ERROR at line 1:
ORA-03113: database connection closed by peer (connection_type=client, source=/127.0.0.1:54748, target=localhost:7521, protocol=TCP, service=freepdb1, detecting_end=client, server_type=unknown, client_program=SQLcl, server_process=17014, session=45.20639, user=MARKUS, last_rpc=Execute query, connection_id=/dem74nXQ+SCJ6/l2biOYg==, nne_encryption=null, nne_checksumming=null, authentication=O5LOGON)
https://docs.oracle.com/error-help/db/ora-03113/
More Details :
https://docs.oracle.com/error-help/db/ora-03113/
SQL >
merge_crash_in_plsql.sql
some suggestion, maybe just a bug
thanks Markus