Re: Question about odbc link to Oracle database from PostgreSQL

hello ...

in SQL this "*" has to be known at parsing time ... so, it needs the number / type of columns before doing anything else.
as you select "some" data from oracle (the postgres parser does not look into the oracle sql) you have to tell the system which data to expect.
it is somehow like this:

> select * FROM odbclink.query(1, 'SELECT * FROM test') as result(user_name text,score number);

so, first of all it is a set returning function.
secondly it needs this magic AS clause.
the data types have to map nicely.

many thanks,

hans

On May 11, 2011, at 12:03 PM, Hsien-Wen Chu wrote:

> 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