Re: Question about odbc link to Oracle database from PostgreSQL - Mailing list pgsql-admin
From | Hans-Jürgen Schönig |
---|---|
Subject | Re: Question about odbc link to Oracle database from PostgreSQL |
Date | |
Msg-id | EB267626-F2F0-49BB-826F-1904CC2E688F@cybertec.at Whole thread Raw |
In response to | Re: Question about odbc link to Oracle database from PostgreSQL (Hsien-Wen Chu <chu.hsien.wen@gmail.com>) |
Responses |
Re: Question about odbc link to Oracle database from PostgreSQL
|
List | pgsql-admin |
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: