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@xxxxxxxxx>:
> 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
>
--
Sent via pgsql-admin mailing list (pgsql-admin@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
