Problem with heterogeneous service (ODBC)

From: Lothar Armbrüster <la_at_oktagramm.de>
Date: Thu, 31 May 2001 18:09:12 +0200
Message-ID: <PM0003859AEB800126@hades.unknown.dom>

Hello out there,

today I sucessfully set up a database link from Oracle 8.1.7 on NT to an Access database via ODBC and herterogeneous service.

I can select, delete and insert into tables on that database.

But then I tried the following:

SQLWKS> insert into gp_test_at_hsaccess

2> (select gp_nr from gp_katalog);
(select gp_nr from gp_katalog)

*
ORA-02025: Alle Tabellen in der SQL-Anweisung müssen in der Fern-Datenbank sein

Well, doesn't work. So I did my inserts in a loop:

SQLWKS> begin
     2>    for gp_cur in (select gp_nr from gp_katalog) loop
     3>       insert into gp_test_at_hsaccess values(gp_cur.gp_nr);
     4>    end loop;
     5> end;
     6> /
Anweisung verarbeitet
Parse             0,06 (Elapsed)     0,00 (CPU)
Execute/Fetch   602,67 (Elapsed)     0,00 (CPU)
Gesamt          602,73               0,00

SQLWKS> commit;
Anweisung verarbeitet

Parse             0,00 (Elapsed)     0,00 (CPU)
Execute/Fetch     0,04 (Elapsed)     0,00 (CPU)
Gesamt            0,04               0,00

SQLWKS> select count(*) from gp_test_at_hsaccess; COUNT(*)

248724
1 Zeile ausgewählt.

10 minutes for 248000 rows is quite slow. So I remenbered about collections and bulk binds:

SQLWKS> declare
     2>
     3> type tab_gp is table of char(9) index by binary_integer;
     4>
     5> gps tab_gp;
     6> begin
     7>    select gp_nr bulk collect into gps from gp_katalog;
     8>    forall i in gps.first..gps.last
     9>       insert into gp_test_at_hsaccess values(gps(i));
    10> end;
    11> /
ORA-28527: Fehler bei Zuordnung von Heterogeneous Services-Datentyp

ORA-02063: vorangestelltes line von HSACCESS ORA-06512: in Zeile 8

Now I need some help to circumvent this error. The heterogeneous servive is able to handle char(9). But obviously not in bulk binds.

Any ideas?

Many thanks in advance,
Lothar

-- 
Lothar Armbrüster       | la_at_oktagramm.de
Hauptstr. 26            | la_at_heptagramm.de
D-65346 Eltville        | lothar.armbruester_at_t-online.de

Received on Thu May 31 2001 - 11:09:12 CDT