topic Re: Oracle SQL Loader in Operating System - HP-UX

Oracle SQL Loader

suki — Mon, 14 Oct 2002 06:28:19 GMT

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.

Re: Oracle SQL Loader

Christian Gebhardt — Mon, 14 Oct 2002 07:39:56 GMT

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

Re: Oracle SQL Loader

benoit Bruckert — Mon, 14 Oct 2002 07:42:18 GMT

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

Re: Oracle SQL Loader

Andreas D. Skjervold — Mon, 14 Oct 2002 07:46:30 GMT

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

Re: Oracle SQL Loader

suki — Mon, 14 Oct 2002 07:57:05 GMT

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.

Re: Oracle SQL Loader

Christian Gebhardt — Mon, 14 Oct 2002 08:26:55 GMT

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

Re: Oracle SQL Loader

Jean-Luc Oudart — Mon, 14 Oct 2002 10:44:41 GMT

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

Re: Oracle SQL Loader

benoit Bruckert — Mon, 14 Oct 2002 11:20:52 GMT

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

Re: Oracle SQL Loader

suki — Tue, 15 Oct 2002 00:45:54 GMT

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