| Postgrsql remote select only Ok with sqlplus [message #578403] |
Wed, 27 February 2013 10:55  |
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  |
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.
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
