Re: Question about odbc link to Oracle database from PostgreSQL
От | Hsien-Wen Chu |
---|---|
Тема | Re: Question about odbc link to Oracle database from PostgreSQL |
Дата | |
Msg-id | BANLkTim1Z0hKcqiTV7GzMWgw2RfQtmg3mQ@mail.gmail.com обсуждение исходный текст |
Ответ на | Question about odbc link to Oracle database from PostgreSQL (Hsien-Wen Chu <chu.hsien.wen@gmail.com>) |
Ответы |
Re: Question about odbc link to Oracle database from PostgreSQL
(Hans-Jürgen Schönig <hs@cybertec.at>)
|
Список | pgsql-admin |
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 >
В списке pgsql-admin по дате отправления:
Предыдущее
От: Hsien-Wen ChuДата:
Сообщение: Question about odbc link to Oracle database from PostgreSQL
Следующее
От: Hans-Jürgen SchönigДата:
Сообщение: Re: Question about odbc link to Oracle database from PostgreSQL