sql loader control file syntax

Hello,

I search how to create a sql loader control file reference for load data to a table which has the following structure?

(t_id number(38,0) PRIMARY KEY,
 t_usersid number(38,0),
 trp_id VARCHAR2(254 BYTE)
 );

Thanks a lot

Regards

Bibi
bibi92Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

slightwv (䄆 Netminder) Commented:
There are TONS of examples out there on the Internet.

If you are unable to find them, please post sample data from the file you wish to load and expected results and we can probably create one for you.
0
Alexander Eßer [Alex140181]Software DeveloperCommented:
There are TONS of examples out there on the Internet.

hell, yeah ;-)

i.e. http://www.orafaq.com/wiki/SQL*Loader_FAQ
0
bibi92Author Commented:
Hello,

The data are in a csv file :
"5;4785884;TESTDBA"
"6;4785974;TESTDBA"
"7;4781074;TESTDBA"
"8;4715174;TESTDBA"

I try to load with the  sql loader control reference file

LOAD DATA
INFILE data_oib.csv
APPEND
INTO TABLE MIPRODLIVE.TEMP_MERGEOIB_DBA
FIELDS TERMINATED BY ';'
TRAILING NULLCOLS
(
        T_ID INTEGER,
        T_USERSID INTEGER,
        TRP_ID CHAR
)

No error are return when I execute sqlloader but when I select the table. The data are not corrected :

T_ID              T_USERSID TRP_ID
926169909     943207736 4

926169910     926496056 4

926169911     925905208 4

926169912     925971761 4

Thanks

Regards
0
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

slightwv (䄆 Netminder) Commented:
If I was actually going to post a link, it would be to the online docs first:
http://docs.oracle.com/cd/E11882_01/server.112/e22490/ldr_control_file.htm#SUTIL005
0
Alexander Eßer [Alex140181]Software DeveloperCommented:
T_ID              T_USERSID TRP_ID
926169909     943207736 4

926169910     926496056 4

926169911     925905208 4

926169912     925971761 4

Maybe I'm blind, but these values have nothing in common with those from the file?!?

Are there any triggers on that table?!
0
DavidSenior Oracle Database AdministratorCommented:
From SQL run and return results of:

DESCRIBE MIPRODLIVE.TEMP_MERGEOIB_DBA

You are not clear how the CSV sample in your first post, relates to your post-import sample.
0
slightwv (䄆 Netminder) Commented:
First problem is the double quotes surrounding the entire line.

The next problem is the INTEGER declaration in the control file.  It seems to be doing weird things with the values.

If you make the CHAR and remove the double quotes it works.

I'll work in the INTEGER issue.

If you cannot remove the double quotes around the entire line, I'll see if I can come up with a trick for that.

This control file works:
LOAD DATA
INFILE *
APPEND
INTO TABLE tab1
FIELDS TERMINATED BY ';'
TRAILING NULLCOLS
(
        T_ID CHAR,
        T_USERSID CHAR,
        TRP_ID CHAR
)
begindata
5;4785884;TESTDBA
6;4785974;TESTDBA
7;4781074;TESTDBA
8;4715174;TESTDBA

Open in new window

0
slightwv (䄆 Netminder) Commented:
>>Maybe I'm blind, but these values have nothing in common with those from the file?!?

Try setting up the test case and actually run it.

I was able to confirm the results.
0
slightwv (䄆 Netminder) Commented:
If you can remove the double quotes, define the intergers as external and it works:
LOAD DATA
INFILE *
APPEND
INTO TABLE tab1
FIELDS TERMINATED BY ';'
TRAILING NULLCOLS
(
        T_ID INTEGER EXTERNAL,
        T_USERSID INTEGER EXTERNAL,
        TRP_ID CHAR
)
begindata
5;4785884;TESTDBA
6;4785974;TESTDBA
7;4781074;TESTDBA
8;4715174;TESTDBA

Open in new window


Still working on the double quote issue.  This one might be tough.
0
Alexander Eßer [Alex140181]Software DeveloperCommented:
If you cannot remove the double quotes around the entire line, I'll see if I can come up with a trick for that.

what about
optionally enclosed by '"'

Open in new window

0
slightwv (䄆 Netminder) Commented:
>>optionally enclosed by '"'

Did you try it?  I did...

That is optional around individual fields.  Not for the entire line.

It would work if the data was:
"5";"4785884";"TESTDBA"

but not:
"5;4785884;TESTDBA"
0
Alexander Eßer [Alex140181]Software DeveloperCommented:
I know, it was just an idea though ;-)
But there has to be some kind of trick...
0
slightwv (䄆 Netminder) Commented:
This works with your test case as is.

The trick is to declare the ID as a CHAR in the control file and trim the double quotes.

LOAD DATA
INFILE *
APPEND
INTO TABLE tab1
FIELDS TERMINATED BY ';'
TRAILING NULLCOLS
(
        T_ID CHAR "TO_NUMBER(LTRIM(:T_ID,chr(34)))",
        T_USERSID INTEGER EXTERNAL,
        TRP_ID  "RTRIM(:TRP_ID,chr(34))"
)
begindata
"5;4785884;TESTDBA"
"6;4785974;TESTDBA"
"7;4781074;TESTDBA"
"8;4715174;TESTDBA"

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
awking00Information Technology SpecialistCommented:
You should also be able to use -
T_ID INTEGER EXTERNAL "REPLACE(:T_ID,chr(34))",
T_USERSID INTEGER EXTERNAL,
TRP_ID  "REPLACE(:TRP_ID,chr(34))"
0
slightwv (䄆 Netminder) Commented:
>>You should also be able to use -

True but there are MANY ways to do it, didn't see a need to post any of the alternatives.

That is unless you know REPLACE performs so must faster/more efficient than TRIM.

This can be an issue if you are loading millions of records and time is important.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.