Всем привет.
Конфигурация ODBC и гетерогенного сервиса: 1. 2. 3. 4. 5. 6. 7. 8. 9. 10. 11. 12. 13. 14. 15. 16. 17. 18. 19.
[ODBC Data Sources]
root@dev-postgres1:~# cat /etc/odbc.ini
GG_Postgres=DataDirect 6.1 PostgreSQL Wire Protocol
[ODBC]
QEWSD=2460221
IANAAppCodePage=106
InstallDir=/usr/lib/x86_64-linux-gnu/odbc/
[STF_DBS]
Driver=/usr/lib/x86_64-linux-gnu/odbc/psqlodbca.so
Description=DataDirect 6.1 PostgreSQL Wire Protocol
Database=stf_dbs
Server=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 1. 2. 3. 4. 5. 6. 7. 8. 9. 10. 11. 12. 13. 14. 15.
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/network/admin/listener.ora 1. 2. 3. 4. 5. 6. 7. 8. 9. 10. 11. 12. 13. 14. 15. 16. 17. 18. 19. 20. 21. 22. 23. 24. 25. 26. 27. 28.
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)
)
)
Тестовая таблица на стороне PostgreSQL 15: 1. 2. 3. 4. 5. 6. 7. 8. 9. 10. 11. 12. 13. 14. 15. 16. 17. 18. 19. 20. 21. 22. 23. 24. 25. 26. 27. 28. 29. 30. 31. 32. 33. 34. 35. 36. 37. 38. 39. 40. 41. 42. 43. 44. 45. 46. 47. 48. 49. 50. 51. 52. 53. 54. 55. 56. 57. 58. 59. 60. 61. 62. 63. 64. 65. 66. 67. 68. 69. 70. 71. 72. 73. 74. 75.
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
$$;
Через isql все работает:
oracle@dev-postgres1:~$ isql -v STF_DBS srv_goldengate my_postgres_password 1. 2. 3. 4. 5. 6. 7. 8. 9. 10. 11. 12. 13. 14. 15.
+---------------------------------------+
| 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>
Когда я пытаюсь из ораклового клиента через dblink заселектить данные из таблицы, то получаю следующее:
1. Значения полей varchar больше 255 символов, обрезаются до 255 символов.
2. Значения полей varchar больше 2000 символов в Oracle приходят вообще, как тип LONG.
Вопрос: что и где нужно подкрутить, чтобы этого избежать?
|