Hey Guys, here is my opinion.

External Tables has limitation when it comes to size of the file, e.g you
cannot expect external tables work effectively when file size exceedes
1GB.
Another point is when you are going to pull data into another table from
External Tables, this behaves like another parallel inserts from one
source to another source.

To answer to this question in straight - I WOULD SAY THERE IS NO WAY TO
CALL SQL LOADER FROM STORED PROCEDURE.

Regards
Ravikumar
973-360-2842
VOIP: 610-2842



"Ricardo Chaves "
<oracledba-ezmlmshield-x97574264.[Email address protected]
04/03/2008 06:55 AM

To
"LazyDBA Discussion" <[Email address protected]
cc

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





******************************************************
IMPORTANT: Any information contained in this communication is intended for the use of the named individual or entity. All information contained in this communication is not intended or construed as an offer, solicitation, or a recommendation to purchase any security. Advice, suggestions or views presented in this communication are not necessarily those of Pershing LLC nor do they warrant a complete or accurate statement.

If you are not an intended party to this communication, please notify the sender and delete/destroy any and all copies of this communication. Unintended recipients shall not review, reproduce, disseminate nor disclose any information contained in this communication. Pershing LLC reserves the right to monitor and retain all incoming and outgoing communications as permitted by applicable law.

Email communications may contain viruses or other defects. Pershing LLC does not accept liability nor does it warrant that email communications are virus or defect free.
******************************************************

Oracle LazyDBA home page