Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Problem with heterogeneous service (ODBC)
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,00SQLWKS> commit;
Parse 0,00 (Elapsed) 0,00 (CPU) Execute/Fetch 0,04 (Elapsed) 0,00 (CPU) Gesamt 0,04 0,00SQLWKS> 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-DatentypORA-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.deReceived on Thu May 31 2001 - 11:09:12 CDT