There are several ways to do this, but I will only show you how to do this using SQL Developer. Why? Because I just came across this functionality the other day.
However there are some things you have to pay attention to, you might get frustrated with it otherwise.
26 October 2009
13 October 2009
Oracle PL/SQL Programming Preview
This post will be mainly in Dutch. It's about a Preview we are doing in our office in Nieuwegein, The Netherlands.
The sessions will probably be in Dutch, but can be changed on the fly to English if desired.
If you are not able to make it to our office, you still have a chance to see Patrick do his presentations in Atlanta, Georgia during the Oracle PL/SQL Programming Conference, November 10 and 11.
You can register by following this link.
Our Event in Nieuwegein will be on October 20 (Thanks Erik for pointing out the date was missing)
Here is the agenda for this evening:
De Oracle PL/SQL Programming conference vind dit jaar in Atlanta, Georgia plaats. Patrick Barel is daarbij om een drietal presentaties te geven. Voor degene die niet aanwezig zullen zijn in Atlanta vind deze OPP-Preview plaats.
Tijdens deze KC zal Patrick twee van deze presentaties geven.
Na het diner zal Alex een re-run geven van zijn ODTUG presentatie: "SQL Holmes".
Het programma voor deze avond:
16:30
"Pipelined table functions" - Patrick Barel
Pipelined table functions offer an ideal convergence of the elegance and simplicity of PL/SQL with the performance of SQL. Complex data transformations are effortless to develop and support with PL/SQL, yet to achieve high-performance data processing, we often resort to set-based SQL solutions. Pipelined functions bridge the gap between the two methods effortlessly, but they also have some unique performance features of their own, making them a superb performance optimization tool.
18:00
diner: Chinees
Na diner (rond 19:00):
"SQL Holmes: The Case of the Missing Performance" - Alex Nuijten
During this presentation, a case study is unfolded to reveal the true cause of a slow performing query. Did the database just "have a bad day"? Was the evil DBA to blame? The PL/SQL developer who didn't get enough coffee? Or was it the application sending the "wrong" query in the first place?
In this classic "whodunnit" you will take a tour past the crime scene. Investigate the query, use the tools of the trade and collect all the relevant information. Follow the trail to uncover the truth and nothing but the truth...
20:00
Ter afsluiting:
"Optimizing SQL with Collections" - Patrick Barel
Collections (array-like structures in PL/SQL) are used in two of the most important performance features of PL/SQL: FORALL and BULK COLLECT. This session demonstrates the power of these features and offers in-depth guidance on how to apply them.
The sessions will probably be in Dutch, but can be changed on the fly to English if desired.
If you are not able to make it to our office, you still have a chance to see Patrick do his presentations in Atlanta, Georgia during the Oracle PL/SQL Programming Conference, November 10 and 11.
You can register by following this link.
Our Event in Nieuwegein will be on October 20 (Thanks Erik for pointing out the date was missing)
Here is the agenda for this evening:
De Oracle PL/SQL Programming conference vind dit jaar in Atlanta, Georgia plaats. Patrick Barel is daarbij om een drietal presentaties te geven. Voor degene die niet aanwezig zullen zijn in Atlanta vind deze OPP-Preview plaats.
Tijdens deze KC zal Patrick twee van deze presentaties geven.
Na het diner zal Alex een re-run geven van zijn ODTUG presentatie: "SQL Holmes".
Het programma voor deze avond:
16:30
"Pipelined table functions" - Patrick Barel
Pipelined table functions offer an ideal convergence of the elegance and simplicity of PL/SQL with the performance of SQL. Complex data transformations are effortless to develop and support with PL/SQL, yet to achieve high-performance data processing, we often resort to set-based SQL solutions. Pipelined functions bridge the gap between the two methods effortlessly, but they also have some unique performance features of their own, making them a superb performance optimization tool.
18:00
diner: Chinees
Na diner (rond 19:00):
"SQL Holmes: The Case of the Missing Performance" - Alex Nuijten
During this presentation, a case study is unfolded to reveal the true cause of a slow performing query. Did the database just "have a bad day"? Was the evil DBA to blame? The PL/SQL developer who didn't get enough coffee? Or was it the application sending the "wrong" query in the first place?
In this classic "whodunnit" you will take a tour past the crime scene. Investigate the query, use the tools of the trade and collect all the relevant information. Follow the trail to uncover the truth and nothing but the truth...
20:00
Ter afsluiting:
"Optimizing SQL with Collections" - Patrick Barel
Collections (array-like structures in PL/SQL) are used in two of the most important performance features of PL/SQL: FORALL and BULK COLLECT. This session demonstrates the power of these features and offers in-depth guidance on how to apply them.
09 October 2009
Just the plain DDL, please.. DBMS_METADATA
The other day I needed to get some DDL statements from the datadictionary. In the old days you could write your own queries to extract all this. Nowadays you can use the built in package DBMS_METADATA to get the DDL for you.
Let's take a look at how you can use this. The DDL that I want to extract is the infamous EMP table, normally in the SCOTT schema.
To make it a little more interesting, we also add the DEPT table and place some constraints on them.
If you use DBMS_METADATA just like that, you might get more than you asked for.
As you can in the code block above, you get the complete DDL including the storage clauses and the constraints. If this is not what you want, and I didn't want all this, than you need to modify some transformation parameters.
Each of these transformation parameters take out bit by bit parts of the generated DDL. After running the above anonymous block we will get the following result.
That's more like it, just what I was after. The plain DDL for the EMP table.
One of the nice things is that you don't need to modify all the transformation parameters again to go back to the default. They made it really easy to return to the default settings:
Documentation on DBMS_METADATA
Let's take a look at how you can use this. The DDL that I want to extract is the infamous EMP table, normally in the SCOTT schema.
SQL> create table emp 2 as 3 select * 4 from scott.emp 5 / Table created.
To make it a little more interesting, we also add the DEPT table and place some constraints on them.
SQL> create table dept 2 as 3 select * 4 from scott.dept 5 / Table created. SQL> SQL> alter table dept 2 add constraint dept_pk primary key (deptno) 3 / Table altered. SQL> SQL> alter table emp 2 add constraint emp_dept_fk foreign key (deptno) references dept (deptno) 3 / Table altered.
If you use DBMS_METADATA just like that, you might get more than you asked for.
SQL> set long 5000
SQL> select dbms_metadata.get_ddl('TABLE', 'EMP')
2 from dual
3 /
DBMS_METADATA.GET_DDL('TABLE','EMP')
--------------------------------------------------------------------------------
CREATE TABLE "ALEX"."EMP"
( "EMPNO" NUMBER(4,0),
"ENAME" VARCHAR2(10),
"JOB" VARCHAR2(9),
"MGR" NUMBER(4,0),
"HIREDATE" DATE,
"SAL" NUMBER(7,2),
"COMM" NUMBER(7,2),
"DEPTNO" NUMBER(2,0),
CONSTRAINT "EMP_DEPT_FK" FOREIGN KEY ("DEPTNO")
REFERENCES "ALEX"."DEPT" ("DEPTNO") ENABLE
) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "USERS"
As you can in the code block above, you get the complete DDL including the storage clauses and the constraints. If this is not what you want, and I didn't want all this, than you need to modify some transformation parameters.
SQL> SQL> begin 2 dbms_metadata.set_transform_param (dbms_metadata.session_transform,'STORAGE',false); 3 dbms_metadata.set_transform_param (dbms_metadata.session_transform,'TABLESPACE',false); 4 dbms_metadata.set_transform_param (dbms_metadata.session_transform,'SEGMENT_ATTRIBUTES', false); 5 dbms_metadata.set_transform_param (dbms_metadata.session_transform,'REF_CONSTRAINTS', false); 6 dbms_metadata.set_transform_param (dbms_metadata.session_transform,'CONSTRAINTS', false); 7 end; 8 / PL/SQL procedure successfully completed.
Each of these transformation parameters take out bit by bit parts of the generated DDL. After running the above anonymous block we will get the following result.
SQL> select dbms_metadata.get_ddl ('TABLE', 'EMP')
2 from dual
3 /
DBMS_METADATA.GET_DDL('TABLE','EMP')
--------------------------------------------------------------------------------
CREATE TABLE "ALEX"."EMP"
( "EMPNO" NUMBER(4,0),
"ENAME" VARCHAR2(10),
"JOB" VARCHAR2(9),
"MGR" NUMBER(4,0),
"HIREDATE" DATE,
"SAL" NUMBER(7,2),
"COMM" NUMBER(7,2),
"DEPTNO" NUMBER(2,0)
)
That's more like it, just what I was after. The plain DDL for the EMP table.
One of the nice things is that you don't need to modify all the transformation parameters again to go back to the default. They made it really easy to return to the default settings:
SQL> begin 2 dbms_metadata.set_transform_param (dbms_metadata.session_transform, 'DEFAULT'); 3 end; 4 / PL/SQL procedure successfully completed. SQL>
Documentation on DBMS_METADATA
UPDATE July 10, 2013 (especially for Erhan Sarigul): Remove the Schema from the DDL-script
create or replace function remap_schema return clob is
-- Define local variables.
h number; --handle returned by OPEN
th number; -- handle returned by ADD_TRANSFORM
doc clob;
begin
-- Specify the object type.
h := dbms_metadata.open('TABLE');
-- Use filters to specify the particular object desired.
dbms_metadata.set_filter(h
,'SCHEMA'
,'ALEX');
dbms_metadata.set_filter(h
,'NAME'
,'EMP');
-- Request that the schema name be modified.
th := dbms_metadata.add_transform(h
,'MODIFY');
dbms_metadata.set_remap_param(th
,'REMAP_SCHEMA'
,'ALEX'
,null);
-- Request that the metadata be transformed into creation DDL.
th := dbms_metadata.add_transform(h
,'DDL');
-- Specify that segment attributes are not to be returned.
dbms_metadata.set_transform_param(th
,'SEGMENT_ATTRIBUTES'
,false);
-- Fetch the object.
doc := dbms_metadata.fetch_clob(h);
-- Release resources.
dbms_metadata.close(h);
return doc;
end remap_schema;
/
select remap_schema -- dbms_metadata.get_ddl ('TABLE','EMP')
from dual
/
Using the DBMS_METADATA API
06 October 2009
OPP 5: The Big Fat Book on PL/SQL
Yesterday I got the fifth edition of "Oracle PL/SQL Programming" by Steven Feuerstein. Another 1200+ pages on PL/SQL. This edition has been updated to cover versions through Oracle 11gR2.
It is loaded with the latest on Oracle PL/SQL Programming, hence the title.
When I started programming in PL/SQL I had a great mentor. When I told him I had a hard time writing PL/SQL, he advised me to get Feuerstein's book (the second edition was just out).
Immediately I loved it, couldn't put it down. I dragged this book everywhere. Reading it in the traffic jam on my way to work, reading it before I went to sleep. By the time I finished that book, people at work started asking me questions about PL/SQL. Next to my copy of the Second Edition are also the Third and the Fourth edition. And now, I need to clear some space on the shelve, the fifth edition will take it's place soon. After I'm finished reading it.
I'm sure this book is a must have for anyone who uses PL/SQL on a daily basis.
I did mention in an earlier blogpost that I was reviewing some chapters for an upcoming book, well it was this book. And needless to say I'm very proud that I was mentioned in the preface. But I have to say I just submitted the first errata. Not a biggie, but still... My lastname in the book was spelled Nuitjen, while it should be Nuijten. Oh well...
Update 22-10-2009:
My friend and colleague Patrick Barel pointed out that Google knows who I am ;)
It is loaded with the latest on Oracle PL/SQL Programming, hence the title.
When I started programming in PL/SQL I had a great mentor. When I told him I had a hard time writing PL/SQL, he advised me to get Feuerstein's book (the second edition was just out).
Immediately I loved it, couldn't put it down. I dragged this book everywhere. Reading it in the traffic jam on my way to work, reading it before I went to sleep. By the time I finished that book, people at work started asking me questions about PL/SQL. Next to my copy of the Second Edition are also the Third and the Fourth edition. And now, I need to clear some space on the shelve, the fifth edition will take it's place soon. After I'm finished reading it.
I'm sure this book is a must have for anyone who uses PL/SQL on a daily basis.
I did mention in an earlier blogpost that I was reviewing some chapters for an upcoming book, well it was this book. And needless to say I'm very proud that I was mentioned in the preface. But I have to say I just submitted the first errata. Not a biggie, but still... My lastname in the book was spelled Nuitjen, while it should be Nuijten. Oh well...
Update 22-10-2009:
My friend and colleague Patrick Barel pointed out that Google knows who I am ;)
28 September 2009
Planboard Symposium: Registration open
For the fourth time the Planboard Symposium will be held on November 17. This symposium is unique as it's "for DBA by DBA". This one day symposium will have 5 parallel sessions with lots of time for networking and open discussions.
I am lucky enough to be one of the presentors and my topic will be "Continuous Database Application Evolution in Oracle Database 11g Release 2 ", a mouth full.
This session will discuss a new feature of Oracle 11gR2: Edition Based Redefinition. Not just "a new feature", it's "The new feature".
You can register on the Planboard Symposium site
Oh, and by the way... don't tell anyone I'm not a DBA... ssshhh...
see you there.
I am lucky enough to be one of the presentors and my topic will be "Continuous Database Application Evolution in Oracle Database 11g Release 2 ", a mouth full.
This session will discuss a new feature of Oracle 11gR2: Edition Based Redefinition. Not just "a new feature", it's "The new feature".
You can register on the Planboard Symposium site
Oh, and by the way... don't tell anyone I'm not a DBA... ssshhh...
see you there.
24 September 2009
Carrying down values with Analytic Functions
The other day - yesterday actually - I got an email with a question regarding Analytic Functions.
This was the requirement for the query:
Let's take a look at an example to clarify the requirement. First of all the data in the table:
As you can see in the above output, there are two CODES ("A" and "B"). The combination of CODE and STARTDATE is unique. The last column (VAL) has some values filled out, not all.
The desired output would be:
When the VAL column has no entry (IS NULL) the output should show the latest value of the VAL column - based on the Startdate within the CODE. Latest in this case means: the most recent value of the VAL column with regards to the STARTDATE column of the current record.
Is the requirement clear? Time to solve it. I will show you two ways of solving this, there are possibly many more ways of getting the required output. One way is quite cumbersome, but will work in Oracle 8.1.6 EE and up. The other way is really trivial and works in Oracle 10g and up.
First method, fully explained.
Per CODE, we need to identify which VAL should be carried down, until a different VAL is encountered. To identify these subgroups we use a Case statement:
Now the column NEW_VAL shows the marker for each subgroup. Using the "running total" technique we can clearly see which records belong together.
With the distinction made - you can see the different subgroups, each having a unique number within the partition, we need the first value of the VAL column per subgroup. For that we will use the FIRST_VALUE function. Notice that the partitioning clause in the FIRST_VALUE is using the subgroups we defined in the previous section as well as the CODE.
And there you have it, the required result. But it takes a lot of typing. In Oracle 10g we can get the same results, but a lot simpler.
Newly added in Oracle 10g is the IGNORE NULLS clause. And it does exactly what it says, it ignores nulls.
In the result we want to get the last value, per partition within the window that gets larger with the current row. The default windowing clause is Rows Unbounded Preceding (the docs say Range Unbounded Preceding, but I believe this is wrong. Range only works for a numeric offset like with Dates and Numbers)
In the following query, I inserted the windowing clause in there. Just to be very explicit.
As the Windowing Clause is the default, you can also omit it.
This was the requirement for the query:
In a table there are a CODE, STARTDATE and VAL columns. The combination of CODE and STARTDATE are mandatory, the VAL is optional. I want to get an overview where the VAL column shows the latest (based on the STARTDATE) value. If there is no value filled out for a particular date, it should show the value of the latest entry for that particular CODE.
Let's take a look at an example to clarify the requirement. First of all the data in the table:
COD STARTDATE VAL
--- --------- ----------
A 24-SEP-08 QRS
A 24-OCT-08
A 24-NOV-08
A 24-DEC-08 a
A 24-JAN-09
A 24-FEB-09 XY
A 24-MAR-09 ABC
A 24-APR-09
A 24-MAY-09
B 24-DEC-08 BLA
B 24-JAN-09
B 24-FEB-09
B 24-MAR-09 BLABLA
B 24-APR-09
As you can see in the above output, there are two CODES ("A" and "B"). The combination of CODE and STARTDATE is unique. The last column (VAL) has some values filled out, not all.
The desired output would be:
COD STARTDATE VAL
--- --------- ------
A 24-SEP-08 QRS
A 24-OCT-08 QRS
A 24-NOV-08 QRS
A 24-DEC-08 a
A 24-JAN-09 a
A 24-FEB-09 XY
A 24-MAR-09 ABC
A 24-APR-09 ABC
A 24-MAY-09 ABC
B 24-DEC-08 BLA
B 24-JAN-09 BLA
B 24-FEB-09 BLA
B 24-MAR-09 BLABLA
B 24-APR-09 BLABLA
When the VAL column has no entry (IS NULL) the output should show the latest value of the VAL column - based on the Startdate within the CODE. Latest in this case means: the most recent value of the VAL column with regards to the STARTDATE column of the current record.
Is the requirement clear? Time to solve it. I will show you two ways of solving this, there are possibly many more ways of getting the required output. One way is quite cumbersome, but will work in Oracle 8.1.6 EE and up. The other way is really trivial and works in Oracle 10g and up.
First method, fully explained.
Per CODE, we need to identify which VAL should be carried down, until a different VAL is encountered. To identify these subgroups we use a Case statement:
SQL> select code
2 , startdate
3 , val
4 , case
5 when val is not null
6 then 1
7 end new_val
8 from tbl
9 order by code
10 , startdate
11 /
COD STARTDATE VAL NEW_VAL
--- --------- ---------- ----------
A 24-SEP-08 QRS 1
A 24-OCT-08
A 24-NOV-08
A 24-DEC-08 a 1
A 24-JAN-09
A 24-FEB-09 XY 1
A 24-MAR-09 ABC 1
A 24-APR-09
A 24-MAY-09
B 24-DEC-08 BLA 1
B 24-JAN-09
B 24-FEB-09
B 24-MAR-09 BLABLA 1
B 24-APR-09
Now the column NEW_VAL shows the marker for each subgroup. Using the "running total" technique we can clearly see which records belong together.
SQL> select code
2 , startdate
3 , val
4 , sum (case
5 when val is not null
6 then 1
7 end
8 ) over (partition by code
9 order by startdate
10 ) new_val
11 from tbl
12 order by code
13 , startdate
14 /
COD STARTDATE VAL NEW_VAL
--- --------- ---------- ----------
A 24-SEP-08 QRS 1
A 24-OCT-08 1
A 24-NOV-08 1
A 24-DEC-08 a 2
A 24-JAN-09 2
A 24-FEB-09 XY 3
A 24-MAR-09 ABC 4
A 24-APR-09 4
A 24-MAY-09 4
B 24-DEC-08 BLA 1
B 24-JAN-09 1
B 24-FEB-09 1
B 24-MAR-09 BLABLA 2
B 24-APR-09 2
With the distinction made - you can see the different subgroups, each having a unique number within the partition, we need the first value of the VAL column per subgroup. For that we will use the FIRST_VALUE function. Notice that the partitioning clause in the FIRST_VALUE is using the subgroups we defined in the previous section as well as the CODE.
SQL> select code
2 , startdate
3 , first_value (val)
4 over (partition by code
5 , new_val
6 order by startdate
7 ) new_val
8 from (
9 select code
10 , startdate
11 , val
12 , sum (case
13 when val is not null
14 then 1
15 end
16 ) over (partition by code
17 order by startdate
18 ) new_val
19 from tbl
20 )
21 order by code
22 , startdate
23 /
COD STARTDATE NEW_VAL
--- --------- ----------
A 24-SEP-08 QRS
A 24-OCT-08 QRS
A 24-NOV-08 QRS
A 24-DEC-08 a
A 24-JAN-09 a
A 24-FEB-09 XY
A 24-MAR-09 ABC
A 24-APR-09 ABC
A 24-MAY-09 ABC
B 24-DEC-08 BLA
B 24-JAN-09 BLA
B 24-FEB-09 BLA
B 24-MAR-09 BLABLA
B 24-APR-09 BLABLA
And there you have it, the required result. But it takes a lot of typing. In Oracle 10g we can get the same results, but a lot simpler.
Newly added in Oracle 10g is the IGNORE NULLS clause. And it does exactly what it says, it ignores nulls.
In the result we want to get the last value, per partition within the window that gets larger with the current row. The default windowing clause is Rows Unbounded Preceding (the docs say Range Unbounded Preceding, but I believe this is wrong. Range only works for a numeric offset like with Dates and Numbers)
In the following query, I inserted the windowing clause in there. Just to be very explicit.
SQL> select code
2 , startdate
3 , val
4 , last_value (val ignore nulls)
5 over (partition by code
6 order by startdate
7 rows between unbounded preceding
8 and current row
9 ) new_val
10 from tbl
11 ;
COD STARTDATE VAL NEW_VAL
--- --------- ---------- ----------
A 24-SEP-08 QRS QRS
A 24-OCT-08 QRS
A 24-NOV-08 QRS
A 24-DEC-08 a a
A 24-JAN-09 a
A 24-FEB-09 XY XY
A 24-MAR-09 ABC ABC
A 24-APR-09 ABC
A 24-MAY-09 ABC
B 24-DEC-08 BLA BLA
B 24-JAN-09 BLA
B 24-FEB-09 BLA
B 24-MAR-09 BLABLA BLABLA
B 24-APR-09 BLABLA
As the Windowing Clause is the default, you can also omit it.
SQL> select code
2 , startdate
3 , last_value (val ignore nulls)
4 over (partition by code
5 order by startdate
6 ) new_val
7 from tbl
8 ;
COD STARTDATE NEW_VAL
--- --------- ----------
A 24-SEP-08 QRS
A 24-OCT-08 QRS
A 24-NOV-08 QRS
A 24-DEC-08 a
A 24-JAN-09 a
A 24-FEB-09 XY
A 24-MAR-09 ABC
A 24-APR-09 ABC
A 24-MAY-09 ABC
B 24-DEC-08 BLA
B 24-JAN-09 BLA
B 24-FEB-09 BLA
B 24-MAR-09 BLABLA
B 24-APR-09 BLABLA
29 July 2009
DELETE in the MERGE statement
The Merge statement was introduced in Oracle 9i and improved upon in Oracle 10g.
In Oracle 9i only the INSERT and UPDATE parts were supported, in Oracle 10g DELETE was added. The "merge_update_clause" and "merge_insert_clause" became optional.
The basic syntax for the MERGE statement:
DELETE can only occur in the "merge_update_clause" of the above schema. This means that it must occur in the WHEN MATCHED THEN clause.
Until recent, I missed this part of the description of the "merge_update_clause" concerning the DELETE operation. First I will show you what I thought, then I'll show you where the behavior is documented.
First we'll create a table with two columns:
Notice that the last record, with RN 5, has an Ind "D".
Next we will merge a record into this table.
Only one row? Shouldn't that be two rows? One for the UPDATE and one for the DELETE?
Question for you: Which record(s) is (are) affected by this statement?
My wrong assumption was this:
Record with RN 3 has had the IND column changed to "D" and all records with IND "D" are removed. Effectively removing records with RN 3 and 5.
Now the quote from the documentation.
This means that records in the destination table are not deleted when they are not updated by the MERGE first.
As you can see the record with RN 5 is still in the table. Because it was not updated in the merge, it was not deleted.
In order to remove some records from the table using the MERGE statement, you need to update these records first. It is not possible to dismiss the UPDATE statement from the MERGE:
... learn something every day.
documentation link
In Oracle 9i only the INSERT and UPDATE parts were supported, in Oracle 10g DELETE was added. The "merge_update_clause" and "merge_insert_clause" became optional.
The basic syntax for the MERGE statement:
DELETE can only occur in the "merge_update_clause" of the above schema. This means that it must occur in the WHEN MATCHED THEN clause.
Until recent, I missed this part of the description of the "merge_update_clause" concerning the DELETE operation. First I will show you what I thought, then I'll show you where the behavior is documented.
First we'll create a table with two columns:
SQL> select *
2 from v$version
3 /
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bi
PL/SQL Release 10.2.0.3.0 - Production
CORE 10.2.0.3.0 Production
TNS for IBM/AIX RISC System/6000: Version 10.2.0.3.0 - Productio
NLSRTL Version 10.2.0.3.0 - Production
SQL>
SQL> create table t
2 as
3 select rownum rn
4 , 'A' ind
5 from all_objects
6 where rownum <= 5
7 /
Table created.
SQL> update t
2 set ind = 'D'
3 where rn = 5
4 /
1 row updated.
SQL>
SQL> select *
2 from t
3 /
RN I
---------- -
1 A
2 A
3 A
4 A
5 D
Notice that the last record, with RN 5, has an Ind "D".
Next we will merge a record into this table.
SQL> merge into t
2 using (select 3 i
3 , 'D' ind
4 from dual
5 ) dat
6 on (t.rn = dat.i)
7 when matched then
8 update set t.ind = dat.ind
9 delete where t.ind = 'D' --<--- Here is the DELETE
10 /
1 row merged.
Only one row? Shouldn't that be two rows? One for the UPDATE and one for the DELETE?
Question for you: Which record(s) is (are) affected by this statement?
My wrong assumption was this:
Record with RN 3 has had the IND column changed to "D" and all records with IND "D" are removed. Effectively removing records with RN 3 and 5.
Now the quote from the documentation.
The only rows affected by this clause are those rows in the destination table that are updated by the merge operation.
This means that records in the destination table are not deleted when they are not updated by the MERGE first.
SQL> select *
2 from t
3 /
RN I
---------- -
1 A
2 A
4 A
5 D
As you can see the record with RN 5 is still in the table. Because it was not updated in the merge, it was not deleted.
In order to remove some records from the table using the MERGE statement, you need to update these records first. It is not possible to dismiss the UPDATE statement from the MERGE:
SQL> merge into t
2 using (select 3 i
3 , 'D' ind
4 from dual
5 ) dat
6 on (t.rn = dat.i)
7 when matched then
8 delete where t.ind = 'D' --<--- Here is the DELETE
9 /
delete where t.ind = 'D' --<--- Here is the DELETE
*
ERROR at line 8:
ORA-00905: missing keyword
... learn something every day.
documentation link
Subscribe to:
Posts (Atom)