Oracle SQL Loader

[suki]

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.

[Christian Gebhardt]

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]

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

[Andreas D. Skjervold]

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

[suki]

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]

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]

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

[benoit Bruckert · Accepted solution]

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

[suki]

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