Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

SQL*Loader question 1

Status
Not open for further replies.
FORAND

FORAND

Programmer
May 26, 2003
64
CA
Hi!

I'm currently using SQL*Loader to insert data into a Oracle table.

Here is my sqlloader command :
Code: 
sqlldr / CONTROL=pfc_tmp_int_new.ctl LOG=tmp_int.log BAD=tmp_int.bad DATA=intv.dat ERRORS=2 DIRECT=TRUE>/dev/null

here is my table :
Code: 
TABLE TTMPINTPFC ( 
	INTCOD		 NUMBER,
	MSGIDF          CHAR    (16) not null,
	INTVINSTNUM    NUMBER       not null,
	INTVDATETIME    VARCHAR2(17),
	INTVSEQNB       VARCHAR2(15),
	INTVINTVNUM     VARCHAR2(5)  not null,
	INTVNAME        VARCHAR2(25),
	INTVCAT         VARCHAR2(30),
	INTVNICKNAME    VARCHAR2(10),
	INTVLOCATION    VARCHAR2(25),
	INTVPFNNAME     VARCHAR2(25),
	INTVAPPETIME    VARCHAR2(17),
	INTVAPPESEQ     VARCHAR2(15),
	INTVCHUNK       VARCHAR2(10),
	INTVCHUNKFL     VARCHAR2(10),
	INTVLENGTH      NUMBER,
	INTVTOKEN       VARCHAR2(10),
	INTVTEXT        VARCHAR2(200))

The problem is that some data in my file is WWAAYY bigger than one of my field (INTVTEXT). Sometimes it's 2000 caracter, and I've set my field for 200........

So, I basicly want to TRUNCATE my date so SQL*Loader can fit the first 200 into my database.

Here's my .ctl :
Code: 
LOAD DATA
INFILE *
TRUNCATE
INTO TABLE ttmpintpfc
FIELDS TERMINATED BY "," OPTIONALLY ENCLOSED BY '"'
                        (INTCOD         ,
                         MSGIDF         ,
                         INTVINSTNUM    ,
                         INTVDATETIME   ,
                         INTVSEQNB      ,
                         INTVINTVNUM    ,
                         INTVNAME       ,
                         INTVCAT        ,
                         INTVNICKNAME   ,
                         INTVLOCATION   ,
                         INTVPFNNAME    ,
                         INTVAPPETIME   ,
                         INTVAPPESEQ    ,
                         INTVCHUNK      ,
                         INTVCHUNKFL    ,
                         INTVLENGTH     ,
                         INTVTOKEN      ,
                         INTVTEXT       )

I have already set my .ctl to Truncate, but it does not work... can someone help ?

thanks a lot

FORAND

 
Sort by date Sort by votes
The "truncate" option you're using there refers to the table, not the individual columns.

There may be other ways of doing it, but one option might be to apply a SQL operator to the column:

INTVTEXT "SUBSTR:)INTVTEXT,1,200)"
 
Upvote 0 Downvote
Since when did this site start automatically interpreting text as smilies ?

Code: 
INTVTEXT "SUBSTR(:INTVTEXT,1,200)"
 
Upvote 0 Downvote
So, i just paste 'INTVTEXT "SUBSTR:)INTVTEXT,1,200)"' in my .ctl file instead of my 'normal' definition of INTVTEXT ?

 
Upvote 0 Downvote
Tried that^^ but I got this error msg :

Code: 
SQL*Loader-417: SQL string (on column INTVTEXT) not allowed in direct path.

anyone ?
 
Upvote 0 Downvote
Hold on... i think it might have worked... don't know why I got this error msg, but some data has been entered in my table.

 
Upvote 0 Downvote
Ok. So i changed my sqlldr command "DIRECT=FALSE". I'm trying it right now.
More to come :)
 
Upvote 0 Downvote
Ok. Here is the latest :

Looks like I'm back @ square 1.... I changed the DIRECT parameter to false, and changed my INTVTEXT definition in the .ctl to INTVTEXT "SUBSTR:)INTVTEXT,1,200)".
Here is the result :

Code: 
Number to load: ALL
Number to skip: 0
Errors allowed: 2
Bind array:     64 rows, maximum of 65536 bytes
Continuation:    none specified
Path used:      Conventional

Table TTMPINTPFC, loaded from every logical record.
Insert option in effect for this table: TRUNCATE

   Column Name                  Position   Len  Term Encl Datatype
------------------------------ ---------- ----- ---- ---- ---------------------
INTCOD                              FIRST     *   ,  O(") CHARACTER            
MSGIDF                               NEXT     *   ,  O(") CHARACTER            
INTVINSTNUM                          NEXT     *   ,  O(") CHARACTER            
INTVDATETIME                         NEXT     *   ,  O(") CHARACTER            
INTVSEQNB                            NEXT     *   ,  O(") CHARACTER            
INTVINTVNUM                          NEXT     *   ,  O(") CHARACTER            
INTVNAME                             NEXT     *   ,  O(") CHARACTER            
INTVCAT                              NEXT     *   ,  O(") CHARACTER            
INTVNICKNAME                         NEXT     *   ,  O(") CHARACTER            
INTVLOCATION                         NEXT     *   ,  O(") CHARACTER            
INTVPFNNAME                          NEXT     *   ,  O(") CHARACTER            
INTVAPPETIME                         NEXT     *   ,  O(") CHARACTER            
INTVAPPESEQ                          NEXT     *   ,  O(") CHARACTER            
INTVCHUNK                            NEXT     *   ,  O(") CHARACTER            
INTVCHUNKFL                          NEXT     *   ,  O(") CHARACTER            
INTVLENGTH                           NEXT     *   ,  O(") CHARACTER            
INTVTOKEN                            NEXT     *   ,  O(") CHARACTER            
INTVTEXT                             NEXT     *   ,  O(") CHARACTER            
    SQL string for column : "SUBSTR(:INTVTEXT,1,200)"

Record 1: Rejected - Error on table TTMPINTPFC, column INTVINSTNUM.
ORA-01722: invalid number

Record 1933: Rejected - Error on table TTMPINTPFC, column INTVTEXT.
Field in data file exceeds maximum length
Record 3420: Rejected - Error on table TTMPINTPFC, column INTVTEXT.
Field in data file exceeds maximum length

MAXIMUM ERROR COUNT EXCEEDED - Above statistics reflect partial run.

Table TTMPINTPFC:
  3417 Rows successfully loaded.
  3 Rows not loaded due to data errors.
  0 Rows not loaded because all WHEN clauses were failed.
  0 Rows not loaded because all fields were null.


Space allocated for bind array:                  65016 bytes(14 rows)
Space allocated for memory besides bind array:        0 bytes

Total logical records skipped:          0
Total logical records read:          3420
Total logical records rejected:         3
Total logical records discarded:        0


The first error is normal, but I don't get why SQLLoader is still trying to insert 3000 caracter in a 200 caracter field... even with the SUBSTR...

anyone ?
 
Upvote 0 Downvote
oups... sorry. I got the wrong log ;)
It actually works ;)
Big thanks to Dagon --->Star for ya!! :)

Forand
 
Upvote 0 Downvote
I tried the technique on my system and it worked fine, even in direct path mode. It may be your version of Oracle. If you're on Oracle 8, it may not be supported fully.

Here's what I had:

Code: 
create table test_load (a number, b varchar2(20));

Control file:

Code: 
LOAD DATA
REPLACE
INTO TABLE test_load
FIELDS TERMINATED BY ","
TRAILING NULLCOLS
(a,b "substr(:b,1,20)")

Data:

Code: 
1,jjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjj
2,dddss
3,jjjj
4,kkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkk

Log:


Code: 
   Column Name                  Position   Len  Term Encl Datatype
------------------------------ ---------- ----- ---- ---- ---------------------
A                                   FIRST     *   ,       CHARACTER
B                                    NEXT     *   ,       CHARACTER
    SQL string for column : "substr(:b,1,20)"


Table TEST_LOAD:
  4 Rows successfully loaded.
  0 Rows not loaded due to data errors.
  0 Rows not loaded because all WHEN clauses were failed.
  0 Rows not loaded because all fields were null.

 
Upvote 0 Downvote
Posted before I saw your last reply. Did you click on the "Thank ... for this valuable post" option because the star doesn't seem to have gone on the system ?
 
Upvote 0 Downvote
Oooh, Dagon, you shameless hussy, you ;-)

I want to be good, is that not enough?
 
Upvote 0 Downvote
I merely wanted to check that the correct procedures had been followed.
 
Upvote 0 Downvote
Yup, I gave you a star allright. You deserved it!
Thanks again
 
Upvote 0 Downvote
Sorry to tell you that, but it seems like it does not work. I checked and checked again, and here is the result :

Code: 
Number to load: ALL
Number to skip: 0
Errors allowed: 2
Bind array:     64 rows, maximum of 65536 bytes
Continuation:    none specified
Path used:      Conventional

Table TTMPINTPFC, loaded from every logical record.
Insert option in effect for this table: TRUNCATE

   Column Name                  Position   Len  Term Encl Datatype
------------------------------ ---------- ----- ---- ---- ---------------------
INTCOD                              FIRST     *   ,  O(") CHARACTER            
MSGIDF                               NEXT     *   ,  O(") CHARACTER            
INTVINSTNUM                          NEXT     *   ,  O(") CHARACTER            
INTVDATETIME                         NEXT     *   ,  O(") CHARACTER            
INTVSEQNB                            NEXT     *   ,  O(") CHARACTER            
INTVINTVNUM                          NEXT     *   ,  O(") CHARACTER            
INTVNAME                             NEXT     *   ,  O(") CHARACTER            
INTVCAT                              NEXT     *   ,  O(") CHARACTER            
INTVNICKNAME                         NEXT     *   ,  O(") CHARACTER            
INTVLOCATION                         NEXT     *   ,  O(") CHARACTER            
INTVPFNNAME                          NEXT     *   ,  O(") CHARACTER            
INTVAPPETIME                         NEXT     *   ,  O(") CHARACTER            
INTVAPPESEQ                          NEXT     *   ,  O(") CHARACTER            
INTVCHUNK                            NEXT     *   ,  O(") CHARACTER            
INTVCHUNKFL                          NEXT     *   ,  O(") CHARACTER            
INTVLENGTH                           NEXT     *   ,  O(") CHARACTER            
INTVTOKEN                            NEXT     *   ,  O(") CHARACTER            
INTVTEXT                             NEXT     *   ,  O(") CHARACTER            
    SQL string for column : "SUBSTR(:INTVTEXT,1,200)"

Record 1: Rejected - Error on table TTMPINTPFC, column INTVINSTNUM.
ORA-01722: invalid number

Record 15646: Rejected - Error on table TTMPINTPFC, column INTVTEXT.
Field in data file exceeds maximum length
Record 15662: Rejected - Error on table TTMPINTPFC, column INTVTEXT.
Field in data file exceeds maximum length

MAXIMUM ERROR COUNT EXCEEDED - Above statistics reflect partial run.

Table TTMPINTPFC:
  15661 Rows successfully loaded.
  3 Rows not loaded due to data errors.
  0 Rows not loaded because all WHEN clauses were failed.
  0 Rows not loaded because all fields were null.

Sorry about the confusion on my side. I am using Oracle 8... so that might be the problem. But I really need a solution for this .... ...


Are you up for it Dagon ? ;)


Just to be on the safe side, I modified the INTVTEXT field to a VARCHAR2(205).

any ideas?

 
Upvote 0 Downvote
Unfortunately, I don't have Oracle 8, so I can't test any solutions. You could maybe try playing around with some of the command line parameters like RECORDLENGTH. Failing that, all I can suggest is that you trim the file before loading it. Or, if the file is too big and there aren't too many long rows, allow all the errors to go to the bad file, trim that and load it.

 
Upvote 0 Downvote
Got something :

WHen I add the following code to Dagon's example, I reprodure the problem I have in my table :
Code: 
OPTIONALLY ENCLOSED BY '"'

HHHEELLPP please
 
Upvote 0 Downvote
I still can't reproduce it on my system. It must be Oracle 8 specific.
 
Upvote 0 Downvote
I managed to reproduce it this time. It looks as if SQL*Ldr has a limit of 255 characters on the size of any field. However, you can override this in the field specifications. I changed my control file to set the field size to 2000 and it worked.

Code: 
LOAD DATA
REPLACE
INTO TABLE test_load
FIELDS TERMINATED BY "," OPTIONALLY ENCLOSED BY '"'
TRAILING NULLCOLS
(a,b char(2000) "substr(:b,1,200)")
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

Andrzejek
  • Locked
  • Question
Get part of a string with SQL 1
Replies
2
Views
395
Andrzejek
Andrzejek
Livia8
  • Locked
  • Question
Question re: SELECT DISTINCT 1
Replies
6
Views
439
SkipVought
S
Livia8
  • Locked
  • Question
SQL SELECT - where date is null or greater than 2
Replies
9
Views
2K
carp
carp
annie1975
  • Locked
  • Question
sql script in an cron job script failing
Replies
3
Views
173
carp
carp
Livia8
  • Locked
  • Question
SQL script to find number of characters allowed in "Description" column of "AP_INVOIC 2
Replies
5
Views
195
Livia8
Livia8

Part and Inventory Search

Sponsor

Back
Top