{"id":13198,"date":"2024-01-30T01:17:39","date_gmt":"2024-01-30T00:17:39","guid":{"rendered":"https:\/\/www.salvis.com\/blog\/?p=13198"},"modified":"2024-06-17T11:06:22","modified_gmt":"2024-06-17T09:06:22","slug":"islandsql-episode-6-dml-statements-in-oracle-database-23c","status":"publish","type":"post","link":"https:\/\/www.salvis.com\/blog\/2024\/01\/30\/islandsql-episode-6-dml-statements-in-oracle-database-23c\/","title":{"rendered":"IslandSQL Episode 6: DML Statements in Oracle Database 23c"},"content":{"rendered":"\n

Introduction<\/h2>\n\n\n\n

The IslandSQL<\/a> grammar now covers all DML statements. This means call<\/code>, delete<\/code>, explain plan<\/code>, insert<\/code>, lock table<\/code>, merge<\/code>, select<\/code> and update<\/code>.<\/p>\n\n\n\n

In this episode, we will focus on new features in the Oracle Database 23c that can be used in insert<\/a><\/code>, update<\/a><\/code>, delete<\/a><\/code> and merge<\/a><\/code> statements. For the select<\/a><\/code> statement see the last episode<\/a>.<\/p>\n\n\n\n

Table Value Constructor<\/h2>\n\n\n\n

The new table value constructor allows you to create rows on the fly. This simplifies statements. Furthermore, it allows you to write a single statement instead of a series of statements, which makes the execution in scripts faster. It can be used in the select<\/a><\/code>, insert<\/a><\/code> and merge<\/a><\/code> statement.<\/p>\n\n\n\n

Insert<\/h5>\n\n\n\n
1) Insert with table value constructor<\/span><\/path><\/path><\/svg><\/span>
drop<\/span> <\/span>table<\/span> <\/span>if<\/span> <\/span>exists<\/span> d;<\/span><\/span>\ncreate<\/span> <\/span>table<\/span> d (deptno <\/span>number<\/span>(<\/span>2<\/span>,<\/span>0<\/span>), dname <\/span>varchar2<\/span>(<\/span>14<\/span>), loc <\/span>varchar2<\/span>(<\/span>13<\/span>));<\/span><\/span>\ninsert<\/span> <\/span>into<\/span> d (deptno, dname, loc)<\/span><\/span>\nvalues<\/span> (<\/span>10<\/span>, <\/span>'ACCOUNTING'<\/span>, <\/span>'NEW YORK'<\/span>),<\/span><\/span>\n       (<\/span>20<\/span>, <\/span>'RESEARCH'<\/span>,   <\/span>'DALLAS'<\/span>),<\/span><\/span>\n       (<\/span>30<\/span>, <\/span>'SALES'<\/span>,      <\/span>'CHICAGO'<\/span>),<\/span><\/span>\n       (<\/span>40<\/span>, <\/span>'OPERATIONS'<\/span>, <\/span>'BOSTON'<\/span>);<\/span><\/span><\/code><\/pre><\/div>\n\n\n\n
<\/path><\/path><\/svg><\/span>
Table D dropped.<\/span><\/span>\n<\/span><\/span>\nTable D created.<\/span><\/span>\n<\/span><\/span>\n4 rows inserted.<\/span><\/span><\/code><\/pre><\/div>\n\n\n\n
Merge<\/h5>\n\n\n\n
2) Merge with table value constructor<\/span><\/path><\/path><\/svg><\/span>
merge<\/span> <\/span>into<\/span> d t<\/span><\/span>\nusing<\/span> (<\/span>values<\/span> <\/span><\/span>\n         (<\/span>10<\/span>, <\/span>'ACCOUNTING'<\/span>, <\/span>'NEW YORK'<\/span>),<\/span><\/span>\n         (<\/span>20<\/span>, <\/span>'RESEARCH'<\/span>,   <\/span>'DALLAS'<\/span>),<\/span><\/span>\n         (<\/span>30<\/span>, <\/span>'SALES'<\/span>,      <\/span>'CHICAGO'<\/span>),<\/span><\/span>\n         (<\/span>40<\/span>, <\/span>'OPERATIONS'<\/span>, <\/span>'BOSTON'<\/span>)<\/span><\/span>\n      ) s (deptno, dname, loc)<\/span><\/span>\n   <\/span>on<\/span> (t.deptno = s.deptno)<\/span><\/span>\n <\/span>when<\/span> <\/span>matched<\/span> <\/span>then<\/span><\/span>\n      <\/span>update<\/span><\/span>\n         <\/span>set<\/span> t.dname = s.dname,<\/span><\/span>\n             t.loc = s.loc<\/span><\/span>\n <\/span>when<\/span> <\/span>not<\/span> <\/span>matched<\/span> <\/span>then<\/span><\/span>\n      <\/span>insert<\/span> (t.deptno, t.dname, t.loc)<\/span><\/span>\n      <\/span>values<\/span> (s.deptno, s.dname, s.loc);<\/span><\/span><\/code><\/pre><\/div>\n\n\n\n
<\/path><\/path><\/svg><\/span>
4 rows merged.<\/span><\/span><\/code><\/pre><\/div>\n\n\n\n
Direct Joins for UPDATE and DELETE Statements<\/h2>\n\n\n\n
The new from_using_clause<\/code> can be used in delete<\/a><\/code> and update<\/a><\/code> statements. <\/p>\n\n\n\n
\"from_using_clause<\/a><\/figure>\n\n\n\n
With this new clause, you can avoid a self-join and, as a result, the optimizer can produce a more efficient execution plan.<\/p>\n\n\n\n
Delete<\/h5>\n\n\n\n
The next example is based on the HR schema. We delete all countries that are not used by any department. See line 3 for the from_using_clause<\/code>. The join conditions and the filter criteria are part of the where_clause<\/code>. <\/p>\n\n\n\n
You cannot define the join condition for the table in the from_clause<\/code> in the from_using_clause<\/code>. This is a documented limitation. Furthermore, we cannot mix ANSI-92 join syntax with Oracle-style outer join syntax (see ORA-25156<\/a>). As a result, we have to use the Oracle-style join syntax for all tables.<\/p>\n\n\n\n
3a) Delete with from_using_clause<\/span><\/path><\/path><\/svg><\/span>
delete<\/span> <\/span><\/span>\n  <\/span>from<\/span> countries <\/span>c<\/span> <\/span><\/span>\n  <\/span>from<\/span> locations l, departments d<\/span><\/span>\n <\/span>where<\/span> l.country_id (+) = <\/span>c<\/span>.country_id<\/span><\/span>\n   <\/span>and<\/span> d.location_id (+) = l.location_id<\/span><\/span>\n   <\/span>and<\/span> l.location_id <\/span>is null<\/span><\/span>\n   <\/span>and<\/span> d.department_id <\/span>is null<\/span>; <\/span><\/span><\/code><\/pre><\/div>\n\n\n\n
<\/path><\/path><\/svg><\/span>
11 rows deleted.<\/span><\/span>\n<\/span><\/span>\n--------------------------------------------------<\/span><\/span>\n| Id  | Operation              | Name            |<\/span><\/span>\n--------------------------------------------------<\/span><\/span>\n|   0 | DELETE STATEMENT       |                 |<\/span><\/span>\n|   1 |  DELETE                | COUNTRIES       |<\/span><\/span>\n|   2 |   FILTER               |                 |<\/span><\/span>\n|   3 |    HASH JOIN OUTER     |                 |<\/span><\/span>\n|   4 |     FILTER             |                 |<\/span><\/span>\n|   5 |      HASH JOIN OUTER   |                 |<\/span><\/span>\n|   6 |       INDEX FULL SCAN  | COUNTRY_C_ID_PK |<\/span><\/span>\n|   7 |       TABLE ACCESS FULL| LOCATIONS       |<\/span><\/span>\n|   8 |     TABLE ACCESS FULL  | DEPARTMENTS     |<\/span><\/span>\n--------------------------------------------------<\/span><\/span><\/code><\/pre><\/div>\n\n\n\n
Having two from<\/code> keywords in the delete<\/code> statement is funny, but it does not make the statement easier to read. I therefore recommend rewriting the statement like this:<\/p>\n\n\n\n
3b) Delete with from_using_clause (simplified & clearer)<\/span><\/path><\/path><\/svg><\/span>
delete<\/span> countries <\/span>c<\/span> <\/span><\/span>\n <\/span>using<\/span> locations l, departments d<\/span><\/span>\n <\/span>where<\/span> l.country_id (+) = <\/span>c<\/span>.country_id<\/span><\/span>\n   <\/span>and<\/span> d.location_id (+) = l.location_id<\/span><\/span>\n   <\/span>and<\/span> l.location_id <\/span>is null<\/span><\/span>\n   <\/span>and<\/span> d.department_id <\/span>is null<\/span>;<\/span><\/span><\/code><\/pre><\/div>\n\n\n\n
<\/path><\/path><\/svg><\/span>
11 rows deleted.<\/span><\/span>\n<\/span><\/span>\n--------------------------------------------------<\/span><\/span>\n| Id  | Operation              | Name            |<\/span><\/span>\n--------------------------------------------------<\/span><\/span>\n|   0 | DELETE STATEMENT       |                 |<\/span><\/span>\n|   1 |  DELETE                | COUNTRIES       |<\/span><\/span>\n|   2 |   FILTER               |                 |<\/span><\/span>\n|   3 |    HASH JOIN OUTER     |                 |<\/span><\/span>\n|   4 |     FILTER             |                 |<\/span><\/span>\n|   5 |      HASH JOIN OUTER   |                 |<\/span><\/span>\n|   6 |       INDEX FULL SCAN  | COUNTRY_C_ID_PK |<\/span><\/span>\n|   7 |       TABLE ACCESS FULL| LOCATIONS       |<\/span><\/span>\n|   8 |     TABLE ACCESS FULL  | DEPARTMENTS     |<\/span><\/span>\n--------------------------------------------------<\/span><\/span><\/code><\/pre><\/div>\n\n\n\n
Here’s an alternative, pre-23c-style delete<\/code> statement without the from_using_clause<\/code>. It is accessing the countries<\/code> table twice, which might lead to a less efficient execution plan.<\/p>\n\n\n\n
3c) Delete with subquery filter<\/span><\/path><\/path><\/svg><\/span>
delete<\/span> <\/span><\/span>\n  <\/span>from<\/span> countries c1<\/span><\/span>\n <\/span>where<\/span> c1.country_id <\/span>in<\/span> (<\/span><\/span>\n          <\/span>select<\/span> c2.country_id<\/span><\/span>\n            <\/span>from<\/span> countries c2<\/span><\/span>\n            <\/span>left join<\/span> locations l<\/span><\/span>\n              <\/span>on<\/span> l.country_id = c2.country_id<\/span><\/span>\n            <\/span>left join<\/span> departments d<\/span><\/span>\n              <\/span>on<\/span> d.location_id = l.location_id<\/span><\/span>\n           <\/span>where<\/span> l.location_id <\/span>is null<\/span><\/span>\n             <\/span>and<\/span> d.department_id <\/span>is null<\/span><\/span>\n       );<\/span><\/span><\/code><\/pre><\/div>\n\n\n\n
<\/path><\/path><\/svg><\/span>
11 rows deleted.<\/span><\/span>\n<\/span><\/span>\n----------------------------------------------------------------------<\/span><\/span>\n| Id  | Operation                                 | Name             |<\/span><\/span>\n----------------------------------------------------------------------<\/span><\/span>\n|   0 | DELETE STATEMENT                          |                  |<\/span><\/span>\n|   1 |  DELETE                                   | COUNTRIES        |<\/span><\/span>\n|   2 |   INDEX FULL SCAN                         | COUNTRY_C_ID_PK  |<\/span><\/span>\n|   3 |    FILTER                                 |                  |<\/span><\/span>\n|   4 |     NESTED LOOPS OUTER                    |                  |<\/span><\/span>\n|   5 |      FILTER                               |                  |<\/span><\/span>\n|   6 |       NESTED LOOPS OUTER                  |                  |<\/span><\/span>\n|   7 |        INDEX UNIQUE SCAN                  | COUNTRY_C_ID_PK  |<\/span><\/span>\n|   8 |        TABLE ACCESS BY INDEX ROWID BATCHED| LOCATIONS        |<\/span><\/span>\n|   9 |         INDEX RANGE SCAN                  | LOC_COUNTRY_IX   |<\/span><\/span>\n|  10 |      TABLE ACCESS BY INDEX ROWID BATCHED  | DEPARTMENTS      |<\/span><\/span>\n|  11 |       INDEX RANGE SCAN                    | DEPT_LOCATION_IX |<\/span><\/span>\n----------------------------------------------------------------------<\/span><\/span><\/code><\/pre><\/div>\n\n\n\n
Update<\/h5>\n\n\n\n
In this example, we increase the salaries of all employees in Germany and Canada by 20%. See lines 3 to 7 for the from_using_clause<\/code> where we use ANSI-92 join syntax.<\/p>\n\n\n\n
4a) Update with from_using_clause<\/span><\/path><\/path><\/svg><\/span>
update<\/span> employees e<\/span><\/span>\n   <\/span>set<\/span> e.salary = e.salary * <\/span>1.2<\/span> <\/span><\/span>\n <\/span>using<\/span> departments d<\/span><\/span>\n  <\/span>join<\/span> locations l<\/span><\/span>\n    <\/span>on<\/span> l.location_id = d.location_id<\/span><\/span>\n  <\/span>join<\/span> countries <\/span>c<\/span><\/span>\n    <\/span>on<\/span> <\/span>c<\/span>.country_id = l.country_id<\/span><\/span>\n <\/span>where<\/span> d.department_id = e.department_id<\/span><\/span>\n   <\/span>and<\/span> <\/span>c<\/span>.country_name <\/span>in<\/span> (<\/span>'Germany'<\/span>, <\/span>'Canada'<\/span>);<\/span><\/span><\/code><\/pre><\/div>\n\n\n\n
<\/path><\/path><\/svg><\/span>
3 rows updated.<\/span><\/span>\n<\/span><\/span>\n----------------------------------------------------------------------<\/span><\/span>\n| Id  | Operation                                | Name              |<\/span><\/span>\n----------------------------------------------------------------------<\/span><\/span>\n|   0 | UPDATE STATEMENT                         |                   |<\/span><\/span>\n|   1 |  UPDATE                                  | EMPLOYEES         |<\/span><\/span>\n|   2 |   NESTED LOOPS                           |                   |<\/span><\/span>\n|   3 |    NESTED LOOPS                          |                   |<\/span><\/span>\n|   4 |     NESTED LOOPS                         |                   |<\/span><\/span>\n|   5 |      NESTED LOOPS                        |                   |<\/span><\/span>\n|   6 |       INDEX FULL SCAN                    | COUNTRY_C_ID_PK   |<\/span><\/span>\n|   7 |       TABLE ACCESS BY INDEX ROWID BATCHED| LOCATIONS         |<\/span><\/span>\n|   8 |        INDEX RANGE SCAN                  | LOC_COUNTRY_IX    |<\/span><\/span>\n|   9 |      TABLE ACCESS BY INDEX ROWID BATCHED | DEPARTMENTS       |<\/span><\/span>\n|  10 |       INDEX RANGE SCAN                   | DEPT_LOCATION_IX  |<\/span><\/span>\n|  11 |     INDEX RANGE SCAN                     | EMP_DEPARTMENT_IX |<\/span><\/span>\n|  12 |    TABLE ACCESS BY INDEX ROWID           | EMPLOYEES         |<\/span><\/span>\n----------------------------------------------------------------------<\/span><\/span><\/code><\/pre><\/div>\n\n\n\n
And here’s an alternative, pre-23c-style update<\/code> statement without the from_using_clause<\/code>. It is accessing the employees<\/code> table twice, which might lead to a less efficient execution plan.<\/p>\n\n\n\n
4b) Update with subquery filter<\/span><\/path><\/path><\/svg><\/span>
update<\/span> employees e1<\/span><\/span>\n   <\/span>set<\/span> e1.salary = e1.salary * <\/span>1.2<\/span><\/span>\n <\/span>where<\/span> e1.employee_id <\/span>in<\/span> (<\/span><\/span>\n          <\/span>select<\/span> e2.employee_id<\/span><\/span>\n            <\/span>from<\/span> employees e2<\/span><\/span>\n            <\/span>join<\/span> departments d<\/span><\/span>\n              <\/span>on<\/span> d.department_id = e2.department_id<\/span><\/span>\n            <\/span>join<\/span> locations l<\/span><\/span>\n              <\/span>on<\/span> l.location_id = d.location_id<\/span><\/span>\n            <\/span>join<\/span> countries <\/span>c<\/span><\/span>\n              <\/span>on<\/span> <\/span>c<\/span>.country_id = l.country_id<\/span><\/span>\n           <\/span>where<\/span> <\/span>c<\/span>.country_name <\/span>in<\/span> (<\/span>'Germany'<\/span>, <\/span>'Canada'<\/span>)<\/span><\/span>\n       );<\/span><\/span><\/code><\/pre><\/div>\n\n\n\n
<\/path><\/path><\/svg><\/span>
3 rows updated.<\/span><\/span>\n<\/span><\/span>\n-----------------------------------------------------------------------<\/span><\/span>\n| Id  | Operation                                 | Name              |<\/span><\/span>\n-----------------------------------------------------------------------<\/span><\/span>\n|   0 | UPDATE STATEMENT                          |                   |<\/span><\/span>\n|   1 |  UPDATE                                   | EMPLOYEES         |<\/span><\/span>\n|   2 |   HASH JOIN SEMI                          |                   |<\/span><\/span>\n|   3 |    TABLE ACCESS FULL                      | EMPLOYEES         |<\/span><\/span>\n|   4 |    VIEW                                   | VW_NSO_1          |<\/span><\/span>\n|   5 |     NESTED LOOPS                          |                   |<\/span><\/span>\n|   6 |      NESTED LOOPS                         |                   |<\/span><\/span>\n|   7 |       NESTED LOOPS                        |                   |<\/span><\/span>\n|   8 |        NESTED LOOPS SEMI                  |                   |<\/span><\/span>\n|   9 |         VIEW                              | index$_join$_005  |<\/span><\/span>\n|  10 |          HASH JOIN                        |                   |<\/span><\/span>\n|  11 |           INDEX FAST FULL SCAN            | LOC_COUNTRY_IX    |<\/span><\/span>\n|  12 |           INDEX FAST FULL SCAN            | LOC_ID_PK         |<\/span><\/span>\n|  13 |         INDEX UNIQUE SCAN                 | COUNTRY_C_ID_PK   |<\/span><\/span>\n|  14 |        TABLE ACCESS BY INDEX ROWID BATCHED| DEPARTMENTS       |<\/span><\/span>\n|  15 |         INDEX RANGE SCAN                  | DEPT_LOCATION_IX  |<\/span><\/span>\n|  16 |       INDEX RANGE SCAN                    | EMP_DEPARTMENT_IX |<\/span><\/span>\n|  17 |      TABLE ACCESS BY INDEX ROWID          | EMPLOYEES         |<\/span><\/span>\n-----------------------------------------------------------------------<\/span><\/span><\/code><\/pre><\/div>\n\n\n\n
However, we can update an inline view. The Oracle database has supported this for a very long time (without a BYPASS_UJVC<\/code> hint). There are some limitations, but otherwise, it works quite well. Here’s an example:<\/p>\n\n\n\n
4c) Update inline-view<\/span><\/path><\/path><\/svg><\/span>
update<\/span> (<\/span><\/span>\n          <\/span>select<\/span> e.*<\/span><\/span>\n            <\/span>from<\/span> employees e<\/span><\/span>\n            <\/span>join<\/span> departments d<\/span><\/span>\n              <\/span>on<\/span> d.department_id = e.department_id<\/span><\/span>\n            <\/span>join<\/span> locations l<\/span><\/span>\n              <\/span>on<\/span> l.location_id = d.location_id<\/span><\/span>\n            <\/span>join<\/span> countries <\/span>c<\/span><\/span>\n              <\/span>on<\/span> <\/span>c<\/span>.country_id = l.country_id<\/span><\/span>\n           <\/span>where<\/span> <\/span>c<\/span>.country_name <\/span>in<\/span> (<\/span>'Germany'<\/span>, <\/span>'Canada'<\/span>)<\/span><\/span>\n       )<\/span><\/span>\n   <\/span>set<\/span> salary = salary * <\/span>1.2<\/span>;<\/span><\/span><\/code><\/pre><\/div>\n\n\n\n
<\/path><\/path><\/svg><\/span>
3 rows updated.<\/span><\/span>\n<\/span><\/span>\n---------------------------------------------------------------------<\/span><\/span>\n| Id  | Operation                               | Name              |<\/span><\/span>\n---------------------------------------------------------------------<\/span><\/span>\n|   0 | UPDATE STATEMENT                        |                   |<\/span><\/span>\n|   1 |  UPDATE                                 | EMPLOYEES         |<\/span><\/span>\n|   2 |   NESTED LOOPS                          |                   |<\/span><\/span>\n|   3 |    NESTED LOOPS                         |                   |<\/span><\/span>\n|   4 |     NESTED LOOPS                        |                   |<\/span><\/span>\n|   5 |      INDEX FULL SCAN                    | COUNTRY_C_ID_PK   |<\/span><\/span>\n|   6 |      TABLE ACCESS BY INDEX ROWID BATCHED| LOCATIONS         |<\/span><\/span>\n|   7 |       INDEX RANGE SCAN                  | LOC_COUNTRY_IX    |<\/span><\/span>\n|   8 |     TABLE ACCESS BY INDEX ROWID BATCHED | DEPARTMENTS       |<\/span><\/span>\n|   9 |      INDEX RANGE SCAN                   | DEPT_LOCATION_IX  |<\/span><\/span>\n|  10 |    INDEX RANGE SCAN                     | EMP_DEPARTMENT_IX |<\/span><\/span>\n---------------------------------------------------------------------<\/span><\/span><\/code><\/pre><\/div>\n\n\n\n
The execution plan is similar to the variant with the from_using_clause<\/code>. So, from a performance point of view, this is a good option. However, I like the from_using_clause<\/code> variant better because it’s clearer which table is updated and which tables are just used for query purposes.<\/p>\n\n\n\n
SQL UPDATE RETURN Clause Enhancements<\/h2>\n\n\n\n
The returning_clause<\/a><\/code> has been extended. <\/p>\n\n\n\n
\"returning_clause<\/a><\/figure>\n\n\n\n
It’s now possible to explicitly return old<\/code> and new<\/code> values. The default depends on the operation. new<\/code> in insert<\/code>\/update<\/code> and old<\/code> in delete<\/code> statements. I do not see a lot of value for delete<\/code> and insert<\/code> statements besides maybe making the statements more explicit and therefore easier to read. However, for the update<\/code> statement, this new feature can be useful.<\/p>\n\n\n\n
Here’s a small SQL script showing the new returning clause in action for insert<\/a><\/code>, update<\/a><\/code> and delete<\/a><\/code>.<\/p>\n\n\n\n
5) New returning_clause in insert, update and delete<\/span><\/path><\/path><\/svg><\/span>
set<\/span> serveroutput <\/span>on<\/span><\/span>\ndrop<\/span> <\/span>table<\/span> <\/span>if<\/span> <\/span>exists<\/span> t;<\/span><\/span>\ncreate<\/span> <\/span>table<\/span> t (id <\/span>integer<\/span>, value <\/span>integer<\/span>);<\/span><\/span>\n<\/span>\ndeclare<\/span><\/span>\n   <\/span>l_old_value<\/span> t.value%<\/span>type<\/span>;<\/span><\/span>\n   <\/span>l_new_value<\/span> t.value%<\/span>type<\/span>;<\/span><\/span>\nbegin<\/span><\/span>\n   <\/span>dbms_random.<\/span>seed<\/span>(<\/span>16<\/span>);<\/span><\/span>\n<\/span>\n   <\/span>insert<\/span> <\/span>into<\/span> t (id, value) <\/span><\/span>\n   <\/span>values<\/span> (<\/span>1<\/span>, <\/span>dbms_random.<\/span>value<\/span>(low => <\/span>1<\/span>, high => <\/span>100<\/span>))<\/span><\/span>\n   <\/span>return<\/span> new value <\/span>into<\/span> <\/span>l_new_value<\/span>;<\/span><\/span>\n   <\/span>dbms_output.<\/span>put_line<\/span>(<\/span>'Insert: new value '<\/span> || <\/span>l_new_value<\/span>);<\/span><\/span>\n<\/span>\n   <\/span>update<\/span> t<\/span><\/span>\n      <\/span>set<\/span> value = value * <\/span>2<\/span><\/span>\n    <\/span>where<\/span> id = <\/span>1<\/span><\/span>\n   <\/span>return<\/span> old value, new value <\/span>into<\/span> <\/span>l_old_value<\/span>, <\/span>l_new_value<\/span>;<\/span><\/span>\n   <\/span>dbms_output.<\/span>put_line<\/span>(<\/span>'Update: old value '<\/span> || <\/span>l_old_value<\/span> || <\/span>', new value '<\/span> || <\/span>l_new_value<\/span>);<\/span><\/span>\n<\/span>\n   <\/span>delete<\/span> t<\/span><\/span>\n    <\/span>where<\/span> id = <\/span>1<\/span><\/span>\n   <\/span>return<\/span> old value <\/span>into<\/span> <\/span>l_old_value<\/span>;<\/span><\/span>\n   <\/span>dbms_output.<\/span>put_line<\/span>(<\/span>'Delete: old value '<\/span> || <\/span>l_old_value<\/span>); <\/span><\/span>\nend<\/span>;<\/span><\/span>\n\/<\/span><\/span><\/code><\/pre><\/div>\n\n\n\n
<\/path><\/path><\/svg><\/span>
Table T dropped.<\/span><\/span>\n<\/span><\/span>\nTable T created.<\/span><\/span>\n<\/span><\/span>\nInsert: new value 21<\/span><\/span>\nUpdate: old value 21, new value 42<\/span><\/span>\nDelete: old value 42<\/span><\/span>\n<\/span><\/span>\nPL\/SQL procedure successfully completed.<\/span><\/span><\/code><\/pre><\/div>\n\n\n\n
<\/p>\n\n\n\n
DEFAULT ON NULL for UPDATE Statements<\/h2>\n\n\n\n
The column_definition<\/a><\/code> clause in the create table<\/code> statement has been extended.<\/p>\n\n\n\n
\"column_definition<\/a><\/figure>\n\n\n\n
Finally, it’s possible to enforce the default on null<\/code> expression also for update<\/code> and merge<\/code> statements.<\/p>\n\n\n\n
The next SQL script demonstrates this.<\/p>\n\n\n\n
6) default on null for insert and update<\/span><\/path><\/path><\/svg><\/span>
drop<\/span> <\/span>table<\/span> <\/span>if<\/span> <\/span>exists<\/span> t;<\/span><\/span>\ncreate<\/span> <\/span>table<\/span> t (<\/span><\/span>\n   id    <\/span>integer<\/span> <\/span>not<\/span> <\/span>null<\/span> <\/span>primary key<\/span>,<\/span><\/span>\n   value <\/span>varchar2<\/span>(<\/span>10<\/span> <\/span>char<\/span>) <\/span>default<\/span> <\/span>on<\/span> <\/span>null<\/span> <\/span>for<\/span> <\/span>insert<\/span> <\/span>and<\/span> <\/span>update<\/span> <\/span>'my default'<\/span><\/span>\n);<\/span><\/span>\n<\/span>\ninsert<\/span> <\/span>into<\/span> t(id, value)<\/span><\/span>\nvalues<\/span> (<\/span>1<\/span>, <\/span>'value1'<\/span>),<\/span><\/span>\n       (<\/span>2<\/span>, <\/span>null<\/span>);<\/span><\/span>\nselect<\/span> * <\/span>from<\/span> t <\/span>order by<\/span> id;<\/span><\/span>\n<\/span>\nupdate<\/span> t <\/span>set<\/span> value = <\/span>case<\/span> id<\/span><\/span>\n                        <\/span>when<\/span> <\/span>1<\/span> <\/span>then<\/span><\/span>\n                           <\/span>null<\/span><\/span>\n                        <\/span>when<\/span> <\/span>2<\/span> <\/span>then<\/span><\/span>\n                           <\/span>'value2'<\/span><\/span>\n                     <\/span>end<\/span>;<\/span><\/span>\nselect<\/span> * <\/span>from<\/span> t <\/span>order by<\/span> id;<\/span><\/span>\n<\/span>\nmerge<\/span> <\/span>into<\/span> t<\/span><\/span>\nusing<\/span> (<\/span>values<\/span> <\/span><\/span>\n         (<\/span>1<\/span>, <\/span>'value3'<\/span>),<\/span><\/span>\n         (<\/span>2<\/span>, <\/span>null<\/span>),<\/span><\/span>\n         (<\/span>3<\/span>, <\/span>null<\/span>)<\/span><\/span>\n      ) s (id, value)<\/span><\/span>\n   <\/span>on<\/span> (t.id = s.id)<\/span><\/span>\n <\/span>when<\/span> <\/span>matched<\/span> <\/span>then<\/span><\/span>\n      <\/span>update<\/span><\/span>\n         <\/span>set<\/span> t.value = s.value<\/span><\/span>\n <\/span>when<\/span> <\/span>not<\/span> <\/span>matched<\/span> <\/span>then<\/span><\/span>\n      <\/span>insert<\/span> (t.id, t.value)<\/span><\/span>\n      <\/span>values<\/span> (s.id, s.value);<\/span><\/span>\nselect<\/span> * <\/span>from<\/span> t <\/span>order by<\/span> id;<\/span><\/span><\/code><\/pre><\/div>\n\n\n\n
<\/path><\/path><\/svg><\/span>
Table T dropped.<\/span><\/span>\n<\/span><\/span>\nTable T created.<\/span><\/span>\n<\/span><\/span>\n2 rows inserted.<\/span><\/span>\n<\/span><\/span>\n        ID VALUE     <\/span><\/span>\n---------- ----------<\/span><\/span>\n         1 value1    <\/span><\/span>\n         2 my default<\/span><\/span>\n<\/span><\/span>\n2 rows updated.<\/span><\/span>\n<\/span><\/span>\n        ID VALUE     <\/span><\/span>\n---------- ----------<\/span><\/span>\n         1 my default<\/span><\/span>\n         2 value2    <\/span><\/span>\n<\/span><\/span>\n3 rows merged.<\/span><\/span>\n<\/span><\/span>\n        ID VALUE     <\/span><\/span>\n---------- ----------<\/span><\/span>\n         1 value3    <\/span><\/span>\n         2 my default<\/span><\/span>\n         3 my default<\/span><\/span><\/code><\/pre><\/div>\n\n\n\n
Lock-Free Reservation<\/h2>\n\n\n\n
The new datatype_domain<\/a><\/code> clause comes with a reservable<\/code> keyword.<\/p>\n\n\n\n
\"\"<\/a><\/figure>\n\n\n\n
You can update reservable<\/code> columns without locking a row. As a result, updating such a column is possible from multiple sessions in a transactional way. However, only numeric columns can be declared as reserveable<\/code>. <\/p>\n\n\n\n
Let’s make an example.<\/p>\n\n\n\n
7.1) Create and populate table with reservable column<\/span><\/path><\/path><\/svg><\/span>
drop<\/span> <\/span>table<\/span> <\/span>if<\/span> <\/span>exists<\/span> e;<\/span><\/span>\ncreate<\/span> <\/span>table<\/span> e (<\/span><\/span>\n   empno <\/span>number<\/span>(<\/span>4<\/span>,<\/span>0<\/span>)  <\/span>not<\/span> <\/span>null<\/span> <\/span>primary key<\/span>, <\/span><\/span>\n   ename <\/span>varchar2<\/span>(<\/span>10<\/span>) <\/span>not<\/span> <\/span>null<\/span>,<\/span><\/span>\n   sal   <\/span>number<\/span>(<\/span>7<\/span>,<\/span>2<\/span>)  reservable <\/span>not<\/span> <\/span>null<\/span><\/span>\n);<\/span><\/span>\ninsert<\/span> <\/span>into<\/span> e(empno, ename, sal)<\/span><\/span>\nvalues<\/span> (<\/span>7788<\/span>, <\/span>'SCOTT'<\/span>, <\/span>3000<\/span>),<\/span><\/span>\n       (<\/span>7739<\/span>, <\/span>'KING'<\/span>,  <\/span>5000<\/span>);<\/span><\/span>\ncommit<\/span>;<\/span><\/span><\/code><\/pre><\/div>\n\n\n\n
<\/path><\/path><\/svg><\/span>
Table E dropped.<\/span><\/span>\n<\/span><\/span>\nTable E created.<\/span><\/span>\n<\/span><\/span>\n2 rows inserted.<\/span><\/span>\n<\/span><\/span>\nCommit complete.<\/span><\/span><\/code><\/pre><\/div>\n\n\n\n
After the setup, we run two database sessions in parallel.<\/p>\n\n\n\n
\n
\n
7.2) Session A – update sal of empno 7788<\/span><\/path><\/path><\/svg><\/span>
update<\/span> e<\/span><\/span>\n   <\/span>set<\/span> sal = sal + <\/span>100<\/span><\/span>\n <\/span>where<\/span> empno = <\/span>7788<\/span>;<\/span><\/span>\n <\/span><\/span>\nselect<\/span> * <\/span>from<\/span> e;<\/span><\/span><\/code><\/pre><\/div>\n\n\n\n
<\/path><\/path><\/svg><\/span>
1 row updated.<\/span><\/span>\n<\/span><\/span>\n     EMPNO ENAME             SAL<\/span><\/span>\n---------- ---------- ----------<\/span><\/span>\n      7788 SCOTT            3000<\/span><\/span>\n      7739 KING             5000<\/span><\/span><\/code><\/pre><\/div>\n<\/div>\n\n\n\n
\n
7.3) Session B – update sal of empno 7788<\/span><\/path><\/path><\/svg><\/span>
update<\/span> e<\/span><\/span>\n   <\/span>set<\/span> sal = sal + <\/span>500<\/span><\/span>\n <\/span>where<\/span> empno = <\/span>7788<\/span>;<\/span><\/span>\n <\/span><\/span>\nselect<\/span> * <\/span>from<\/span> e;<\/span><\/span><\/code><\/pre><\/div>\n\n\n\n
<\/path><\/path><\/svg><\/span>
1 row updated.<\/span><\/span>\n<\/span><\/span>\n     EMPNO ENAME             SAL<\/span><\/span>\n---------- ---------- ----------<\/span><\/span>\n      7788 SCOTT            3000<\/span><\/span>\n      7739 KING             5000<\/span><\/span><\/code><\/pre><\/div>\n<\/div>\n<\/div>\n\n\n\n
We’ve updated the same record in two sessions. The transactions are pending and the changes are not yet visible in the target table. Let’s complete the pending transactions.<\/p>\n\n\n\n
\n
\n
7.4) Session A – commit changes<\/span><\/path><\/path><\/svg><\/span>
commit<\/span>;<\/span><\/span>\n<\/span>\nselect<\/span> * <\/span>from<\/span> e; <\/span><\/span><\/code><\/pre><\/div>\n\n\n\n
<\/path><\/path><\/svg><\/span>
Commit complete.<\/span><\/span>\n<\/span><\/span>\n     EMPNO ENAME             SAL<\/span><\/span>\n---------- ---------- ----------<\/span><\/span>\n      7788 SCOTT            3100<\/span><\/span>\n      7739 KING             5000<\/span><\/span><\/code><\/pre><\/div>\n<\/div>\n\n\n\n
\n
7.5) Session B – commit changes<\/span><\/path><\/path><\/svg><\/span>
commit<\/span>;<\/span><\/span>\n<\/span>\nselect<\/span> * <\/span>from<\/span> e; <\/span><\/span><\/code><\/pre><\/div>\n\n\n\n
<\/path><\/path><\/svg><\/span>
Commit complete.<\/span><\/span>\n<\/span><\/span>\n     EMPNO ENAME             SAL<\/span><\/span>\n---------- ---------- ----------<\/span><\/span>\n      7788 SCOTT            3600<\/span><\/span>\n      7739 KING             5000<\/span><\/span><\/code><\/pre><\/div>\n<\/div>\n<\/div>\n\n\n\n
After committing, the changes are visible in the target table. The changes from both sessions have been applied. Concurrent updates of the same row without locking. Pure magic.<\/p>\n\n\n\n
How is that possible? Quite simple. Behind the scenes, the Oracle Database creates a reservation journal table named SYS_RESERVJRNL_<object_id_of_table><\/code> for every table with a reservable<\/code> column. This table stores the pending changes per session and applies them on commit. You can query this table, to better understand the process.<\/p>\n\n\n\n
See the Database Development Guide<\/a> for more information about lock-free reservations.<\/p>\n\n\n\n
More New Features<\/h2>\n\n\n\n
More features are applicable in DML statements. For example, using sys_row_etag<\/a><\/code> for optimistic locking or when working with JSON-relational duality views. The JSON-Relational Duality Developer’s Guide<\/a> explains this new feature in detail. <\/p>\n\n\n\n
For a complete list see Oracle Database New Features<\/a>.<\/p>\n\n\n\n
Outlook<\/h2>\n\n\n\n
For the next episode, the IslandSQL grammar will be extended to cover the PostgreSQL 16 grammar for the current statements in scope. This means all DML statements. I’m sure I will be able to show some interesting differences between the Oracle Database and PostgreSQL. Stay tuned.<\/p>\n","protected":false},"excerpt":{"rendered":"
Introduction The IslandSQL grammar now covers all DML statements. This means call, delete, explain plan, insert, lock table, merge, select and update. In this episode, we will focus on new features in the Oracle Database 23c that can be used in insert, update, delete and merge statements. For the select statement see [\u2026]<\/span><\/p>\n","protected":false},"author":1,"featured_media":13235,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[5],"tags":[139,86,137,140,85],"class_list":["post-13198","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-oracle","tag-antlr","tag-code-analysis","tag-islandsql","tag-oracle-26ai","tag-sql"],"yoast_head":"\nIslandSQL Episode 6: DML Statements in Oracle Database 23c - Philipp Salvisberg's Blog<\/title>\n<meta name=\"description\" content=\"In this episode, we will focus on new features in the Oracle Database 23c that can be used in DML statements. INSERT, UPDATE, DELETE & MERGE.\" \/>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/www.salvis.com\/blog\/2024\/01\/30\/islandsql-episode-6-dml-statements-in-oracle-database-23c\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"IslandSQL Episode 6: DML Statements in Oracle Database 23c - Philipp Salvisberg's Blog\" \/>\n<meta property=\"og:description\" content=\"In this episode, we will focus on new features in the Oracle Database 23c that can be used in DML statements. INSERT, UPDATE, DELETE & MERGE.\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.salvis.com\/blog\/2024\/01\/30\/islandsql-episode-6-dml-statements-in-oracle-database-23c\/\" \/>\n<meta property=\"og:site_name\" content=\"Philipp Salvisberg's Blog\" \/>\n<meta property=\"article:published_time\" content=\"2024-01-30T00:17:39+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2024-06-17T09:06:22+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/www.salvis.com\/blog\/wp-content\/uploads\/2024\/01\/IslandSQL6.png\" \/>\n\t<meta property=\"og:image:width\" content=\"500\" \/>\n\t<meta property=\"og:image:height\" content=\"500\" \/>\n\t<meta property=\"og:image:type\" content=\"image\/png\" \/>\n<meta name=\"author\" content=\"Philipp Salvisberg\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:creator\" content=\"@phsalvisberg\" \/>\n<meta name=\"twitter:site\" content=\"@phsalvisberg\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Philipp Salvisberg\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"5 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\\\/\\\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\\\/\\\/www.salvis.com\\\/blog\\\/2024\\\/01\\\/30\\\/islandsql-episode-6-dml-statements-in-oracle-database-23c\\\/#article\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/www.salvis.com\\\/blog\\\/2024\\\/01\\\/30\\\/islandsql-episode-6-dml-statements-in-oracle-database-23c\\\/\"},\"author\":{\"name\":\"Philipp Salvisberg\",\"@id\":\"https:\\\/\\\/www.salvis.com\\\/blog\\\/#\\\/schema\\\/person\\\/34352245c48678b1a5a05d4bc1339515\"},\"headline\":\"IslandSQL Episode 6: DML Statements in Oracle Database 23c\",\"datePublished\":\"2024-01-30T00:17:39+00:00\",\"dateModified\":\"2024-06-17T09:06:22+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\\\/\\\/www.salvis.com\\\/blog\\\/2024\\\/01\\\/30\\\/islandsql-episode-6-dml-statements-in-oracle-database-23c\\\/\"},\"wordCount\":861,\"commentCount\":1,\"publisher\":{\"@id\":\"https:\\\/\\\/www.salvis.com\\\/blog\\\/#\\\/schema\\\/person\\\/34352245c48678b1a5a05d4bc1339515\"},\"image\":{\"@id\":\"https:\\\/\\\/www.salvis.com\\\/blog\\\/2024\\\/01\\\/30\\\/islandsql-episode-6-dml-statements-in-oracle-database-23c\\\/#primaryimage\"},\"thumbnailUrl\":\"https:\\\/\\\/www.salvis.com\\\/blog\\\/wp-content\\\/uploads\\\/2024\\\/01\\\/IslandSQL6.png\",\"keywords\":[\"ANTLR\",\"Code Analysis\",\"IslandSQL\",\"Oracle 26ai\",\"SQL\"],\"articleSection\":[\"Oracle\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\\\/\\\/www.salvis.com\\\/blog\\\/2024\\\/01\\\/30\\\/islandsql-episode-6-dml-statements-in-oracle-database-23c\\\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\\\/\\\/www.salvis.com\\\/blog\\\/2024\\\/01\\\/30\\\/islandsql-episode-6-dml-statements-in-oracle-database-23c\\\/\",\"url\":\"https:\\\/\\\/www.salvis.com\\\/blog\\\/2024\\\/01\\\/30\\\/islandsql-episode-6-dml-statements-in-oracle-database-23c\\\/\",\"name\":\"IslandSQL Episode 6: DML Statements in Oracle Database 23c - Philipp Salvisberg's Blog\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/www.salvis.com\\\/blog\\\/#website\"},\"primaryImageOfPage\":{\"@id\":\"https:\\\/\\\/www.salvis.com\\\/blog\\\/2024\\\/01\\\/30\\\/islandsql-episode-6-dml-statements-in-oracle-database-23c\\\/#primaryimage\"},\"image\":{\"@id\":\"https:\\\/\\\/www.salvis.com\\\/blog\\\/2024\\\/01\\\/30\\\/islandsql-episode-6-dml-statements-in-oracle-database-23c\\\/#primaryimage\"},\"thumbnailUrl\":\"https:\\\/\\\/www.salvis.com\\\/blog\\\/wp-content\\\/uploads\\\/2024\\\/01\\\/IslandSQL6.png\",\"datePublished\":\"2024-01-30T00:17:39+00:00\",\"dateModified\":\"2024-06-17T09:06:22+00:00\",\"description\":\"In this episode, we will focus on new features in the Oracle Database 23c that can be used in DML statements. INSERT, UPDATE, DELETE & MERGE.\",\"breadcrumb\":{\"@id\":\"https:\\\/\\\/www.salvis.com\\\/blog\\\/2024\\\/01\\\/30\\\/islandsql-episode-6-dml-statements-in-oracle-database-23c\\\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\\\/\\\/www.salvis.com\\\/blog\\\/2024\\\/01\\\/30\\\/islandsql-episode-6-dml-statements-in-oracle-database-23c\\\/\"]}]},{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\\\/\\\/www.salvis.com\\\/blog\\\/2024\\\/01\\\/30\\\/islandsql-episode-6-dml-statements-in-oracle-database-23c\\\/#primaryimage\",\"url\":\"https:\\\/\\\/www.salvis.com\\\/blog\\\/wp-content\\\/uploads\\\/2024\\\/01\\\/IslandSQL6.png\",\"contentUrl\":\"https:\\\/\\\/www.salvis.com\\\/blog\\\/wp-content\\\/uploads\\\/2024\\\/01\\\/IslandSQL6.png\",\"width\":500,\"height\":500,\"caption\":\"IslandSQL Episode 6 - DML Statements in Oracle Database 23c\"},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\\\/\\\/www.salvis.com\\\/blog\\\/2024\\\/01\\\/30\\\/islandsql-episode-6-dml-statements-in-oracle-database-23c\\\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\\\/\\\/www.salvis.com\\\/blog\\\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"IslandSQL Episode 6: DML Statements in Oracle Database 23c\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\\\/\\\/www.salvis.com\\\/blog\\\/#website\",\"url\":\"https:\\\/\\\/www.salvis.com\\\/blog\\\/\",\"name\":\"Philipp Salvisberg's Blog\",\"description\":\"Database-centric development\",\"publisher\":{\"@id\":\"https:\\\/\\\/www.salvis.com\\\/blog\\\/#\\\/schema\\\/person\\\/34352245c48678b1a5a05d4bc1339515\"},\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\\\/\\\/www.salvis.com\\\/blog\\\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":[\"Person\",\"Organization\"],\"@id\":\"https:\\\/\\\/www.salvis.com\\\/blog\\\/#\\\/schema\\\/person\\\/34352245c48678b1a5a05d4bc1339515\",\"name\":\"Philipp Salvisberg\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\\\/\\\/www.salvis.com\\\/blog\\\/wp-content\\\/uploads\\\/2010\\\/11\\\/phs_trivadis4.jpg\",\"url\":\"https:\\\/\\\/www.salvis.com\\\/blog\\\/wp-content\\\/uploads\\\/2010\\\/11\\\/phs_trivadis4.jpg\",\"contentUrl\":\"https:\\\/\\\/www.salvis.com\\\/blog\\\/wp-content\\\/uploads\\\/2010\\\/11\\\/phs_trivadis4.jpg\",\"width\":400,\"height\":400,\"caption\":\"Philipp Salvisberg\"},\"logo\":{\"@id\":\"https:\\\/\\\/www.salvis.com\\\/blog\\\/wp-content\\\/uploads\\\/2010\\\/11\\\/phs_trivadis4.jpg\"},\"sameAs\":[\"http:\\\/\\\/www.salvis.com\\\/\"]}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"IslandSQL Episode 6: DML Statements in Oracle Database 23c - Philipp Salvisberg's Blog","description":"In this episode, we will focus on new features in the Oracle Database 23c that can be used in DML statements. INSERT, UPDATE, DELETE & MERGE.","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/www.salvis.com\/blog\/2024\/01\/30\/islandsql-episode-6-dml-statements-in-oracle-database-23c\/","og_locale":"en_US","og_type":"article","og_title":"IslandSQL Episode 6: DML Statements in Oracle Database 23c - Philipp Salvisberg's Blog","og_description":"In this episode, we will focus on new features in the Oracle Database 23c that can be used in DML statements. INSERT, UPDATE, DELETE & MERGE.","og_url":"https:\/\/www.salvis.com\/blog\/2024\/01\/30\/islandsql-episode-6-dml-statements-in-oracle-database-23c\/","og_site_name":"Philipp Salvisberg's Blog","article_published_time":"2024-01-30T00:17:39+00:00","article_modified_time":"2024-06-17T09:06:22+00:00","og_image":[{"width":500,"height":500,"url":"https:\/\/www.salvis.com\/blog\/wp-content\/uploads\/2024\/01\/IslandSQL6.png","type":"image\/png"}],"author":"Philipp Salvisberg","twitter_card":"summary_large_image","twitter_creator":"@phsalvisberg","twitter_site":"@phsalvisberg","twitter_misc":{"Written by":"Philipp Salvisberg","Est. reading time":"5 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/www.salvis.com\/blog\/2024\/01\/30\/islandsql-episode-6-dml-statements-in-oracle-database-23c\/#article","isPartOf":{"@id":"https:\/\/www.salvis.com\/blog\/2024\/01\/30\/islandsql-episode-6-dml-statements-in-oracle-database-23c\/"},"author":{"name":"Philipp Salvisberg","@id":"https:\/\/www.salvis.com\/blog\/#\/schema\/person\/34352245c48678b1a5a05d4bc1339515"},"headline":"IslandSQL Episode 6: DML Statements in Oracle Database 23c","datePublished":"2024-01-30T00:17:39+00:00","dateModified":"2024-06-17T09:06:22+00:00","mainEntityOfPage":{"@id":"https:\/\/www.salvis.com\/blog\/2024\/01\/30\/islandsql-episode-6-dml-statements-in-oracle-database-23c\/"},"wordCount":861,"commentCount":1,"publisher":{"@id":"https:\/\/www.salvis.com\/blog\/#\/schema\/person\/34352245c48678b1a5a05d4bc1339515"},"image":{"@id":"https:\/\/www.salvis.com\/blog\/2024\/01\/30\/islandsql-episode-6-dml-statements-in-oracle-database-23c\/#primaryimage"},"thumbnailUrl":"https:\/\/www.salvis.com\/blog\/wp-content\/uploads\/2024\/01\/IslandSQL6.png","keywords":["ANTLR","Code Analysis","IslandSQL","Oracle 26ai","SQL"],"articleSection":["Oracle"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/www.salvis.com\/blog\/2024\/01\/30\/islandsql-episode-6-dml-statements-in-oracle-database-23c\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/www.salvis.com\/blog\/2024\/01\/30\/islandsql-episode-6-dml-statements-in-oracle-database-23c\/","url":"https:\/\/www.salvis.com\/blog\/2024\/01\/30\/islandsql-episode-6-dml-statements-in-oracle-database-23c\/","name":"IslandSQL Episode 6: DML Statements in Oracle Database 23c - Philipp Salvisberg's Blog","isPartOf":{"@id":"https:\/\/www.salvis.com\/blog\/#website"},"primaryImageOfPage":{"@id":"https:\/\/www.salvis.com\/blog\/2024\/01\/30\/islandsql-episode-6-dml-statements-in-oracle-database-23c\/#primaryimage"},"image":{"@id":"https:\/\/www.salvis.com\/blog\/2024\/01\/30\/islandsql-episode-6-dml-statements-in-oracle-database-23c\/#primaryimage"},"thumbnailUrl":"https:\/\/www.salvis.com\/blog\/wp-content\/uploads\/2024\/01\/IslandSQL6.png","datePublished":"2024-01-30T00:17:39+00:00","dateModified":"2024-06-17T09:06:22+00:00","description":"In this episode, we will focus on new features in the Oracle Database 23c that can be used in DML statements. INSERT, UPDATE, DELETE & MERGE.","breadcrumb":{"@id":"https:\/\/www.salvis.com\/blog\/2024\/01\/30\/islandsql-episode-6-dml-statements-in-oracle-database-23c\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.salvis.com\/blog\/2024\/01\/30\/islandsql-episode-6-dml-statements-in-oracle-database-23c\/"]}]},{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.salvis.com\/blog\/2024\/01\/30\/islandsql-episode-6-dml-statements-in-oracle-database-23c\/#primaryimage","url":"https:\/\/www.salvis.com\/blog\/wp-content\/uploads\/2024\/01\/IslandSQL6.png","contentUrl":"https:\/\/www.salvis.com\/blog\/wp-content\/uploads\/2024\/01\/IslandSQL6.png","width":500,"height":500,"caption":"IslandSQL Episode 6 - DML Statements in Oracle Database 23c"},{"@type":"BreadcrumbList","@id":"https:\/\/www.salvis.com\/blog\/2024\/01\/30\/islandsql-episode-6-dml-statements-in-oracle-database-23c\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/www.salvis.com\/blog\/"},{"@type":"ListItem","position":2,"name":"IslandSQL Episode 6: DML Statements in Oracle Database 23c"}]},{"@type":"WebSite","@id":"https:\/\/www.salvis.com\/blog\/#website","url":"https:\/\/www.salvis.com\/blog\/","name":"Philipp Salvisberg's Blog","description":"Database-centric development","publisher":{"@id":"https:\/\/www.salvis.com\/blog\/#\/schema\/person\/34352245c48678b1a5a05d4bc1339515"},"potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/www.salvis.com\/blog\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":["Person","Organization"],"@id":"https:\/\/www.salvis.com\/blog\/#\/schema\/person\/34352245c48678b1a5a05d4bc1339515","name":"Philipp Salvisberg","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.salvis.com\/blog\/wp-content\/uploads\/2010\/11\/phs_trivadis4.jpg","url":"https:\/\/www.salvis.com\/blog\/wp-content\/uploads\/2010\/11\/phs_trivadis4.jpg","contentUrl":"https:\/\/www.salvis.com\/blog\/wp-content\/uploads\/2010\/11\/phs_trivadis4.jpg","width":400,"height":400,"caption":"Philipp Salvisberg"},"logo":{"@id":"https:\/\/www.salvis.com\/blog\/wp-content\/uploads\/2010\/11\/phs_trivadis4.jpg"},"sameAs":["http:\/\/www.salvis.com\/"]}]}},"_links":{"self":[{"href":"https:\/\/www.salvis.com\/blog\/wp-json\/wp\/v2\/posts\/13198","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.salvis.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.salvis.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.salvis.com\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.salvis.com\/blog\/wp-json\/wp\/v2\/comments?post=13198"}],"version-history":[{"count":41,"href":"https:\/\/www.salvis.com\/blog\/wp-json\/wp\/v2\/posts\/13198\/revisions"}],"predecessor-version":[{"id":13427,"href":"https:\/\/www.salvis.com\/blog\/wp-json\/wp\/v2\/posts\/13198\/revisions\/13427"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.salvis.com\/blog\/wp-json\/wp\/v2\/media\/13235"}],"wp:attachment":[{"href":"https:\/\/www.salvis.com\/blog\/wp-json\/wp\/v2\/media?parent=13198"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.salvis.com\/blog\/wp-json\/wp\/v2\/categories?post=13198"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.salvis.com\/blog\/wp-json\/wp\/v2\/tags?post=13198"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}