Обсуждение: Question about odbc link to Oracle database from PostgreSQL

Поиск
Список
Период
Сортировка

Question about odbc link to Oracle database from PostgreSQL

От
Hsien-Wen Chu
Дата:
Dear All

I have a question regarding to odbc link,


I had a PostgreSQL database and an Oracle database, now I have created
a dblink to Oracle database base on odbc link.


in Oracle database, I created a user named ORATEST, and create a table
named TEST as ORATEST user.

SQL> select user_name,score from test;

USER_NAME                 SCORE
-------------------- ----------
kevin                    99
fred                         98





now I have created the database link to Oracle database over odbc link
and get success.


tora=# select odbclink.connect('orcl', 'oratest', 'oratest');
 connect
---------
       1
(1 row)

tora=# select odbclink.connect('DSN=orcl;UID=oratest;PWD=oratest;');
 connect
---------
       2
(1 row)

tora=# select * from odbclink.connections();
 id | connected | dsn  | uid  | pwd  |           connstr
----+-----------+------+------+------+-----------------------------
  1 | t         | orcl | oratest| oratest|
  2 | t         |      |      |      | DSN=orcl;UID=oratest;PWD=oratest;
  3 | f         |      |      |      |
  4 | f         |      |      |      |




but the problem is that I can not execute the SQL execute it.

tora=# select odbclink.query(1, 'SELECT * FROM test') as
result(user_name text,score number);
ERROR:  syntax error at or near "("
LINE 1: ... odbclink.query(1, 'SELECT * FROM test') as result(user_name...
                                                             ^
tora=# select * odbclink.query(1, 'SELECT * FROM test') ;
ERROR:  syntax error at or near "odbclink"
LINE 1: select * odbclink.query(1, 'SELECT * FROM test') ;
                 ^
tora=# select * from odbclink.query(2,'SELECT SYSDATE FROM DUAL');
ERROR:  a column definition list is required for functions returning "record"
LINE 1: select * from odbclink.query(2,'SELECT SYSDATE FROM DUAL');
                      ^
tora=# select * from odbclink.query(1,'SELECT SYSDATE FROM DUAL');
ERROR:  a column definition list is required for functions returning "record"
LINE 1: select * from odbclink.query(1,'SELECT SYSDATE FROM DUAL');
                      ^
tora=#


does anyone mind to give me hint?


a big thanks

Hsien-Wen

Re: Question about odbc link to Oracle database from PostgreSQL

От
Hsien-Wen Chu
Дата:
Hi Hans

many thanks for the answer,

my table structure on Oracle,   but I still get error as following,
Please could show me the correct SQL statement?



many many thanks

Hsien-Wen


SQL> desc test;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 USER_NAME                                          VARCHAR2(20)
 SCORE                                              NUMBER

SQL>







tora=#  select odbclink.query(1, 'SELECT USER_NAME,SCORE from test')
as result(id int4, USER_NAME text, SCORE decimal);
ERROR:  syntax error at or near "("
LINE 1: ...y(1, 'SELECT USER_NAME,SCORE from test') as result(id int4, ...
                                                             ^
tora=# select odbclink.query(1, 'SELECT USER_NAME,SCORE from test') as
result(USER_NAME text, SCORE decimal);
ERROR:  syntax error at or near "("
LINE 1: ...y(1, 'SELECT USER_NAME,SCORE from test') as result(USER_NAME...
                                                             ^




2011/5/11 Hsien-Wen Chu <chu.hsien.wen@gmail.com>:
> Dear All
>
> I have a question regarding to odbc link,
>
>
> I had a PostgreSQL database and an Oracle database, now I have created
> a dblink to Oracle database base on odbc link.
>
>
> in Oracle database, I created a user named ORATEST, and create a table
> named TEST as ORATEST user.
>
> SQL> select user_name,score from test;
>
> USER_NAME                 SCORE
> -------------------- ----------
> kevin                    99
> fred                         98
>
>
>
>
>
> now I have created the database link to Oracle database over odbc link
> and get success.
>
>
> tora=# select odbclink.connect('orcl', 'oratest', 'oratest');
>  connect
> ---------
>       1
> (1 row)
>
> tora=# select odbclink.connect('DSN=orcl;UID=oratest;PWD=oratest;');
>  connect
> ---------
>       2
> (1 row)
>
> tora=# select * from odbclink.connections();
>  id | connected | dsn  | uid  | pwd  |           connstr
> ----+-----------+------+------+------+-----------------------------
>  1 | t         | orcl | oratest| oratest|
>  2 | t         |      |      |      | DSN=orcl;UID=oratest;PWD=oratest;
>  3 | f         |      |      |      |
>  4 | f         |      |      |      |
>
>
>
>
> but the problem is that I can not execute the SQL execute it.
>
> tora=# select odbclink.query(1, 'SELECT * FROM test') as
> result(user_name text,score number);
> ERROR:  syntax error at or near "("
> LINE 1: ... odbclink.query(1, 'SELECT * FROM test') as result(user_name...
>                                                             ^
> tora=# select * odbclink.query(1, 'SELECT * FROM test') ;
> ERROR:  syntax error at or near "odbclink"
> LINE 1: select * odbclink.query(1, 'SELECT * FROM test') ;
>                 ^
> tora=# select * from odbclink.query(2,'SELECT SYSDATE FROM DUAL');
> ERROR:  a column definition list is required for functions returning "record"
> LINE 1: select * from odbclink.query(2,'SELECT SYSDATE FROM DUAL');
>                      ^
> tora=# select * from odbclink.query(1,'SELECT SYSDATE FROM DUAL');
> ERROR:  a column definition list is required for functions returning "record"
> LINE 1: select * from odbclink.query(1,'SELECT SYSDATE FROM DUAL');
>                      ^
> tora=#
>
>
> does anyone mind to give me hint?
>
>
> a big thanks
>
> Hsien-Wen
>

Re: Question about odbc link to Oracle database from PostgreSQL

От
Hans-Jürgen Schönig
Дата:
hello ...

oh, it seems you did not get that one:

select * FROM odbclink.query(1, 'SELECT * FROM test') as result(user_name text,score number);

give it a try ;).

    hans



On May 11, 2011, at 12:24 PM, Hsien-Wen Chu wrote:

> Hi Hans
>
> many thanks for the answer,
>
> my table structure on Oracle,   but I still get error as following,
> Please could show me the correct SQL statement?
>
>
>
> many many thanks
>
> Hsien-Wen
>
>
> SQL> desc test;
> Name                                      Null?    Type
> ----------------------------------------- -------- ----------------------------
> USER_NAME                                          VARCHAR2(20)
> SCORE                                              NUMBER
>
> SQL>
>
>
>
>
>
>
>
> tora=#  select odbclink.query(1, 'SELECT USER_NAME,SCORE from test')
> as result(id int4, USER_NAME text, SCORE decimal);
> ERROR:  syntax error at or near "("
> LINE 1: ...y(1, 'SELECT USER_NAME,SCORE from test') as result(id int4, ...
>                                                             ^
> tora=# select odbclink.query(1, 'SELECT USER_NAME,SCORE from test') as
> result(USER_NAME text, SCORE decimal);
> ERROR:  syntax error at or near "("
> LINE 1: ...y(1, 'SELECT USER_NAME,SCORE from test') as result(USER_NAME...
>                                                             ^
>
>
>
>
> 2011/5/11 Hsien-Wen Chu <chu.hsien.wen@gmail.com>:
>> Dear All
>>
>> I have a question regarding to odbc link,
>>
>>
>> I had a PostgreSQL database and an Oracle database, now I have created
>> a dblink to Oracle database base on odbc link.
>>
>>
>> in Oracle database, I created a user named ORATEST, and create a table
>> named TEST as ORATEST user.
>>
>> SQL> select user_name,score from test;
>>
>> USER_NAME                 SCORE
>> -------------------- ----------
>> kevin                    99
>> fred                         98
>>
>>
>>
>>
>>
>> now I have created the database link to Oracle database over odbc link
>> and get success.
>>
>>
>> tora=# select odbclink.connect('orcl', 'oratest', 'oratest');
>>  connect
>> ---------
>>       1
>> (1 row)
>>
>> tora=# select odbclink.connect('DSN=orcl;UID=oratest;PWD=oratest;');
>>  connect
>> ---------
>>       2
>> (1 row)
>>
>> tora=# select * from odbclink.connections();
>>  id | connected | dsn  | uid  | pwd  |           connstr
>> ----+-----------+------+------+------+-----------------------------
>>  1 | t         | orcl | oratest| oratest|
>>  2 | t         |      |      |      | DSN=orcl;UID=oratest;PWD=oratest;
>>  3 | f         |      |      |      |
>>  4 | f         |      |      |      |
>>
>>
>>
>>
>> but the problem is that I can not execute the SQL execute it.
>>
>> tora=# select odbclink.query(1, 'SELECT * FROM test') as
>> result(user_name text,score number);
>> ERROR:  syntax error at or near "("
>> LINE 1: ... odbclink.query(1, 'SELECT * FROM test') as result(user_name...
>>                                                             ^
>> tora=# select * odbclink.query(1, 'SELECT * FROM test') ;
>> ERROR:  syntax error at or near "odbclink"
>> LINE 1: select * odbclink.query(1, 'SELECT * FROM test') ;
>>                 ^
>> tora=# select * from odbclink.query(2,'SELECT SYSDATE FROM DUAL');
>> ERROR:  a column definition list is required for functions returning "record"
>> LINE 1: select * from odbclink.query(2,'SELECT SYSDATE FROM DUAL');
>>                      ^
>> tora=# select * from odbclink.query(1,'SELECT SYSDATE FROM DUAL');
>> ERROR:  a column definition list is required for functions returning "record"
>> LINE 1: select * from odbclink.query(1,'SELECT SYSDATE FROM DUAL');
>>                      ^
>> tora=#
>>
>>
>> does anyone mind to give me hint?
>>
>>
>> a big thanks
>>
>> Hsien-Wen
>>
>


--
Cybertec Schönig & Schönig GmbH
Gröhrmühlgasse 26
A-2700 Wiener Neustadt, Austria
Web: http://www.postgresql-support.de

Re: Question about odbc link to Oracle database from PostgreSQL

От
Hans-Jürgen Schönig
Дата:
hello ...

in SQL this "*" has to be known at parsing time ... so, it needs the number / type of columns before doing anything
else.
as you select "some" data from oracle (the postgres parser does not look into the oracle sql) you have to tell the
systemwhich data to expect. 
it is somehow like this:

> select * FROM odbclink.query(1, 'SELECT * FROM test') as result(user_name text,score number);


so, first of all it is a set returning function.
secondly it needs this magic AS clause.
the data types have to map nicely.

    many thanks,

        hans



On May 11, 2011, at 12:03 PM, Hsien-Wen Chu wrote:

> Dear All
>
> I have a question regarding to odbc link,
>
>
> I had a PostgreSQL database and an Oracle database, now I have created
> a dblink to Oracle database base on odbc link.
>
>
> in Oracle database, I created a user named ORATEST, and create a table
> named TEST as ORATEST user.
>
> SQL> select user_name,score from test;
>
> USER_NAME                 SCORE
> -------------------- ----------
> kevin                    99
> fred                         98
>
>
>
>
>
> now I have created the database link to Oracle database over odbc link
> and get success.
>
>
> tora=# select odbclink.connect('orcl', 'oratest', 'oratest');
> connect
> ---------
>       1
> (1 row)
>
> tora=# select odbclink.connect('DSN=orcl;UID=oratest;PWD=oratest;');
> connect
> ---------
>       2
> (1 row)
>
> tora=# select * from odbclink.connections();
> id | connected | dsn  | uid  | pwd  |           connstr
> ----+-----------+------+------+------+-----------------------------
>  1 | t         | orcl | oratest| oratest|
>  2 | t         |      |      |      | DSN=orcl;UID=oratest;PWD=oratest;
>  3 | f         |      |      |      |
>  4 | f         |      |      |      |
>
>
>
>
> but the problem is that I can not execute the SQL execute it.
>
> tora=# select odbclink.query(1, 'SELECT * FROM test') as
> result(user_name text,score number);
> ERROR:  syntax error at or near "("
> LINE 1: ... odbclink.query(1, 'SELECT * FROM test') as result(user_name...
>                                                             ^
> tora=# select * odbclink.query(1, 'SELECT * FROM test') ;
> ERROR:  syntax error at or near "odbclink"
> LINE 1: select * odbclink.query(1, 'SELECT * FROM test') ;
>                 ^
> tora=# select * from odbclink.query(2,'SELECT SYSDATE FROM DUAL');
> ERROR:  a column definition list is required for functions returning "record"
> LINE 1: select * from odbclink.query(2,'SELECT SYSDATE FROM DUAL');
>                      ^
> tora=# select * from odbclink.query(1,'SELECT SYSDATE FROM DUAL');
> ERROR:  a column definition list is required for functions returning "record"
> LINE 1: select * from odbclink.query(1,'SELECT SYSDATE FROM DUAL');
>                      ^
> tora=#
>
>
> does anyone mind to give me hint?
>
>
> a big thanks
>
> Hsien-Wen
>


--
Cybertec Schönig & Schönig GmbH
Gröhrmühlgasse 26
A-2700 Wiener Neustadt, Austria
Web: http://www.postgresql-support.de

Re: Question about odbc link to Oracle database from PostgreSQL

От
Hsien-Wen Chu
Дата:
Hi Hans

Many many thanks, I have executed the SQL, how ever, it does not work,
I don't know what's wrong




tora=# select * FROM odbclink.query(1, 'SELECT * FROM test') as
result(user_name text,score number);
ERROR:  type "number" does not exist
LINE 1: ...SELECT * FROM test') as result(user_name text,score number);
                                                               ^
tora=# select * FROM odbclink.query(1, 'SELECT user_name,score  FROM
test') as result(user_name text,score number);
ERROR:  type "number" does not exist
LINE 1: ...e,score  FROM test') as result(user_name text,score number);
                                                               ^
tora=#




2011/5/11 Hans-Jürgen Schönig <hs@cybertec.at>:
> hello ...
>
> oh, it seems you did not get that one:
>
> select * FROM odbclink.query(1, 'SELECT * FROM test') as result(user_name text,score number);
>
> give it a try ;).
>
>        hans
>
>
>
> On May 11, 2011, at 12:24 PM, Hsien-Wen Chu wrote:
>
>> Hi Hans
>>
>> many thanks for the answer,
>>
>> my table structure on Oracle,   but I still get error as following,
>> Please could show me the correct SQL statement?
>>
>>
>>
>> many many thanks
>>
>> Hsien-Wen
>>
>>
>> SQL> desc test;
>> Name                                      Null?    Type
>> ----------------------------------------- -------- ----------------------------
>> USER_NAME                                          VARCHAR2(20)
>> SCORE                                              NUMBER
>>
>> SQL>
>>
>>
>>
>>
>>
>>
>>
>> tora=#  select odbclink.query(1, 'SELECT USER_NAME,SCORE from test')
>> as result(id int4, USER_NAME text, SCORE decimal);
>> ERROR:  syntax error at or near "("
>> LINE 1: ...y(1, 'SELECT USER_NAME,SCORE from test') as result(id int4, ...
>>                                                             ^
>> tora=# select odbclink.query(1, 'SELECT USER_NAME,SCORE from test') as
>> result(USER_NAME text, SCORE decimal);
>> ERROR:  syntax error at or near "("
>> LINE 1: ...y(1, 'SELECT USER_NAME,SCORE from test') as result(USER_NAME...
>>                                                             ^
>>
>>
>>
>>
>> 2011/5/11 Hsien-Wen Chu <chu.hsien.wen@gmail.com>:
>>> Dear All
>>>
>>> I have a question regarding to odbc link,
>>>
>>>
>>> I had a PostgreSQL database and an Oracle database, now I have created
>>> a dblink to Oracle database base on odbc link.
>>>
>>>
>>> in Oracle database, I created a user named ORATEST, and create a table
>>> named TEST as ORATEST user.
>>>
>>> SQL> select user_name,score from test;
>>>
>>> USER_NAME                 SCORE
>>> -------------------- ----------
>>> kevin                    99
>>> fred                         98
>>>
>>>
>>>
>>>
>>>
>>> now I have created the database link to Oracle database over odbc link
>>> and get success.
>>>
>>>
>>> tora=# select odbclink.connect('orcl', 'oratest', 'oratest');
>>>  connect
>>> ---------
>>>       1
>>> (1 row)
>>>
>>> tora=# select odbclink.connect('DSN=orcl;UID=oratest;PWD=oratest;');
>>>  connect
>>> ---------
>>>       2
>>> (1 row)
>>>
>>> tora=# select * from odbclink.connections();
>>>  id | connected | dsn  | uid  | pwd  |           connstr
>>> ----+-----------+------+------+------+-----------------------------
>>>  1 | t         | orcl | oratest| oratest|
>>>  2 | t         |      |      |      | DSN=orcl;UID=oratest;PWD=oratest;
>>>  3 | f         |      |      |      |
>>>  4 | f         |      |      |      |
>>>
>>>
>>>
>>>
>>> but the problem is that I can not execute the SQL execute it.
>>>
>>> tora=# select odbclink.query(1, 'SELECT * FROM test') as
>>> result(user_name text,score number);
>>> ERROR:  syntax error at or near "("
>>> LINE 1: ... odbclink.query(1, 'SELECT * FROM test') as result(user_name...
>>>                                                             ^
>>> tora=# select * odbclink.query(1, 'SELECT * FROM test') ;
>>> ERROR:  syntax error at or near "odbclink"
>>> LINE 1: select * odbclink.query(1, 'SELECT * FROM test') ;
>>>                 ^
>>> tora=# select * from odbclink.query(2,'SELECT SYSDATE FROM DUAL');
>>> ERROR:  a column definition list is required for functions returning "record"
>>> LINE 1: select * from odbclink.query(2,'SELECT SYSDATE FROM DUAL');
>>>                      ^
>>> tora=# select * from odbclink.query(1,'SELECT SYSDATE FROM DUAL');
>>> ERROR:  a column definition list is required for functions returning "record"
>>> LINE 1: select * from odbclink.query(1,'SELECT SYSDATE FROM DUAL');
>>>                      ^
>>> tora=#
>>>
>>>
>>> does anyone mind to give me hint?
>>>
>>>
>>> a big thanks
>>>
>>> Hsien-Wen
>>>
>>
>
>
> --
> Cybertec Schönig & Schönig GmbH
> Gröhrmühlgasse 26
> A-2700 Wiener Neustadt, Austria
> Web: http://www.postgresql-support.de
>
>

Re: Question about odbc link to Oracle database from PostgreSQL

От
Hans-Jürgen Schönig
Дата:
hello ...

read this:

ERROR:  type "number" does not exist

we don't have this type.
use numeric or whatever matches.

    many thanks,

        hans


On May 11, 2011, at 2:46 PM, Hsien-Wen Chu wrote:

> ERROR:  type "number" does not exist


--
Cybertec Schönig & Schönig GmbH
Gröhrmühlgasse 26
A-2700 Wiener Neustadt, Austria
Web: http://www.postgresql-support.de