Received: from maia.hub.org (maia-5.hub.org [200.46.204.29]) by mail.postgresql.org (Postfix) with ESMTP id D16481337B80 for ; Wed, 11 May 2011 07:28:32 -0300 (ADT) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.29]) (amavisd-maia, port 10024) with ESMTP id 58060-05 for ; Wed, 11 May 2011 10:28:24 +0000 (UTC) X-Greylist: delayed 00:16:47.45859 by SQLgrey-1.7.6 Received: from mail.cybertec.at (mail.cybertec.at [87.118.110.48]) by mail.postgresql.org (Postfix) with ESMTP id BF81C1337B97 for ; Wed, 11 May 2011 07:28:23 -0300 (ADT) Received: from [192.168.0.13] (home [84.115.25.80]) by mail.cybertec.at (Postfix) with ESMTP id 449A85FC5E5; Wed, 11 May 2011 12:16:52 +0200 (CEST) Subject: Re: Question about odbc link to Oracle database from PostgreSQL Mime-Version: 1.0 (Apple Message framework v1084) Content-Type: text/plain; charset=iso-8859-1 From: =?iso-8859-1?Q?Hans-J=FCrgen_Sch=F6nig?= In-Reply-To: Date: Wed, 11 May 2011 12:28:22 +0200 Cc: pgsql-admin@postgresql.org Content-Transfer-Encoding: quoted-printable Message-Id: References: To: Hsien-Wen Chu X-Mailer: Apple Mail (2.1084) X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-1.91 tagged_above=-10 required=5 tests=BAYES_00=-1.9, T_RP_MATCHES_RCVD=-0.01 X-Spam-Level: X-Archive-Number: 201105/81 X-Sequence-Number: 37518 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 >=20 > many thanks for the answer, >=20 > my table structure on Oracle, but I still get error as following, > Please could show me the correct SQL statement? >=20 >=20 >=20 > many many thanks >=20 > Hsien-Wen >=20 >=20 > SQL> desc test; > Name Null? Type > ----------------------------------------- -------- = ---------------------------- > USER_NAME VARCHAR2(20) > SCORE NUMBER >=20 > SQL> >=20 >=20 >=20 >=20 >=20 >=20 >=20 > tora=3D# 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=3D# 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... > ^ >=20 >=20 >=20 >=20 > 2011/5/11 Hsien-Wen Chu : >> Dear All >>=20 >> I have a question regarding to odbc link, >>=20 >>=20 >> I had a PostgreSQL database and an Oracle database, now I have = created >> a dblink to Oracle database base on odbc link. >>=20 >>=20 >> in Oracle database, I created a user named ORATEST, and create a = table >> named TEST as ORATEST user. >>=20 >> SQL> select user_name,score from test; >>=20 >> USER_NAME SCORE >> -------------------- ---------- >> kevin 99 >> fred 98 >>=20 >>=20 >>=20 >>=20 >>=20 >> now I have created the database link to Oracle database over odbc = link >> and get success. >>=20 >>=20 >> tora=3D# select odbclink.connect('orcl', 'oratest', 'oratest'); >> connect >> --------- >> 1 >> (1 row) >>=20 >> tora=3D# select = odbclink.connect('DSN=3Dorcl;UID=3Doratest;PWD=3Doratest;'); >> connect >> --------- >> 2 >> (1 row) >>=20 >> tora=3D# select * from odbclink.connections(); >> id | connected | dsn | uid | pwd | connstr >> ----+-----------+------+------+------+----------------------------- >> 1 | t | orcl | oratest| oratest| >> 2 | t | | | | = DSN=3Dorcl;UID=3Doratest;PWD=3Doratest; >> 3 | f | | | | >> 4 | f | | | | >>=20 >>=20 >>=20 >>=20 >> but the problem is that I can not execute the SQL execute it. >>=20 >> tora=3D# 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=3D# select * odbclink.query(1, 'SELECT * FROM test') ; >> ERROR: syntax error at or near "odbclink" >> LINE 1: select * odbclink.query(1, 'SELECT * FROM test') ; >> ^ >> tora=3D# 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=3D# 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=3D# >>=20 >>=20 >> does anyone mind to give me hint? >>=20 >>=20 >> a big thanks >>=20 >> Hsien-Wen >>=20 >=20 -- Cybertec Sch=F6nig & Sch=F6nig GmbH Gr=F6hrm=FChlgasse 26 A-2700 Wiener Neustadt, Austria Web: http://www.postgresql-support.de