Skip to Main Content

Oracle Database Discussions

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

How to avoid few data when using sql*loader with Truncate

633450Oct 27 2008 — edited Oct 27 2008
Hi,
I am using sql*loader to load data everyday for some incoming file using Truncate option (reloading table) and also running.
Now when i trucate this table to load incoming file then my seperate loaded sql data (from my insert after sql*loader) also get truncated and i have to run again insert sql everyday.
My seperate sql'a pk id column starts with special char so i know that these are from my seperate isnert sqls.
Now how can i use the sql*loader so when i truncate/delete the table then i keep the seperate sql'a pk id column data which starts with special char and load only incoming file?
My table structure and sql*loader is:

CREATE TABLE CODE
(
ID VARCHAR2(40 BYTE) NOT NULL,
CODE_NAME VARCHAR2(5 BYTE) NOT NULL,
CODE_VALUE VARCHAR2(17 BYTE),
CTL VARCHAR2(6 BYTE),
DESC VARCHAR2(20 BYTE) NOT NULL,
JOB VARCHAR2(1 BYTE),
TERM VARCHAR2(1 BYTE),
CONSTRAINT CODE_PK PRIMARY KEY (ID)
SQL*LOADER:
---------
load data
infile DATA2.txt'
truncate
into table CODE
(id recnum,
code_name position(1:5),
code_value position(6:22),
ctl position(23:28),
desc position(29:48),
JOB position(49:49),
TERM position(50:50)
)

example Data:

1 EC01 B1 Beta J T
2 EC01 B2 Beta J T
3 EC01 B3 Beta J T
4 EC01 C1 test J T
5 EC01 C2 test J T
6 EC01 C3 test J T
7 EC01 C4 test J T
8 EC01 C5 test J T
9 EC01 C6 test J T
P1 EC01 PR1 MAX J T
P2 EC01 PR2 MAX J T
P3 EC03 PR3 MAX J T

==> P1, P2, P3... data which is i am loading after sql*loader truncate the table and load new file.
I want to keep this P1, P2, P3 data when sql*loader truncate the table and reload new file each time.

Thanks,
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Nov 24 2008
Added on Oct 27 2008
1 comment
196 views