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@xxxxxxxxxxx>:
> 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@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
>>>
>>
>
>
> --
> Cybertec Schönig & Schönig GmbH
> Gröhrmühlgasse 26
> A-2700 Wiener Neustadt, Austria
> Web: http://www.postgresql-support.de
>
>
--
Sent via pgsql-admin mailing list (pgsql-admin@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
