Re: using sql*loader from pl/sql

From: Howard J. Rogers <hjr_at_dizwell.com>
Date: Mon, 15 Nov 2004 19:07:30 +1100
Message-ID: <419863c3$0$25321$afc38c87@news.optusnet.com.au>

Prem K Mehrotra wrote:
> DA Morgan <damorgan_at_x.washington.edu> wrote in message news:<1100456586.152756_at_yasure>...
>

>>Kaly wrote:
>>
>>
>>>is there any method to use sql*loader from pl/sql code? 
>>>i heard about doing it with java classes but i can't use it in my application
>>>
>>>thanx 
>>>kaly
>>
>>And your Oracle has a version?
>>
>>If 10g look at DBMS_SCHEDULER.
>>
>>Otherwise go to http://asktom.oracle.com for some solutions.
>>
>>If 9i or above I wouldn't waste my time with SQL*Loader and look at
>>using external tables.

>
>
>
> External tables are great but one limitation I found when I used in 9i
> is one cannot create any indexes on the external table. I don't know
> about 10G. So if I have loaded lot of data in an external table and
> later wanted to access it, my select will always be full table scan.
> Also, I cannot delete, insert, update using sql.
>
>
> Prem

But that is a serious misunderstanding of what external tables are supposed to do for you, not a limitation in external tables. If you are repeatedly scanning a huge text file as an external table, it is time to give in and load the data into an internal table (ie a normal one). The beauty of external tables is that one-time reads don't now require bringing the data inside. But if it ain't one-time (or close to it), then forget it.

HJR Received on Mon Nov 15 2004 - 02:07:30 CST