The SQL loader utility generates the wrong control file syntax in the case of loading into a specific table partition.
Getting Error:Sql loader fails with:
- SQL*Loader-350: Syntax error at line 7.
- Expecting valid column specification, "," or ")", found "P01".
Steps to replicate:
1. Create partitioned table use following script for a testcase:
CREATE TABLE TMP_SQLLOADER (
part_key VARCHAR2(3),
data_value VARCHAR2(10)
)
PARTITION BY LIST (part_key) (
PARTITION P01 VALUES ('001'),
PARTITION P02 VALUES ('002')
)
2. Create a file using notepad c:\p01-data.csv
With following data:
001,"RECORD 001"
001,"RECORD 002"
001,"RECORD 003"
001,"RECORD 004"
001,"RECORD 005"
3. Choose in the menu: "Database | Import | SQL*Loader Wizard"
Make sure you choose "Build control file", and "Specify fields"
Press [Next], then choose [Add ...], and enter as follows:
Input file name: c:\p01-data.csv
Bad file name: c:\p01-data.bad
Discard file name: c:\p01-data.dsc
Record format: Stream
Choose [OK]
Choose [Next]
Enter as follows:
Delimited
String: ,
Choose [Next]
Under Tables, choose [Add ...], and enter as follows:
Choose the Table TMP_SQLLOADER
Select Partition, and enter partition P01
Load method: TRUNCATE
Terminated By: ,
Enclosed By: " and: (leave blank)
Turn on 'Optionally'
Choose [OK]
Under Field Mapping, Choose [Auto map]
Choose [Next]
Choose [Next]
Fill in as follows:
Control file name: c:\p01-data.ctl
Log file name: c:\p01-data.log
Choose [Next]
Turn on Execute now, Watch progress
Press [Finish]
<
