Then if I understand correctly, the ultimate goal is to read a local
file, and load that in a remote database based on some information
internal to the local database?

-----Original Message-----
From: chandrashekar
[mailto:oracledba-ezmlmshield-x70852604.[Email address protected]
Sent: Thursday, April 03, 2008 8:16 AM
To: LazyDBA Discussion
Subject: RE: SQLLoader from Procedure



If I use External table I need to write my procedure in target Location
I mean in Prod2 also right? But I don't have rights for creating any
objects in it. Only I can do DML Commands. I can't add new Procedure
there

Thanks,
Chandu
-----Original Message-----
From: Ricardo Chaves
[mailto:oracledba-ezmlmshield-x97574264.[Email address protected]
Sent: Thursday, April 03, 2008 4:26 PM
To: LazyDBA Discussion
Subject: RE: SQLLoader from Procedure

You would use an external table, which gives us the ability to query a
file
as if it were a database table. So, a call to SQLLDR from PL/SQL becomes
simply on 'INSERT INTO table SELECT * FROM external_table'.
A quick and easy way to get started with external tables is to take some
of
your control (CTL) files and convert them into external table
definitions:
'SQLLDR user/pass example.ctl external_table=generate_only'
In example.log file, we'll find:
- A CREATE DIRECTORY statement. External tables require a
database
directory object.
- A CREATE TABLE statement for the external table. This will
have
your converted SQL Loader script embedded in it.
- An INSERT statement that does the loading.
- A pair of DROP statements to clean up.
You'll edit this CREATE TABLE statement to give the external table
whatever
name you like, change the directories, and so on. Certainly, better if
you
use PARALLEL on this statement.

Ciao


-----Mensaje original-----
De: chandrashekar [mailto:chandrashekar.[Email address protected]
Enviado el: jueves, 03 de abril de 2008 9:36
Para: RICARDO.[Email address protected]
Asunto: RE: SQLLoader from Procedure


Hi Ricardo,

Thanks for scripts, but its pure Shell scripting write. My case is
little different. I will get particular table name and Target database
details. On based on that I need to transfer data to target location,
from Local procedure in oracle I need to do all these process by using
SqlLoader (I need invoke SQL Loader command from Procedure).

Is there any way to handle this?

Thanks,
Chandu


No virus found in this outgoing message.
Checked by AVG.
Version: 7.5.519 / Virus Database: 269.22.5/1356 - Release Date:
02/04/2008
16:14




---------------------------------------------------------------------
TO REPLY TO EVERYBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY
To post a dba job: http://jobs.lazydba.com
To Subscribe : http://www.LazyDBA.com
To unsubscribe: http://www.lazydba.com/unsubscribe.html



***********************
No virus was detected in the attachment no filename

Your mail has been scanned by InterScan.
***********-***********


************************************************************************
************************************************************************
*********************
"This message and any attachments are solely for the intended recipient
and may contain Birlasoft confidential or privileged information. If you
are not the intended recipient,any disclosure,copying, use, or
distribution of the information included in this message and any
attachments is
prohibited. If you have received this communication in error, please
notify us by reply e-mail at ([Email address protected] and permanently
delete this message and any attachments. Thank you."
************************************************************************
************************************************************************
*********************


---------------------------------------------------------------------
TO REPLY TO EVERYBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY
To post a dba job: http://jobs.lazydba.com
To Subscribe : http://www.LazyDBA.com
To unsubscribe: http://www.lazydba.com/unsubscribe.html


______________

The information contained in this message is proprietary and/or confidential. If you are not the
intended recipient, please: (i) delete the message and all copies; (ii) do not disclose,
distribute or use the message in any manner; and (iii) notify the sender immediately. In addition,
please be aware that any message addressed to our domain is subject to archiving and review by
persons other than the intended recipient. Thank you.
_____________

Oracle LazyDBA home page