Re: Question about odbc link to Oracle database from PostgreSQL

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
>
>