Re: prodeure running for ever

My advice for what it is worth

Why dynamically create the base table and indexes - if you use this frequently have the table their permanetly it will save the overhead of the creation and where clause index creations.

Also to handle the updates and inserts use a trigger on the base_Table which which update the oracle table or you can have a second cursor which deals with the columns that match ie. the updates.

HTH Colin.

Clell S. Lamb wrote in message <35661066.DB368907_at_agcs.com>...
>hai guys ,
>i have a table in non oracle database,
> that is the source of my oracle table,
> my oracle table only reads from the non oracle table.
>and any changes made in the non oracle database should
>reflect in my oracle database.
>
>the way i want to accomplish this is
>
>dynamically create an oracle_base_table as select * from
>non_oracle_table
>dynamically create indexes for the where claused columns
>
>create a my_cursor is select * from oracle_base_TABLE where
> oracle_base_table_column not in (select oracle_column
> from oracle_table)
>feeding the my_cursor into a pl/sql table
>close cursor
>inserting the data in pl/sql table into my oracle_table
>dynamically drop the indexes
>dynamically drop the table
>
>it works well for most of the tables but one table
>has over 100,000 records (which is not a whole lot)
>is taking a long time.
>
>the above procedure is just taking care of inserting
>new records in oracle database from non oracle
>but it doesn't do the updating of changed values
>which will involve
>
>if oracle_base_table.column1 != oracle_table.column1
>then update oracle_table
>set oracle_columns = non_oracle_columns
>where non_oracle_table.column1 = oracle_table.column1.
>
>i just have a feeling that this might run for ever.
>any ideas either for improving my sql or pl/sql coding or logic
>will be greatly appreciated.
>
>i may be doing an over kill, is there is a better way of doing this.
>
>thanks in advance
>regards
>hari
>
Received on Sat May 23 1998 - 21:04:36 CDT