Problematic dynamic Merge statement with plsql and binds

View All Scripts Login to Run Script

Script Name Problematic dynamic Merge statement with plsql and binds
Description Script shows a problem with executing dynamic merge statements (my dba is filing a bug report). If that merge statement contains plsql in a with clause (a new feature for Oracle 12c) and (!!) it makes use of bind variables which get their values from a using-clause with the execute immediate, then your session will crash horribly. You will be disconnected from the database. Script will first show a merge that does work and then a version with the changes needed to reproduce this problem. NOTE: Fixed in 18c
Area PL/SQL General
Contributor Erik van Roon
Created Wednesday January 06, 2016

Statement 1

Drop the test table if it exists, and don't bother me with errors if it didn't exist in the first place

Silently drop the test table

declare
  no_table exception;
  pragma Exception_Init (no_table, -00942);
begin
  execute immediate 'drop table ero_tst';
exception
  when no_table
  then 
    null;
end;
/

Table dropped.

Statement 2

Create a test table and add data: 10 rows with ID numbered 1 through 10 and txt empty for now

Create table

create table ero_tst 
as 
select level                         id 
,      cast (null as varchar2(30))   txt 
from   dual 
connect by level <= 10

Table created.

Statement 3
Show me the contents of the table
List data
```
select *  
from   ero_tst 
order by id 
```

Statement 4

Execute a dynamic merge statement that has plsql in a with clause. In this form the execution will be succesfull. 5 Rows (with even- id's) will have updated txt column.

Merge statement without problems

declare 
  eol   constant  varchar2(1)  := chr(10); 
  stmnt    clob; 
begin 
  stmnt := 'merge  /*+ WITH_PLSQL */'                     ||eol|| 
           'into   ero_tst    tgt'                        ||eol|| 
           'using ('                                      ||eol|| 
           '       with'                                  ||eol|| 
           '         function update_text'                ||eol|| 
           '           return varchar2'                   ||eol|| 
           '           is'                                ||eol|| 
           '           begin'                             ||eol|| 
           '             return (''updated'');'           ||eol|| 
           '           end update_text;'                  ||eol|| 
           ''                                             ||eol|| 
           '       select level          upd_id'          ||eol|| 
           '       ,      update_text    update_text'     ||eol|| 
           '       from   dual'                           ||eol|| 
           '       where mod(level,2) = 0'                ||eol|| 
           '       connect by level <= 10'                ||eol|| 
           '      )    src'                               ||eol|| 
           'on    (tgt.id = src.upd_id'                   ||eol|| 
           '      )'                                      ||eol|| 
           'when matched'                                 ||eol|| 
           'then'                                         ||eol|| 
           '  update'                                     ||eol|| 
           '  set    tgt.txt     = src.update_text' 
           ; 
 
  execute immediate stmnt 
  ; 
   
  dbms_output.put_line ('Numer of records merged: '||to_char(sql%rowcount,'fm999g999g999')); 
  
  commit;
end;

Numer of records merged: 5

Statement 5

Show me that the merge did what is is supposed to do

List data

select *  
from   ero_tst 
order by id

 
ID TXT
1  - 
2 updated
3  - 
4 updated
5  - 
6 updated
7  - 
8 updated
9  - 
10 updated

10 rows selected.

ID	TXT
1	-
2	updated
3	-
4	updated
5	-
6	updated
7	-
8	updated
9	-
10	updated

Statement 6

This merge has the problem parts commented out, so you can run this script without problems, but now it is identical to the previous one. If you comment the current "where" line and uncomment the currently commented one + uncomment the "using 2" line, running this merge on Live SQL will present a fatal server error page to you. Running it in your own sqlplus or sqlcl will crash your session with messages: "ORA-03114: not connected to ORACLE" and "ORA-03113: end-of-file on communication channel". This happens only if all 4 conditions are met: 1. dynamic merge statement (no problems with insert, update, delete, select) 2. The merge has plsql in the with clause of its using-clause (whether the plsql is actually used or not is irrelevant). 3. The merge has bind variables 4. The execute immediate has a using clause to provide values for the binds.

Problematic merge

declare 
  eol   constant  varchar2(1)  := chr(10); 
  stmnt    clob; 
begin 
  -- to make this a problem merge statement activate the where-line with the bind variable
  -- (and obviously deactivate the where-line without), and activate the using-clause at the
  -- execute immediate
  -- ORACLE WILL PROBABLY NOT BE HAPPY IF YOU THIS ON LIVESQL.
  -- IF YOU DO THIS AT WORK, YOUR DBA MAY CALL AND ASK WHY YOU'RE CAUSING 
  -- ACCESS VIOLATION MESSAGES IN THE ALERT LOG
  stmnt := 'merge  /*+ WITH_PLSQL */'                     ||eol|| 
           'into   ero_tst    tgt'                        ||eol|| 
           'using ('                                      ||eol|| 
           '       with'                                  ||eol|| 
           '         function update_text'                ||eol|| 
           '           return varchar2'                   ||eol|| 
           '           is'                                ||eol|| 
           '           begin'                             ||eol|| 
           '             return (''updated'');'           ||eol|| 
           '           end update_text;'                  ||eol|| 
           ''                                             ||eol|| 
           '       select level          upd_id'          ||eol|| 
           '       ,      update_text    update_text'     ||eol|| 
           '       from   dual'                           ||eol|| 
--           '       where mod(level,:A) = 0'               ||eol||  -- replace next line with this line and uncomment 
           '       where mod(level,2) = 0'               ||eol|| 
           '       connect by level <= 10'                ||eol|| 
           '      )    src'                               ||eol|| 
           'on    (tgt.id = src.upd_id'                   ||eol|| 
           '      )'                                      ||eol|| 
           'when matched'                                 ||eol|| 
           'then'                                         ||eol|| 
           '  update'                                     ||eol|| 
           '  set    tgt.txt     = src.update_text' 
           ; 
 
  execute immediate stmnt 
  -- using 2                           --uncomment 
  ; 
   
  dbms_output.put_line ('Numer of records merged: '||to_char(sql%rowcount,'fm999g999g999')); 
   
end;

Numer of records merged: 5

Statement 7

Show me What the merge did. However, if you made the proposed changes to the previous merge, you will not ever get here.

List data

select *  
from   ero_tst 
order by id

 
ID TXT
1  - 
2 updated
3  - 
4 updated
5  - 
6 updated
7  - 
8 updated
9  - 
10 updated

10 rows selected.

ID	TXT
1	-
2	updated
3	-
4	updated
5	-
6	updated
7	-
8	updated
9	-
10	updated

Additional Information

Database on OTN SQL and PL/SQL Discussion forums
Oracle Database
Download Oracle Database