cancel
Showing results for 
Search instead for 
Did you mean: 

Oracle SQL Loader

SOLVED
Go to solution
suki
Frequent Advisor

Oracle SQL Loader

Hi,
I have one Oracle 8i Database Server on Hp-UX 11.0 namely DBserver and one more HP-UX 11.0 with Samba Server namely data server.
Now I want to upload the data from the Samba shared folder on the data server to the Oracle Database on the Database server.
What are the Oracle components I need to install on both the server and how to install?

Thanks in advance.
suki.
8 REPLIES
Christian Gebhardt
Honored Contributor

Re: Oracle SQL Loader

Hi

You need SQL-Loader on your DBserver this is part from Oracle Server-Installation and nothing else.

Mount your shared-Directory from the data server on your DBserver and load the data.

Chris
benoit Bruckert
Honored Contributor

Re: Oracle SQL Loader

Hi,
You can also install oracle client on the data server, and run sqlloader from the data server to the db server (connect string).

hope that help
Benoit
Une application mal pansée aboutit à une usine à gaze (GHG)
Andreas D. Skjervold
Honored Contributor

Re: Oracle SQL Loader

Hi

No need for additional install as SQL*Loader is bundled with Oracle.

Just map the shared filsystem onto the database server and use SQL*Loader to load the data into the database.
You'll need to put inplace a control file to tell SQL*Loader where the data is supposed to go and what format it has.

sqlldr userid=/ control=

control.ctl :
LOAD DATA
INFILE '< datafile.csv>'
APPEND --(or REPLACE, INSERT is default;requires empty table)
INTO TABLE
FIELDS TERMINATED BY ';' OPTIONALLY ENCLOSED BY '???'
(col_name1, col_name2, col_name3...
date_col DATE ???dd-mm-yyy???)

The datafile must contain similar number of fields as referenced in the controlfile and the datatype must be like.

datafile.csv:
Text file with ; separator and no other data than the data supposed to be loaded (ie no comments)
John;Doe;East street;NY

Andreas
Only by ignoring what everyone think is important, can you be aware of what everyone ignores!
suki
Frequent Advisor

Re: Oracle SQL Loader

Hi Benoit,
Can you give me an example for the scenario what you have mentioned. It will be very helpful for me.
Thanks to Chris, Benoit and Andreas for your information.

Thanks in Advance,
suki.
Christian Gebhardt
Honored Contributor

Re: Oracle SQL Loader

Hi

SQL*Loader via SQL*Net works but is very slow.
SQL*Net is an old, terrible protocoll and not designed for higher volumes of data.

I will suggest to transfer the data to the DBserver (NFS-mount, ftp, rcp, scp, ...) and use the SQL*Loader on the DBserver.

Chris
Jean-Luc Oudart
Honored Contributor

Re: Oracle SQL Loader

If you can use SQL*Loader with MODE DIRECT=TRUE
and tune the number of rows, you can really outperform the "conventional" mode


Jean-Luc
fiat lux
benoit Bruckert
Honored Contributor
Solution

Re: Oracle SQL Loader

Sure,
As example :
create a ctl file which describe the structure of the text file. examples given bellow..
You just have to use as connect string : user/passwd@database.

Of course, your client should be configure, and for this tnsnames.ora should be OK.

Oracle 8i is using net 8 which is replacement for sql*net.
Performances are really better, and I think you can use it !!! unless you have GBytes of datas to load...

example of tnsnames -in $ORACLE_HOME/network/admin :
DATABASE =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = DBserver)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = )
)
)

If you have a database server and networked client, you should have a listener.ora on your server, check the name of the service...

hope it will help
Benoit
Une application mal pansée aboutit à une usine à gaze (GHG)
suki
Frequent Advisor

Re: Oracle SQL Loader

Thanks to everyone who has cleared me about the sqlldr.
Thanks and Regards,
suki