Re: Problem with heterogeneous service (ODBC)

"Lothar Armbrüster" <la_at_oktagramm.de> wrote in message news:PM0003859AEB800126_at_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

Could you please unset NLS_LANG and repeat the operation so we get the error messages in English?
You don't expect this group to search the translations in the documentation, do you?

Regards,
Sybrand Bakker, Oracle DBA Received on Thu May 31 2001 - 13:15:46 CDT