Creating SQlldr CTL file with truncation at start.

[aliflood]

Hi

please find below A control file which I have loaded successfully - I now want to append into it -but have decided that each I run it I would rather trncate the table first and then load the data into it - how can I do this?

LOAD DATA
APPEND INTO TABLE px_extract
FIELDS TERMINATED BY "," OPTIONALLY ENCLOSED BY "'"
TRAILING NULLCOLS
(
payroll_no,
request_id,
product_code,
walsh_no,
request_date date "YYYY-MM-DD",
free_text char(2000),
priority,
high_date date "YYYY-MM-DD",
high_reason CHAR(2000),
deliver_to,
walsh_place_no,
bus_unit_id,
position_idn,
postal_address,
process_flag
)

 

[jaid89]

USe TRUNCATE instead of APPEND.

 

[Turkbear]

Hi,
One 'gotcha' with Truncate and some more info:

TRUNCATE
Using this method, SQL*Loader uses the SQL TRUNCATE statement to achieve the best possible performance. For the TRUNCATE statement to operate, the table's referential integrity constraints must first be disabled. If they have not been disabled, SQL*Loader returns an error.

Once the integrity constraints have been disabled, DELETE CASCADE is no longer defined for the table. If the DELETE CASCADE functionality is needed, then the contents of the table must be manually deleted before the load begins.

The table must be in your schema, or you must have the DELETE ANY TABLE privilege.

Note: Unlike the SQL TRUNCATE statement, this method reuses a table's extents.



Just a caution...

 

[aliflood]

So would I have to write 2 CTL files........
one to truncate the table and one to load the new data...
as when I try to do both in one - It just errors....

 

[THoey]

The following is an example of a control file that truncates and loads:

options(errors=10)
load data				-- Keywords to start SQL Loader
infile 'D:\FTP\SATR\satxref.txt'	-- Import file and path
into table AGENT_XREF TRUNCATE		-- Table to import into
     when (WORK_TYPE <> '')
(
 AGENT_ID    position(01:04)  CHAR,
 AGENT_NAME  position(05:24)  CHAR "INITCAP(:AGENT_NAME)",
 DIALER_ID   position(25:31)  CHAR "LOWER(:DIALER_ID)",
 SUPERVISOR  position(32:50)  CHAR "INITCAP(:SUPERVISOR)",
 WORK_TYPE   position(60:62)  CHAR,
 CENTER      CONSTANT 'SAT'
)

Terry
**************************
* General Disclaimer - Please read *
**************************
Please make sure your post is in the CORRECT forum, has a descriptive title, gives as much detail to the problem as possible, and has examples of expected results. This will enable me and others to help you faster...