设置黄金门复制过程时出错，JSON 文档转换为 rdbms 表

Question

我正在尝试使用oracle goldengate将存储在oracle表（源）中的json文档作为clob值转换并复制到目标数据库（oracle）作为rdbms表：

GGSCI (ggsrv2.localdomain) 39> view params RJSON3
Replicat rjson3
DiscardFile ./dirrpt/rjson3.dsc, Purge
USERID ogg, PASSWORD oracle
Map HR.JSON_TEST_SOURCE, Target HRTRG.JSON_TEST_TARGET,
SQLEXEC (id lookup, QUERY 'SELECT json_value(jsondoc, '$.IDNumber') AS J_ID, json_value(jsondoc, '$.Description') AS J_DESCRIPTION, json_value(jsondoc, '$.DecimalNumber') AS J_DECIMAL FROM
HRTRG.JSON_TEST_SOURCE
WHERE ID = :V_ID ',
PARAMS (V_ID = ID)),
J_ID = @GETVAL(lookup.J_ID)),
J_DESCRIPTION = @GETVAL(lookup.J_DESCRIPTION)),
J_DECIMAL = @GETVAL(lookup.J_DECIMAL))
);

错误信息：

2019-01-06 07:56:54  WARNING OGG-10173  (rjson3.prm) line 4 column 317: Parsing error, value "'@'" syntax error.
2019-01-06 07:56:54  WARNING OGG-10173  (rjson3.prm) line 4: Parsing error, option [sqlexec] for parameter [map] has unrecognized value "=".
2019-01-06 07:56:54  WARNING OGG-10173  (rjson3.prm) line 4: Parsing error, option [sqlexec] for parameter [map] has unrecognized value "J_ID"."
Database version 12cR2,GoldenGate version 12.3 

源表：

SQL> desc HR.JSON_TEST_SOURCE;
 Name     Null?    Type
 -------  -------- ------------
 ID       NOT NULL NUMBER(10)
 JSONDOC           CLOB

源表上的示例数据：

SQL> col JSONDOC for a55
SQL> select * from HR.JSON_TEST_SOURCE;
        ID JSONDOC
---------- -------------------------------------------------------
      1600 { "IDNumber"      : 01,
              "Description"   : "I am Shanoj",
              "DecimalNumber" :
      1601 { "IDNumber"      : 02,
              "Description"   : "I am Joby",
              "DecimalNumber" : 1
      1602 { "IDNumber"      : 03,
              "Description"   : "I am Siriram",
              "DecimalNumber"
      1603 { "IDNumber"      : 04,
              "Description"   : "No idea who I am",
              "DecimalNumb

目标表：

SQL> desc HRTRG.JSON_TEST_TARGET
 Name           Null?    Type
 -------------- -------- --------------
 J_ID           NOT NULL VARCHAR2(4000)
 J_DESCRIPTION           VARCHAR2(4000)
 J_DECIMAL               VARCHAR2(4000)

Answer 1

看起来您可以使用一些适配器。另一种选择是使用目标中的实例化视图，使用 json_values（） 将 JSon 值作为记录获取。