Re: Question about odbc link to Oracle database from PostgreSQL

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(dot)hsien(dot)wen(at)gmail(dot)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
>