Home » RDBMS Server » Server Utilities » Direct Load with nextval clause in Control file
icon5.gif  Direct Load with nextval clause in Control file [message #131778] Tue, 09 August 2005 08:17 Go to next message
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 Go to previous messageGo to next message
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 #131782 is a reply to message #131780] Tue, 09 August 2005 08:37 Go to previous messageGo to next message
puneetsachar
Messages: 97
Registered: March 2005
Location: New Delhi, India
Member

thanks for reply

its not working still same error.

I guess we cannot use nextval with direct load..

But i have to use..... both of them any solutions

Puneet
Re: Direct Load with nextval clause in Control file [message #131786 is a reply to message #131782] Tue, 09 August 2005 08:47 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10708
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
true.
My apologies.
>>I have to use direct Load
Seems that i have ignored that.
Re: Direct Load with nextval clause in Control file [message #131789 is a reply to message #131786] Tue, 09 August 2005 08:55 Go to previous messageGo to next message
puneetsachar
Messages: 97
Registered: March 2005
Location: New Delhi, India
Member

Hi,

do you know how to use with direct load...
please its urgent with my requirnment.

any other solutions for it

Puneet
Re: Direct Load with nextval clause in Control file [message #131795 is a reply to message #131789] Tue, 09 August 2005 09:08 Go to previous messageGo to next message
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 #131807 is a reply to message #131795] Tue, 09 August 2005 09:43 Go to previous messageGo to next message
puneetsachar
Messages: 97
Registered: March 2005
Location: New Delhi, India
Member

Hi

Thanks a ton

Is there is any other way to do it, though this works fine

The strange thing I come across with this is
With direct Load my time is 00.00.00.33

and with conventional load the time is 00.00.00.26

why this strange behaviour.. is it cos of SEQUENCE field

Puneet
Re: Direct Load with nextval clause in Control file [message #131813 is a reply to message #131807] Tue, 09 August 2005 10:07 Go to previous message
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).
Previous Topic: SQL*LOADER: Load multiple tables with same record/Fields
Next Topic: imp-00010 error
Goto Forum:
  


Current Time: Wed Dec 25 19:43:12 CST 2024