Re: using sql*loader from pl/sql

From: Howard J. Rogers <hjr_at_dizwell.com>
Date: Tue, 16 Nov 2004 17:15:37 +1100
Message-ID: <41999b09$0$24380$afc38c87@news.optusnet.com.au>

Prem K Mehrotra wrote:
> "Howard J. Rogers" <hjr_at_dizwell.com> wrote in message news:<419863c3$0$25321$afc38c87_at_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

>
>
> I recently implemented an interface where another systems sends a file
> every day
> in some directory. I will load this file in database in a table
> (called staging) and have a script which reads data from this table
> using some selects and then populate in other database tables. It
> seemed like a good application
> of external table. Since file comes from another system, I just mapped
> it to
> an external table. If I used a real table, I will have to delete data
> from the table everyday before load.
>
> However, I had lot of data in the file. I was hoping to do
> select using some index but could not do it. I would think in an
> application like mine, indexes on external table will make good sense.

And where do you think this index will reside? In another text file? Or inside the database.... and if inside the database, what is the point of trying to keep the data itself OUT of the database?!

Whatever...

HJR
> Prem
>
> I have not yet found a one time read application.
Received on Tue Nov 16 2004 - 00:15:37 CST