Skip to Main Content

Breadcrumb

Question and Answer

Tom Kyte

Thanks for the question, John.

Asked: October 20, 2002 - 2:40 pm UTC

Last updated: November 11, 2002 - 6:44 pm UTC

Version: 9.2

Viewed 1000+ times

You Asked

i want to load employee information to our data from staging tables

like i have a table

employee
employee_stag -- data loaded here thru sql loader fill and flush.

now i want to write a proc that updates the changes only to the employee table from employee_stag

q1) is there any way to update only changes differences between these tables

i am planning to write something simular to this:

update employee a
set all the employee columns (select all columns from employee stag b where a.emplid = b.emplid);

insert into employee select * from employee_stag stg where not exists
(select 1 from employee em where em.emplid = stg.emplid)


any suggestions or corrections?

we will have around 50,000 active employees

q2) how do i lock employee table as application developers may be accessing the employee table(to select or update or delete)?

now i am loading employee_stag thru sql loader
i heard new concept of external tables.

q3)
can we schedule loading the data file to the external tables thru oracle once in a day?

q4)the file name we mention for the data file to be loaded in access parameters of external table,can be in any unix directory or is there any init.ora parameter to be mentioned?

would really appreciate ur reply.

thanks


and Tom said...

q1) I would

a) skip sqlldr
b) skip the staging table
c) merge

See
</code> http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:1615330671789 <code>

you can MERGE the contents of that flat file with the EMP table in one statement.


q2) lock table employee in exclusive mode;
merge it with the external table

q3) dbms_job will do that -- it is used to run a procedure on a recurring basis.

q4) the filename needs to be a file in an Oracle "directory" object (see the example above). they can be created whilst the database is up and running (must be up and running) and do not need init.ora parameter changes at all.

Rating

  (5 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

looks wonderful to me -- an error with merge

A reader, October 20, 2002 - 4:24 pm UTC 

tom,

thanks so much for such a wonderful reply.
i need to have permission for "create or replace" directory
so, for the time being i tested the merge with a staging table it gives me an error does it work with only external tables

SSQL*Plus: Release 8.1.6.0.0 - Production on Sun Oct 20 16:17:30 2002

(c) Copyright 1999 Oracle Corporation.  All rights reserved.


Connected to:
Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.1.0 - Production

SQL> create or replace PROCEDURE em_personal_update AS
  2  BEGIN
  3     MERGE INTO emp em
  4     USING emp stg
  5     ON (stg.emplid = em.emplid)
  6     WHEN matched THEN
  7        UPDATE SET em.address1   = stg.address1,
  8                   em.address2   = stg.address2,
  9                   em.address3   = stg.address3
 10     WHEN NOT matched THEN
 11        INSERT (em.emplid,em.address1,em.address2,em.address3)
 12        VALUES (em.emplid,em.address1,em.address2,em.address3);
 13  END em_personal_update;
 14  /

Warning: Procedure created with compilation errors.

SQL> show err
Errors for PROCEDURE EM_PERSONAL_UPDATE:

LINE/COL ERROR
-------- -----------------------------------------------------------------
3/4      PL/SQL: ORA-00904: "EM"."EMPLID": invalid identifier
3/4      PL/SQL: SQL Statement ignored

Tom Kyte
October 20, 2002 - 4:34 pm UTC

it works with any table, view, subquery...

but I suspect it is your correlation names int he INSERT clause:

10 WHEN NOT matched THEN
11 INSERT ( emplid, address1, address2, address3)
12 VALUES (em.emplid,em.address1,em.address2,em.address3);

they do not belong there.

thanks again.. that is my mistake..

John, October 20, 2002 - 4:40 pm UTC

i have a 9i feature added to my code.
thanks to tom.

merge -- delete

A reader, October 20, 2002 - 5:07 pm UTC

tom,

i want to delete the rows that are not present in external table and but are in main table. how do i do this?

Tom Kyte
October 20, 2002 - 5:54 pm UTC

like this:

scott@ORA920.US.ORACLE.COM> create or replace directory data_dir as '/tmp/'
2 /

Directory created.

scott@ORA920.US.ORACLE.COM>
scott@ORA920.US.ORACLE.COM> create table external_table
2 (EMPNO NUMBER(4) ,
3 ENAME VARCHAR2(10),
4 JOB VARCHAR2(9),
5 MGR NUMBER(4),
6 HIREDATE DATE,
7 SAL NUMBER(7, 2),
8 COMM NUMBER(7, 2),
9 DEPTNO NUMBER(2)
10 )
11 ORGANIZATION EXTERNAL
12 ( type oracle_loader
13 default directory data_dir
14 access parameters
15 ( fields terminated by ',' )
16 location ('emp.dat')
17 )
18 /

Table created.

scott@ORA920.US.ORACLE.COM>
scott@ORA920.US.ORACLE.COM> host flat scott/tiger emp > /tmp/emp.dat

scott@ORA920.US.ORACLE.COM>
scott@ORA920.US.ORACLE.COM> set autotrace on

scott@ORA920.US.ORACLE.COM> delete from emp
2 where empno not in ( select /*+ HASH_AJ */ empno from external_table where empno is not null )
3 /

0 rows deleted.


Execution Plan
----------------------------------------------------------
0 DELETE STATEMENT Optimizer=CHOOSE (Cost=14 Card=1 Bytes=26)
1 0 DELETE OF 'EMP'
2 1 HASH JOIN (ANTI) (Cost=14 Card=1 Bytes=26)
3 2 TABLE ACCESS (FULL) OF 'EMP' (Cost=2 Card=82 Bytes=1066)
4 2 EXTERNAL TABLE ACCESS (FULL) OF 'EXTERNAL_TABLE' (Cost=11 Card=408 Bytes=5304)


You want the hash anti join to avoid full scanning the external table once for each row in emp and since there won't be stats on it, we can help the SQL along with the hint in this case....

see also

</code> http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:5081797376327 <code>

for "what is this hash anti join thing"







conditional merge

John, November 11, 2002 - 11:11 am UTC

can we do a conditional merge?

i want to leave the records that are changed

MERGE INTO ps_emergency_contact ec
USING ps_emergency_contact_stag stg
ON (stg.emplid = ec.emplid AND
stg.contact_name = ec.contact_name AND
ec.ps_change_code != 'A')
WHEN matched THEN
-- update
WHEN NOT matched THEN
-- insert

the condition ec.ps_change_code != 'A' raises runtime error
saying invalid identifier.

i want to leave the records whose change_code = 'A'


Tom Kyte
November 11, 2002 - 6:29 pm UTC

No -- and it would be really bad if there was. Consider, if you had a primary key AND the pc_change_code != 'A' was "met" (eg there is an A record in both the current table AND the table to merge with) -- you would end up with a DUPLICATION key condition (they would NOT be matched, so it would be inserted -- but the primary key already exists of course so it would fail)

You can see this in your case by creating a view V as select * from ps_emergency_contact where ps_change_code != 'A' and merging into the view -- you'll get the dup val on index.

Maybe you want to

when matched then
update set ec.c1 = decode(ec.ps_change_code,'A',ec.c1, stg.c1 ),
........




thank you

John, November 11, 2002 - 6:44 pm UTC

tom,

thanks for pointing out that duplicate condtion..
decode looks good.

more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's channel and Chris's channel from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library