problem connecting oracle and postgres database

Поиск

Список

Период

Сортировка

От	Josh Harrison
Тема	problem connecting oracle and postgres database
Дата	8 февраля 2009 г. 14:18:36
Msg-id	8d89ea1d0902080718j55df67bei1e5e49745a99baa4@mail.gmail.com обсуждение исходный текст
Список	pgsql-general

Дерево обсуждения

Hi all,

Im trying to create a dbi_link between Oracle and postgresql. i installed all the necessary perl packages
And I had run dbi_link.sql and it completed without any errors

This is sql that I use to connect
postgres@garuda:~$ less /home/postgres/dbi-link-2.0.0/

examples/oracle/dola.sql
/*
* Data source:     dbi:Oracle:hr;host=localhost;sid=xe
* User:            hr
* Password:        foobar
* dbh attributes: {AutoCommit => 1, RaiseError => 1}
* dbh environment: NULL
* remote schema:   NULL
* remote catalog: NULL
* local schema:    hr
*/

UPDATE
    pg_catalog.pg_settings
SET
    setting =
        CASE WHEN 'dbi_link' = ANY(string_to_array(setting, ','))
        THEN setting
        ELSE 'dbi_link,' || setting
        END
WHERE
    name = 'search_path'
;

SELECT make_accessor_functions(
    'dbi:Oracle:database=sample;host=111.11.11.11;sid=xxx;port=1521',
    ''username',
    'password',
    '---
AutoCommit: 1
RaiseError: 1
',
    NULL,
    NULL,
    NULL,
    'sample'
);

And it executed successfully .It didn't complain of anything and created rules for all the tables in the oracle database

\d sample.* gives the list of all the tables and their fields

Now when I try to execute this SQL

select * from sample."ACCESS_METHOD";
I get

NOTICE: Setting bail in %_SHARED hash. at line 25.

CONTEXT: SQL statement "SELECT dbi_link.dbi_link_init()"
NOTICE: Setting quote_literal in %_SHARED hash. at line 25.

CONTEXT: SQL statement "SELECT dbi_link.dbi_link_init()"
NOTICE: Setting get_connection_info in %_SHARED hash. at line 25.

CONTEXT: SQL statement "SELECT dbi_link.dbi_link_init()"
NOTICE: Setting quote_ident in %_SHARED hash. at line 25.

CONTEXT: SQL statement "SELECT dbi_link.dbi_link_init()"
NOTICE: Setting get_dbh in %_SHARED hash. at line 25.

CONTEXT: SQL statement "SELECT dbi_link.dbi_link_init()"
NOTICE: Setting remote_exec_dbh in %_SHARED hash. at line 25.

CONTEXT: SQL statement "SELECT dbi_link.dbi_link_init()"
NOTICE: SELECT dbi_link.cache_connection( 1 ) at line 12.

NOTICE: In cache_connection, there's no shared dbh 1 at line 7.