Re: Question about odbc link to Oracle database from PostgreSQL - Mailing list pgsql-admin
From | Hsien-Wen Chu |
---|---|
Subject | Re: Question about odbc link to Oracle database from PostgreSQL |
Date | |
Msg-id | BANLkTi=T4aZEH6wprtibceJ7+vZx36oc8g@mail.gmail.com Whole thread Raw |
In response to | Re: Question about odbc link to Oracle database from PostgreSQL (Hans-Jürgen Schönig <hs@cybertec.at>) |
Responses |
Re: Question about odbc link to Oracle database from PostgreSQL
|
List | pgsql-admin |
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 > >
pgsql-admin by date: