Home » RDBMS Server » Server Utilities » Direct Load with nextval clause in Control file
Direct Load with nextval clause in Control file [message #131778] |
Tue, 09 August 2005 08:17 |
puneetsachar
Messages: 97 Registered: March 2005 Location: New Delhi, India
|
Member |
|
|
Hi Group,
Hopw to find everyone in best of Health and Sprits.
Hei Following is my control File
LOAD DATA
INFILE 'TEMP_DEPARTMENT.csv'
TRUNCATE
INTO TABLE test_temp_departmentHCRN
FIELDS TERMINATED BY "," OPTIONALLY ENCLOSED BY '"'
TRAILING NULLCOLS
(
business_org
, baes_site
, emp_cat
, baes_department
, baes_cost_centre
, deleted
, reason
, username
, department_id "department_id.nextval"
, loaded sysdate
)
the problem is I have to use direct Load with SQLLDR and it says error
-->
SQL*Loader-417: SQL string (on column DEPARTMENT_ID) not allowed in direct path.
Can anyone help me Please.. in this.
Regards
Puneet
|
|
|
Re: Direct Load with nextval clause in Control file [message #131780 is a reply to message #131778] |
Tue, 09 August 2005 08:28 |
|
Mahesh Rajendran
Messages: 10708 Registered: March 2002 Location: oracleDocoVille
|
Senior Member Account Moderator |
|
|
Try this
LOAD DATA
INFILE 'TEMP_DEPARTMENT.csv'
TRUNCATE
INTO TABLE test_temp_departmentHCRN
FIELDS TERMINATED BY "," OPTIONALLY ENCLOSED BY '"'
TRAILING NULLCOLS
(
business_org
, baes_site
, emp_cat
, baes_department
, baes_cost_centre
, deleted
, reason
, username
, department_id char "department_id.nextval"
, loaded sysdate
)
|
|
|
|
|
|
Re: Direct Load with nextval clause in Control file [message #131795 is a reply to message #131789] |
Tue, 09 August 2005 09:08 |
|
Mahesh Rajendran
Messages: 10708 Registered: March 2002 Location: oracleDocoVille
|
Senior Member Account Moderator |
|
|
You cannot use this for direct load.
Did you try the SEQUENCE option in sql*loader?
Quoting docs
> 1) -- Variable-length, delimited, and enclosed data format
> LOAD DATA
> 2) INFILE *
> 3) APPEND
> INTO TABLE emp
> 4) FIELDS TERMINATED BY "," OPTIONALLY ENCLOSED BY '"'
> (empno, ename, job, mgr,
> 5) hiredate DATE(20) "DD-Month-YYYY",
> sal, comm, deptno CHAR TERMINATED BY ':',
> projno,
> 6) loadseq SEQUENCE(MAX,1))
> 7) BEGINDATA
> 8) 7782, "Clark", "Manager", 7839, 09-June-1981, 2572.50,, 10:101
> 7839, "King", "President", , 17-November-1981,5500.00,,10:102
> 7934, "Miller", "Clerk", 7782, 23-January-1982, 920.00,, 10:102
> 7566, "Jones", "Manager", 7839, 02-April-1981, 3123.75,, 20:101
> 7499, "Allen", "Salesman", 7698, 20-February-1981, 1600.00,
> (same line continued) 300.00, 30:103
> 7654, "Martin", "Salesman", 7698, 28-September-1981, 1312.50,
> (same line continued) 1400.00, 3:103
> 7658, "Chan", "Analyst", 7566, 03-May-1982, 3450,, 20:101
>
> Notes:
>
> 1. Comments may appear anywhere in the command lines of the file, but they should not appear in data. They are preceded with two hyphens that may appear anywhere on a line.
> 2. INFILE * specifies that the data is found at the end of the control file.
> 3. APPEND specifies that the data can be loaded even if the table already contains rows. That is, the table need not be empty.
> 4. The default terminator for the data fields is a comma, and some fields may be enclosed by double quotation marks (").
> 5. The data to be loaded into column hiredate appears in the format DD-Month-YYYY. The length of the date field is specified to have a maximum of 20. The maximum length is in bytes, with default byte-length semantics. If character-length semantics were used instead, the length would be in characters. If a length is not specified, then the length depends on the length of the date mask.
> 6. The SEQUENCE function generates a unique value in the column loadseq. This function finds the current maximum value in column loadseq and adds the increment (1) to it to obtain the value for loadseq for each row inserted.
> 7. BEGINDATA specifies the end of the control information and the beginning of the data.
> 8. Although each physical record equals one logical record, the fields vary in length, so that some records are longer than others. Note also that several rows have null values for comm.
|
|
|
|
Re: Direct Load with nextval clause in Control file [message #131813 is a reply to message #131807] |
Tue, 09 August 2005 10:07 |
|
Mahesh Rajendran
Messages: 10708 Registered: March 2002 Location: oracleDocoVille
|
Senior Member Account Moderator |
|
|
may be. I am not sure.
For the very same reason, we use some workaround (an online webapplication that loads 1-3 gb worth of data) when needed to create sequential numbers with direct load option. Long story short, we manipulate the datafile to have sequential numbers that are created by perl ( prefetched from database sequences).
|
|
|
Goto Forum:
Current Time: Wed Dec 25 19:43:12 CST 2024
|