Skip to Main Content

SQL & PL/SQL

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!

Need to truncate table before loading new data

costumerSep 3 2009 — edited Sep 3 2009
I am using a sqlldr control file to load a flat file to a table. This file will be provided monthly and is a full replace file. I need to automate the process to truncate the data already in the table before loading the new records. Can I do this in the same ctl file as the load or do I need a separate command file?

This is the current ctl file:

LOAD DATA
INFILE 'D:\DATA\OHNFILES\OHN_PROVIDER_DATA.TXT'
INSERT
PRESERVE BLANKS
INTO TABLE WEB.OHN_OK_SELECT
(
CHKPPO position (0001:0003),
EFFECTIVEDATE position (0004:0011),
TERMDATE position (0012:0019),
CRED_ID position (0020:0024),
LASTNAME position (0025:0099),
FIRSTNAME position (0100:0124),
MIDDLENAME position (0125:0149),
PROFESSIONALDEGREE position (0150:0164),
PRIMARYSPECIALTYID position (0165:0214),
PRIMARYSUBSPECIALTYID position (0215:0264),
SECONDARYSPECIALTY position (0265:0314),
OKLICENSENO position (0315:0329),
NPI position (0330:0339),
TAXIDNO position (0340:0348),
MEDICARENO position (0349:0365),
OFFICEADDRESS position (0366:0465),
BLDGSUITEPO_BOX position (0466:0500),
CITY position (0501:0525),
STATE position (0526:0527),
POSTALCODE position (0528:0537),
PHONENUMBER position (0538:0549),
FAXNUMBER position (0550:0561),
EMAILADDRESS position (0562:0611),
COUNTY position (0612:0631),
BILLINGADDRESS position (0632:0731),
BLDGSUITEPO_BOX1 position (0732:0766),
CITY1 position (0767:0791),
STATE1 position (0792:0793),
POSTALCODE1 position (0794:0803),
PHONE1 position (0804:0815),
FAX1 position (0816:0827),
EMAILADDRESS1 position (0828:0877),
MAILINGADDRESS position (0878:0977),
BLDGSUITEPO_BOX2 position (0978:1012),
CITY2 position (1013:1037),
STATE2 position (1038:1039),
POSTALCODE2 position (1040:1049),
PHONE2 position (1050:1061),
FAX2 position (1062:1073),
EMAILADDRESS2 position (1074:1123),
OFFICEMANAGER position (1124:1163),
CLINIC_GRPNAME position (1164:1263),
FEESCHEDULEID position (1264:1278),
FILLER position (1279:1400)
)
This post has been answered by Frank Kulash on Sep 3 2009
Jump to Answer
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Oct 1 2009
Added on Sep 3 2009
4 comments
26,825 views