Access DB/2 via Transparent Gateway

I have to access data in a DB/2 database via Oracle Transparent Gateway from within PL/SQL (Oracle 7.3.4), means I 've to do inserts and updates.

These inserts and updates access 2 DB/2 tables like follows (in some PL/SQL procedures/packages)

    insert into db2_table_1

        (field_1, field_2, field_3)
        (select  field_1, plsql_variable, field_3 from db2_table_2)

I have to insert some values which are saved in PL/SQL-variables like plsql_variable.

Now Oracle doesn't evaluate plsql_variable, but parses it as is to DB/2, which cannot process it.

I have two solutions:

• I use a cursor-for-loop (but it has to transfer data over network, I think)

    for rec in (select... from db2_table_2) loop

        insert into db2_table_1 (...) values (rec.field_1...)
        ...

• I use dynamic SQL (DBMS_SQL package)

Both function fine, but I have to change a lot of code!

Does anyone know another solution????

Thanks
Bernd Hoeppner
hoeppner_at_ivm.com Received on Wed Jan 27 1999 - 14:39:55 CST