客户反馈一个存储过程从 Oracle 迁移到 OB Oracle 模式后，执行报语法错误。
作者：余振兴，爱可生 DBA 团队成员，热衷技术分享、编写技术文档。爱可生开源社区出品，原创内容未经授权不得随意使用，转载请联系小编并注明来源。本文共 2400 字，预计阅读需要 8 分钟。
背景信息
客户反馈一个存储过程从 Oracle 迁移到 OB Oracle 模式后，执行报语法错误。报错如下：
call pro_table_demo('t_cc_demo', to_char(sysdate, 'yyyy-mm-dd'));
报表库
报错信息在 p17_db_log 中，报错信息：
-5001 ; ORA-00900: You have an error in your SQL syntax; check the manual that corresponds to your OceanBase version for the right syntax to use near ') when matched then update set a.REMINDER_COUNT=b.REMINDER_COUNT,a.EXT_CUST_NO1' at line 1
问题诊断
这类报语法错误的 SQL 通常的诊断方式是执行一遍,获取该 SQL 的 trace_id，从日志中获取到实际传入变量后的真实 SQL 进行排查判断。
2.1 获取该存储过程的 trace 信息
具体给出操作步骤，获取方式如下：
- 执行 set ob_enable_trace_log=on;
- 执行问题 SQL
- 执行 show trace;上面 show trace 后会有 trace_id
- 用这个 trace_id 去查 gv$sql_audit 表获取 svr_ip 值得到实际运行该 SQL 的 observer 服务器 IP
- 去这台 IP 的主机执行 grep trace_id /home/admin/oceanbase/log/observer.log
2.2 定位报错语句
基于获取到的 trace log 信息结合报错位点找到实际报错的 SQL 语句。
如用报错中 when matched then update set a.REMINDER_COUNT=b.REMINDER_COUNT 部分进行匹配，得到以下 SQL（SQL 做了字段精简）。
merge into t_cc_demo a using
(select REMINDER_COUNT,...,ELECTRICALPIN_EMPLOY_NAME
from t_cc_demo@dblink_demo
where lastupt_dttm >= to_date('2023-02-16','yyyy-mm-dd')
and lastupt_dttm<to_date('2023-02-16','yyyy-mm-dd')+1) b on ()
when matched then
update set a.REMINDER_COUNT=b.REMINDER_COUNT,...,a.ELECTRICALPIN_EMPLOY_NAME=b.ELECTRICALPIN_EMPLOY_NAME
when not matched then
insert (REMINDER_COUNT,...,ELECTRICALPIN_EMPLOY_NAME) values (b.REMINDER_COUNT,...,b.ELECTRICALPIN_EMPLOY_NAME)
2.3 对比报错
手工执行获取到的 SQL 观测是否有相同报错。经过验证报错与存储过程执行时相同，基本确定是由该 SQL 导致。开始针对该 SQL 做进一步诊断。
2.4 分析报错原因
可以看到该 SQL 条件中存在 ON 后的括号匹配关联条件为空，初步判断是这块条件缺失导致，需要进一步分析存储过程中的逻辑进行判断。
merge into t_cc_demo a using (select REMINDER_COUNT,...ELECTRICALPIN_EMPLOY_NAME
from t_cc_demo@dblink_demo
where lastupt_dttm >= to_date('2023-02-16','yyyy-mm-dd')
and lastupt_dttm<to_date('2023-02-16','yyyy-mm-dd')+1) b
on () -- 存在问题的点,关联条件不存在
when matched then update set
-- ...... 以下部分省略
2.5 分析存储过程中的定义
完整的存储过程定义如下：
create or replace procedure pro_table_demo(p_par_table in varchar2,
archive_date in varchar2) is
... 存储过程较长，部分无关代码省略
--取表所有字段
cursor c_column is
select t.column_name
from user_tab_columns t
where t.table_name = upper(p_par_table);
--取表除主键外的字段
cursor c_not_pkey is
select t.column_name
from user_tab_columns t
where t.table_name = upper(p_par_table)
and t.column_name not in
(select col.column_name
from user_constraints con, user_cons_columns col
where con.constraint_name = col.constraint_name
and con.constraint_type = 'P'
and col.table_name = upper(p_par_table));
--取表的主键
cursor c_pkey is
select col.column_name
from user_constraints con, user_cons_columns col
where con.constraint_name = col.constraint_name
and con.constraint_type = 'P'
and col.table_name = upper(p_par_table);
begin
for c1 in c_column loop
v_column := v_column || c1.column_name || ',';
v_column_insert := v_column_insert || 'b.' || c1.column_name || ',';
end loop;
v_column1 := substr(v_column, 0, length(v_column) - 1);
v_column_insert1 := '(' || substr(v_column_insert,
0,
length(v_column_insert) - 1) || ') ';
for c2 in c_not_pkey loop
v_column_update := v_column_update || 'a.' || c2.column_name || '=b.' ||
c2.column_name || ',';
end loop;
v_column_update1 := substr(v_column_update,
0,
length(v_column_update) - 1);
for c3 in c_pkey loop
v_column_pkey := v_column_pkey || 'b.' || c3.column_name || '=a.' ||
c3.column_name || ' and ';
end loop;
v_column_pkey1 := '(' ||
substr(v_column_pkey, 0, length(v_column_pkey) - 5) || ') ';
v_sql_str := 'merge into ' || upper(p_par_table) || ' a ' ||
'using (select ' || v_column1 || ' from ' ||
upper(p_par_table) ||
'@dblink_demo where lastupt_dttm >= ' ||
'to_date(''' || archive_date || ''',''yyyy-mm-dd'')' ||
' and lastupt_dttm<' ||
'to_date(''' || archive_date || ''',''yyyy-mm-dd'')+1' ||
') b on ';
v_sql_str1 := v_column_pkey1 || ' when matched then update set ';
v_sql_str3 := v_column_update1 || ' when not matched then insert (';
v_sql_str2 := v_column1 || ') values ' || v_column_insert1;
execute immediate v_sql_str || v_sql_str1 || v_sql_str3 || v_sql_str2;
v_all_cnt := sql%rowcount;
commit;
--统计变动的记录数
v_ins_cnt := 0;
v_upd_cnt := 0;
v_del_cnt := 0;
v_step_tm := v_step_tm || 'step1=' ||
round((sysdate - v_end_tm) * 24 * 60 * 60) || '秒';
v_end_tm := sysdate;
end pro_table_demo;
2.5.1 分析 ①
结合报错的位点，可以知道 问题主要出现在 v_sql_str 定义的 SQL 结尾以及 v_sql_str1 定义的开头部分。v_sql_str1 开头部分拼接的 SQL 存在异常，进一步分析 v_sql_str1 的具体获取方式。
v_sql_str := 'merge into ' || upper(p_par_table) || ' a ' ||
'using (select ' || v_column1 || ' from ' ||
upper(p_par_table) ||
'@dblink_demo where lastupt_dttm >= ' ||
'to_date(''' || archive_date || ''',''yyyy-mm-dd'')' ||
' and lastupt_dttm<' ||
'to_date(''' || archive_date || ''',''yyyy-mm-dd'')+1' ||
') b on ';
v_sql_str1 := v_column_pkey1 || ' when matched then update set ';
---- 省略部分无关代码
execute immediate v_sql_str || v_sql_str1 || v_sql_str3 || v_sql_str2;
2.5.2 分析 ②
v_sql_str 变量的值具体是 v_column_pkey1 变量定义，而 v_column_pkey1 变量引用的是 v_column_pkey 变量定义，继续往上追溯。
for c3 in c_pkey loop
v_column_pkey := v_column_pkey || 'b.' || c3.column_name || '=a.' ||
c3.column_name || ' and ';
end loop;
v_column_pkey1 := '(' ||
substr(v_column_pkey, 0, length(v_column_pkey) - 5) || ') ';
2.5.3 分析 ③
定位到 v_column_pkey 是由游标 c_pkey 定义的 SQL 获取得到，找到游标的 SQL 定义进行分析。
cursor c_pkey is
select col.column_name
from user_constraints con, user_cons_columns col
where con.constraint_name = col.constraint_name
and con.constraint_type = 'P'
and col.table_name = upper(p_par_table);
2.6 具体分析定位后的 SQL 语句
套入具体的表名对该游标 SQL 进行查询，发现返回值为空，获取不到该表的主键信息。
-- 无记录返回
select col.column_name
from user_constraints con, user_cons_columns col
where con.constraint_name = col.constraint_name
and con.constraint_type = 'P'
and col.table_name = upper('t_cc_demo');
Empty set (1.35 sec)
2.6.1 分析 ①
查询该表的所有约束条件,发现该表不包含 constraint_type = 'P' 的主键约束，但包含一个对 SRT_ID 字段的唯一键及非空约束，且从 constraint_name 中 PK_t_cc_demo 约束名判断，该字段确实为该表的主键。
select col.column_name,constraint_type,con.constraint_name
from user_constraints con, user_cons_columns col
where con.constraint_name = col.constraint_name
and col.table_name = upper('t_cc_demo');
2.6.2 分析 ②
横向对比 Oracle 中该表的约束信息，得到如下图。可以看到 Oracle 侧 SRT_ID 字段确实存在主键类型约束，但在 OB 侧转为了唯一键约束。
2.7 小结
- 由于迁移后游标获取主键字段时，匹配不到约束类型为
P的字段，以致于后续 SQL 拼接出现条件为空，从而导致 SQL 报语法错误。 - 该问题主要是由于 OMS 迁移时对部分分区表的主键转换为了唯一键导致，具体转换原因和逻辑参考案例最后的知识扩展。
修复方式
将获取主键的 SQL 调整为取唯一键约束类型，并且约束名称为 PK 开头（排除其他唯一键的干扰）。
--取表的主键（修改前）
cursor c_pkey is
select col.column_name
from user_constraints con, user_cons_columns col
where con.constraint_name = col.constraint_name
and con.constraint_type = 'P'
and col.table_name = upper(p_par_table);
--取表的主键（修改后）
cursor c_pkey is
select col.column_name
from user_constraints con, user_cons_columns col
where con.constraint_name = col.constraint_name
and con.constraint_type in ('U','P')
and con.constraint_name like 'PK%'
and col.table_name = upper(p_par_table);
知识扩展
在 Oracle 中，分区表是堆表结构，数据和索引分开，分区键可以不是主键或者主键的一部分；在 OB 中分区表是索引组织表，需要分区键是主键或主键的一部分。当 Oracle 侧分区键不是主键或主键一部分时，为了在 OB 侧能成功构建分区表，OMS 会对主键约束进行转换改为唯一性约束，以便能正常创建分区表，以下是验证哪些分区表会进行主键转换的示例：
-- ------------------- Oracle 侧表结构
-- 1. 主键就是分区键
CREATE TABLE "T_PARTKEY_IS_PK" (
"ACT_ID" NUMBER(10,0) NOT NULL,
"SRT_ID" NUMBER(10,0),
"SRT_ORIGNAL_ID" NUMBER(10,0),
"CRT_DTTM" DATE,
"LASTUPT_DTTM" DATE,
CONSTRAINT "PK_T_PARTKEY_IS_PK" PRIMARY KEY ("CRT_DTTM")
)
PARTITION BY RANGE ("CRT_DTTM")
(
PARTITION "P201512" VALUES LESS THAN (TO_DATE(' 2016-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')),
PARTITION "PMAX" VALUES LESS THAN (MAXVALUE)
);
-- 2. 主键不是分区键
CREATE TABLE "T_PARTKEY_NOT_PK" (
"ACT_ID" NUMBER(10,0) NOT NULL,
"SRT_ID" NUMBER(10,0),
"SRT_ORIGNAL_ID" NUMBER(10,0),
"CRT_DTTM" DATE,
"LASTUPT_DTTM" DATE,
CONSTRAINT "PK_T_PARTKEY_NOT_PK" PRIMARY KEY ("ACT_ID")
)
PARTITION BY RANGE ("CRT_DTTM")
(
PARTITION "P201512" VALUES LESS THAN (TO_DATE(' 2016-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')),
PARTITION "PMAX" VALUES LESS THAN (MAXVALUE)
);
-- 3. 主键是分区键的一部分,且分区键是主键多列中的第一列
CREATE TABLE "T_PARTKEY_IS_FIRST_COLUMNS_PK" (
"ACT_ID" NUMBER(10,0) NOT NULL,
"SRT_ID" NUMBER(10,0),
"SRT_ORIGNAL_ID" NUMBER(10,0),
"CRT_DTTM" DATE,
"LASTUPT_DTTM" DATE,
CONSTRAINT "PK_T_PARTKEY_IS_FIRST_COLUMNS" PRIMARY KEY ("CRT_DTTM","ACT_ID")
)
PARTITION BY RANGE ("CRT_DTTM")
(
PARTITION "P201512" VALUES LESS THAN (TO_DATE(' 2016-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')),
PARTITION "PMAX" VALUES LESS THAN (MAXVALUE)
);
-- 4. 主键是分区键的一部分,且分区键不是主键多列中的第一列
CREATE TABLE "T_PARTKEY_NOT_FIRST_COLUMNS_PK" (
"ACT_ID" NUMBER(10,0) NOT NULL,
"SRT_ID" NUMBER(10,0),
"SRT_ORIGNAL_ID" NUMBER(10,0),
"CRT_DTTM" DATE,
"LASTUPT_DTTM" DATE,
CONSTRAINT "PK_T_PARTKEY_NOT_FIRST_COLUMNS" PRIMARY KEY ("ACT_ID","CRT_DTTM")
)
PARTITION BY RANGE ("CRT_DTTM")
(
PARTITION "P201512" VALUES LESS THAN (TO_DATE(' 2016-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')),
PARTITION "PMAX" VALUES LESS THAN (MAXVALUE)
);
-- -------------------- Oracle 侧约束状态
col table_name for a30
col COLUMN_NAME for a10
col CONSTRAINT_NAME for a30
col CONSTRAINT_TYPE for a10
SELECT CON.TABLE_NAME,
COL.COLUMN_NAME,
CON.CONSTRAINT_NAME,
CON.CONSTRAINT_TYPE
FROM USER_CONSTRAINTS CON, USER_CONS_COLUMNS COL
WHERE CON.CONSTRAINT_NAME = COL.CONSTRAINT_NAME
AND CON.CONSTRAINT_TYPE = 'P'
AND CON.TABLE_NAME LIKE 'T_PARTKEY%'
ORDER BY CON.TABLE_NAME, COL.POSITION;
TABLE_NAME COLUMN_NAM CONSTRAINT_NAME CONSTRAINT
------------------------------ ---------- ------------------------------ ----------
T_PARTKEY_IS_FIRST_COLUMNS_PK ACT_ID PK_T_PARTKEY_IS_FIRST_COLUMNS P
T_PARTKEY_IS_FIRST_COLUMNS_PK CRT_DTTM PK_T_PARTKEY_IS_FIRST_COLUMNS P
T_PARTKEY_IS_PK CRT_DTTM PK_T_PARTKEY_IS_PK P
T_PARTKEY_NOT_FIRST_COLUMNS_PK CRT_DTTM PK_T_PARTKEY_NOT_FIRST_COLUMNS P
T_PARTKEY_NOT_FIRST_COLUMNS_PK ACT_ID PK_T_PARTKEY_NOT_FIRST_COLUMNS P
T_PARTKEY_NOT_PK ACT_ID PK_T_PARTKEY_NOT_PK P
6 rows selected.
-- --------------------------------- 通过OMS迁移到OB侧约束状态
SELECT CON.TABLE_NAME,
COL.COLUMN_NAME,
CON.CONSTRAINT_NAME,
CON.CONSTRAINT_TYPE
FROM USER_CONSTRAINTS CON, USER_CONS_COLUMNS COL
WHERE CON.CONSTRAINT_NAME = COL.CONSTRAINT_NAME
AND CON.CONSTRAINT_NAME NOT LIKE '%OMS_ROWID'
AND CON.CONSTRAINT_NAME NOT LIKE '%OBNOTNULL%'
ORDER BY CON.TABLE_NAME, COL.POSITION;
+--------------------------------+-------------+--------------------------------+-----------------+
| TABLE_NAME | COLUMN_NAME | CONSTRAINT_NAME | CONSTRAINT_TYPE |
+--------------------------------+-------------+--------------------------------+-----------------+
| T_PARTKEY_IS_FIRST_COLUMNS_PK | ACT_ID | PK_T_PARTKEY_IS_FIRST_COLUMNS | P |
| T_PARTKEY_IS_FIRST_COLUMNS_PK | CRT_DTTM | PK_T_PARTKEY_IS_FIRST_COLUMNS | P |
| T_PARTKEY_IS_PK | CRT_DTTM | PK_T_PARTKEY_IS_PK | P |
| T_PARTKEY_NOT_FIRST_COLUMNS_PK | CRT_DTTM | PK_T_PARTKEY_NOT_FIRST_COLUMNS | P |
| T_PARTKEY_NOT_FIRST_COLUMNS_PK | ACT_ID | PK_T_PARTKEY_NOT_FIRST_COLUMNS | P |
| T_PARTKEY_NOT_PK | ACT_ID | PK_T_PARTKEY_NOT_PK | U |
+--------------------------------+-------------+--------------------------------+-----------------+
6 rows in set (0.16 sec)
-- [INFO] [CONVERT] CONSTRAINT "PK_T_PARTKEY_NOT_PK" PRIMARY KEY ("ACT_ID") -> CONSTRAINT "PK_T_PARTKEY_NOT_PK" UNIQUE ("ACT_ID")
结论
当 Oracle 侧的主键不包含分区键时，OMS 会将 Oracle 侧的主键改为唯一键，但保持约束名一致且 OMS 在做表结构迁移时，会给到存在转换的注释信息。
对于存储过程的报错或者 SQL 执行报错，均可使用以上方法获取到实际的 SQL 执行的 trace id 进行日志诊断，拿到具体的报错 SQL 进行分析。
