TechQA.

Problems with datatype conversions through the database link from Oracle 19 to PostgreSQL 15

225 views Asked by At

I've created the database link from my Oracle 19c database to PostgreSQL 15 database. Here is the configuration:

root@dev-postgres1:~# cat /etc/odbc.ini

[ODBC Data Sources]
GG_Postgres=DataDirect 6.1 PostgreSQL Wire Protocol

[ODBC]
QEWSD=2460221
IANAAppCodePage=106
InstallDir=/usr/lib/x86_64-linux-gnu/odbc/

[STF_DBS]
#Driver=/opt/gg/lib/GGpsql25.so
Driver=/usr/lib/x86_64-linux-gnu/odbc/psqlodbca.so
Description=DataDirect 6.1 PostgreSQL Wire Protocol
#Client_encoding=UTF-8
Database=stf_dbs
#Server=10.36.201.155
Server=127.0.0.1
#Hostname=127.0.0.1
PortNumber=5432
LogonID         = srv_goldengate
Password = my_postgres_password

oracle@nbs-testing> cat /home/oracle/u01/app/product/19.3/db_home/hs/admin/initSTF_DBS.ora

HS_FDS_CONNECT_INFO = STF_DBS
HS_FDS_SQLLEN_INTERPRETATION = 64
HS_RPC_FETCH_REBLOCKING= OFF
HS_FDS_FETCH_ROWS = 1
HS_KEEP_REMOTE_COLUMN_SIZE=ALL
#HS_FDS_TRACE_LEVEL = 4
HS_FDS_TRACE_LEVEL = 255
HS_FDS_SHAREABLE_NAME = /usr/pgsql-15/lib/psqlodbcw.so
HS_NLS_NCHAR=UCS2
HS_FDS_TRACE_LEVEL=ON
set ODBCINI=/etc/odbc.ini
HS_NLS_LENGTH_SEMANTICS=BYTE
NLS_LANGUAGE=AMERICAN
NLS_TERRITORY=AMERICA
NLS_CHARACTERSET=WE8ISO8859P9

oracle@nbs-testing> cat /home/oracle/u01/app/product/19.3/db_home/hs/admin/initSTF_DBS.ora

HS_FDS_CONNECT_INFO = STF_DBS
HS_FDS_SQLLEN_INTERPRETATION = 64
HS_RPC_FETCH_REBLOCKING= OFF
HS_FDS_FETCH_ROWS = 1
HS_KEEP_REMOTE_COLUMN_SIZE=ALL
#HS_FDS_TRACE_LEVEL = 4
HS_FDS_TRACE_LEVEL = 255
HS_FDS_SHAREABLE_NAME = /usr/pgsql-15/lib/psqlodbcw.so
HS_NLS_NCHAR=UCS2
HS_FDS_TRACE_LEVEL=ON
set ODBCINI=/etc/odbc.ini
HS_NLS_LENGTH_SEMANTICS=BYTE
NLS_LANGUAGE=AMERICAN
NLS_TERRITORY=AMERICA
NLS_CHARACTERSET=WE8ISO8859P9

oracle@nbs-testing>

oracle@nbs-testing> cat /home/oracle/u01/app/product/19.3/db_home/network/admin/listener.ora

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = orcl)
      (ORACLE_HOME = /home/oracle/u01/app/product/19.3/db_home)

    )

  )

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 10.32.201.136)(PORT = 1521))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
  )

SID_LIST_LISTENER =
   (SID_LIST=
      (SID_DESC=
      (SID_NAME=STF_DBS)
      (ORACLE_HOME=/home/oracle/u01/app/product/19.3/db_home)
      (CONNECT_DATA=(SID=STF_DBS))
      (PROGRAM=dg4odbc)
      (ENVS=LD_LIBRARY_PATH=/usr/lib64:/home/oracle/u01/app/product/19.3/db_home/lib)
      )
   )

Here is the script for creation of the test table:

DROP TABLE IF EXISTS slm.test_table;

CREATE TABLE IF NOT EXISTS slm.test_table
(
id integer NOT NULL,
name character varying(200) COLLATE pg_catalog."default",
flag boolean,
guid uuid,
date_field date,
timestamp_0 timestamp(0) without time zone,
timestamp_3 timestamp(3) without time zone,
timestamp_6 timestamp(6) without time zone,
timestamp_unlimited timestamp without time zone,
int16 smallint,
int32 integer,
int64 bigint,
varchar_10 varchar(10),
varchar_1000 varchar(1000),
varchar_2000 varchar(2000),
--varchar_2001 varchar(2001), -- ломает работу через dblink
varchar_unlimited varchar,
text_field text, -- ORA-00997: illegal use of LONG datatype (при выполнении create table)
json_field json,
money_in_rubles public.money_in_rubles,
bytea_field bytea
CONSTRAINT test_table_pkey PRIMARY KEY (id)
);

grant all on slm.test_table to srv_oracle_dblink;

DO
$$
DECLARE
  ts1 timestamp := '2023-09-27 12:01:02.123456'::timestamp;
  ts2 timestamp := '2023-09-27 12:01:02.987654'::timestamp;
  str10 varchar := 'а' || REPEAT('.', 8) || 'я';
  str1000 varchar := 'а' || REPEAT('.', 998) || 'я';
  str2000 varchar := 'а' || REPEAT('.', 1998) || 'я';
  str10000 varchar := 'а' || REPEAT('.', 9998) || 'я';
  str100000 varchar := 'а' || REPEAT('.', 99998) || 'я';
  json10 json := '{ "a": 1 }';
  json300 json := '{ "a1": "123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890", "a2": "123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890", "a3": "123456789012345678901234567890123456789012345678901234567890123456789012345678901234567" }';
  byte16 bytea := '\x00112233445566778899AABBCCDDEEFF'::bytea;
BEGIN
insert into slm.test_table (id, date_field, name) values (0, '2023-09-27'::date, 'Проверка date 2023-09-27');

insert into slm.test_table (id, timestamp_0, timestamp_3, timestamp_6, timestamp_unlimited, name) values (10, ts1, ts1, ts1, ts1, 'Проверка timestamp 2023-09-27 12:01:02.123456');
insert into slm.test_table (id, timestamp_0, timestamp_3, timestamp_6, timestamp_unlimited, name) values (11, ts2, ts2, ts2, ts2, 'Проверка timestamp 2023-09-27 12:01:02.987654');

insert into slm.test_table (id, flag, name) values (20, false, 'Проверка boolean false');
insert into slm.test_table (id, flag, name) values (21, true, 'Проверка boolean true');

insert into slm.test_table (id, guid, name) values (30, gen_random_uuid(), 'Проверка uuid');
insert into slm.test_table (id, guid, name) values (31, gen_random_uuid(), 'Проверка uuid');

insert into slm.test_table (id, int16, int32, int64, name) values (40, -32768, -2147483648, -9223372036854775808, 'Проверка граничных целочисленных значений');
insert into slm.test_table (id, int16, int32, int64, name) values (41, 32767, 2147483647, 9223372036854775807, 'Проверка граничных целочисленных значений');

insert into slm.test_table (id, varchar_10, varchar_1000, varchar_2000, name) values (50, str10, str1000, str2000, 'Проверка varchar(x)');
insert into slm.test_table (id, varchar_unlimited, name) values (51, str100000, 'Проверка varchar');

insert into slm.test_table (id, text_field, name) values (60, str10::text, 'Проверка text 10');
insert into slm.test_table (id, text_field, name) values (61, str2000::text, 'Проверка text 2000');
insert into slm.test_table (id, text_field, name) values (62, str10000::text, 'Проверка text 10000');
--insert into slm.test_table (id, text_field, name) values (63, str100000::text, 'Проверка text 100000'); -- arraycopy: last source index 32769 out of bounds for byte[32768]

insert into slm.test_table (id, json_field, name) values (70, json10, 'Проверка json 10');
--insert into slm.test_table (id, json_field, name) values (71, json300, 'Проверка json 300'); -- arraycopy: length -18 is negative

insert into slm.test_table (id, money_in_rubles, name) values (80, 1234567.89, 'Проверка money_in_rubles');

insert into slm.test_table (id, bytea_field, name) values (90, byte16, 'Проверка bytea 16');

END
$$;

So, when I'm trying to check the ODBC connection via isql, it working properly: oracle@dev-postgres1:~$ isql -v STF_DBS srv_goldengate my_postgres_password

+---------------------------------------+
| Connected!                            |
|                                       |
| sql-statement                         |
| help [tablename]                      |
| quit                                  |
|                                       |
+---------------------------------------+
SQL>
SQL> SELECT id, name, flag, guid, date_field, timestamp_0, timestamp_3, timestamp_6, timestamp_unlimited, int16, int32, int64, varchar_10, varchar_1000, varchar_2000, varchar_unlimited, text_field, json_field, money_in_rubles, byte16
        FROM slm.test_table
.........
SQLRowCount returns 17
17 rows fetched
SQL>

When I'm trying to query varchar fields with length more than 255 chars, the values truncated to 255 chars. The fields with varchar more than 2000 comes to Oracle as LONG datatype.

  1. How to prevent the conversion from PostgreSQL varchar to Oracle LONG datatype?
  2. How to prevent the truncation of varchar length more than 255 values?
postgresql oracle odbc heterogeneous-services
Original Q&A
0

There are 0 answers

Related Questions in POSTGRESQL

Related Questions in ORACLE

Related Questions in ODBC

Related Questions in HETEROGENEOUS-SERVICES

Popular Questions

Popular Tags

javascript python java c# php android html jquery c++ css ios sql mysql r reactjs node.js arrays c asp.net json

Trending Questions