Home » RDBMS Server » Networking and Gateways » Postgrsql remote select only Ok with sqlplus (Oracle 11.2.0.1.0)
Postgrsql remote select only Ok with sqlplus [message #578403] Wed, 27 February 2013 10:55 Go to next message
metabaron
Messages: 13
Registered: September 2010
Junior Member
Hi all
I have configured Oracle Gateway included in Oracle 11.2.0.1.0 to acces a remote PostgreSQL database 8.3 using an Oracle DBlink.

Postgrsql odbc driver:
postgresql92-odbc-09.01.0200-1PGDG.rhel5
postgresql92-libs-9.2.3-2PGDG.rhel5

Odbc Driver manager
unixODBC-2.2.11-7.1

I can successfully select a colunm with sqlplus:

SQL> desc "usr"@davical;
 Nom                                       NULL ?   Type
 ----------------------------------------- -------- ----------------------------
 user_no                                   NOT NULL NUMBER(10)
 active                                             LONG
 email_ok                                           DATE
 joined                                             DATE
 updated                                            DATE
 last_used                                          DATE
 username                                  NOT NULL LONG
 password                                           LONG
 fullname                                           LONG
 email                                              LONG
 config_data                                        LONG
 date_format_type                                   LONG
 locale                                             LONG

SQL>select "username" from "usr"@davical
username
--------------------------------------------------------------------------------
jcasena2
bdagorre
...


But when using SQL Developper or OEM , il have the same line as result:

SQL>select "username" from "usr"@davical
username
--------------------------------------------------------------------------------
VPCS
VPCS
VPCS
VPCS
...


I suppose that there is something to do whith a "text" -> LONG column conversion. Here is the Postgresl table description:
Table "public.usr"
Column | Type | Modifiers
------------------+--------------------------+-------------------------------------------------------
user_no | integer | not null default nextval('usr_user_no_seq'::regclass)
active | boolean | default true
email_ok | timestamp with time zone |
joined | timestamp with time zone | default now()
updated | timestamp with time zone |
last_used | timestamp with time zone |
username | text | not null
password | text |
fullname | text |
email | text |
config_data | text |
date_format_type | text | default 'E'::text
locale | text |


I also have notice this difference in $ORACLE_HOME/hs/log trace:

SQLPLUS:
hoadaMOD bit-values found (0x20:NEGATIVE_HOADADTY,0x200:TREAT_AS_CHAR)
DTY NULL-OK LEN MAXBUFLEN PR/SC CST IND MOD NAME
-1 LONGVARCHAR N 80 0 0/ 0 0 0 220 username
Performing delayed open.
SQLBindCol: column 1, cdatatype: 1, bflsz: 81
SQLFetch: row: 1, column 1, bflsz: 81, bflar: 4
SQLFetch: row: 1, column 1, bflsz: 81, bflar: 4, (bfl: 80, mbl: 0)
1 rows fetched
Exiting hgoftch, rc=0 at 2013/02/26-16:47:54
Entered hgoftch, cursor id 1 at 2013/02/26-16:47:54
hgoftch, line 130: Printing hoada @ 0x2862dc8
MAX:1, ACTUAL:1, BRC:1, WHT=5 (SELECT_LIST)


SQLDEVELOPPER:
hoadaMOD bit-values found (0x10:PIECEWISE_PARAMETER,0x20:NEGATIVE_HOADADTY,0x200:TREAT_AS_CHAR)
DTY NULL-OK LEN MAXBUFLEN PR/SC CST IND MOD NAME
-1 LONGVARCHAR N 65536 2147483647 0/ 0 0 0 230 username
Performing delayed open.
SQLFetch: row: 1, column 1, bflsz: 65537, bflar: 0
SQLFetch: row: 1, column 1, bflsz: 65537, bflar: 0, (bfl: 65536, mbl: 2147483647)
1 rows fetched
Exiting hgoftch, rc=0 at 2013/02/26-16:49:28
Entered hgoftch, cursor id 1 at 2013/02/26-16:49:28
hgoftch, line 130: Printing hoada @ 0x197ead88
MAX:1, ACTUAL:1, BRC:1, WHT=5 (SELECT_LIST)


It's like if OEM and SQL Developper are unable to retrieve the colunm size...

What I have tried without any change:
- Other postgresql driver (datadirect/Connect64_for_ODBC_71) and postgresql91-odbc-09.00.0310-1PGDG.rhel5.x86_64
- Bypass Driver manager in 11g/hs/admin/initDAVICAL.ora
using HS_FDS_SHAREABLE_NAME =/usr/pgsql-9.2/lib/psqlodbc.so directly instead of HS_FDS_SHAREABLE_NAME = /usr/lib64/libodbc.so
- these HS params I have found in Oracle documentation
HS_DESCRIBE_CACHE_HWM
HS_NLS_LENGTH_SEMANTICS
HS_KEEP_REMOTE_COLUMN_SIZE
HS_FDS_REMOTE_DB_CHARSET
Currently I have:
HS_FDS_SQLLEN_INTERPRETATION = 32
#Postgresl LANGAGE setup
HS_LANGUAGE = FR_FR.UTF-8



Any Idea?
Thanks

Re: Postgrsql remote select only Ok with sqlplus [message #578465 is a reply to message #578403] Thu, 28 February 2013 09:31 Go to previous messageGo to next message
metabaron
Messages: 13
Registered: September 2010
Junior Member
Ok it's solved with postgresql driver parameter in /etc/odbc.ini to convert "text" into varchar2 instead of LONG
TextAsLongVarchar = Yes

SQL> desc "usr"@davical;
 Nom                                       NULL ?   Type
 ----------------------------------------- -------- ----------------------------
 user_no                                   NOT NULL NUMBER(10)
 active                                             LONG
 email_ok                                           DATE
 joined                                             DATE
 updated                                            DATE
 last_used                                          DATE
 username                                  NOT NULL VARCHAR2(255)
 password                                           VARCHAR2(255)
 fullname                                           VARCHAR2(255)
 email                                              VARCHAR2(255)
 config_data                                        VARCHAR2(255)
 date_format_type                                   VARCHAR2(255)
 locale                                             VARCHAR2(255)


Now it's OK with SQL Developper and OEM.
Re: Postgrsql remote select only Ok with sqlplus [message #578466 is a reply to message #578465] Thu, 28 February 2013 09:35 Go to previous message
Michel Cadot
Messages: 59203
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Thanks for the feedback.

Regards
Michel
Previous Topic: Oracle Discoverer only for Databases or also file system file APPL_TOP
Next Topic: Oracle Golden gate for SQL server
Goto Forum:
  


Current Time: Tue Sep 23 23:39:06 CDT 2014

Total time taken to generate the page: 0.12143 seconds