Re: using sql*loader from pl/sql

"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.

Prem

I have not yet found a one time read application. Received on Mon Nov 15 2004 - 20:37:59 CST