prodeure running for ever

From: Clell S. Lamb <lambc_at_agcs.com>
Date: Fri, 22 May 1998 16:55:18 -0700
Message-ID: <35661066.DB368907@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 Fri May 22 1998 - 18:55:18 CDT