Oracle 存储过程迁移到 OceanBase 数据库后执行报语法报错

适用版本

OceanBase 数据库所有版本。

问题现象

Oracle 数据库迁移到 OceanBase 数据库 Oracle 租户，相同的存储过程在 Oracle 数据库可以执行，在 OceanBase 数据库中报语法错误。

call pro_table_demo('t_cc_demo', to_char(sysdate, 'yyyy-mm-dd'));

报错信息：-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 条件包含查找约束类型为 'P' （Primary Key）的字段，OMS 迁移时对部分分区表的主键转换为了唯一键，导致约束类型为 'P' 的结果为空，从而导致 SQL 拼接错误，产生报错。

问题诊断

对于存储过程报语法错误的问题，常见的诊断方式是执行一次该存储过程，获取报错 SQL 的 trace_id，从日志中获取到实际传入变量后的真实 SQL 进行排查判断。

1. 获取该存储过程的 trace 信息，具体给出操作步骤，获取方式如下。

   1. 执行以下命令开启 Show Trace 功能。

      set ob_enable_trace_log=on; 
      

   2. 执行 SQL。

   3. 执行以下命令查询。show trace 后会有 trace_id。

      show trace; 
      

   4. 用这个 trace_id 去查 gv$sql_audit 表获取 svr_ip 值得到实际运行该 SQL 的 OBServer 服务器 IP。

   5. 在该 IP 的主机上，grep trace_id /home/admin/oceanbase/log/observer.log。

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)
   

3. 手工执行获取到的 SQL 观测是否有相同报错。

   经过验证报错与存储过程执行时相同，基本确定是由该 SQL 导致,开始针对该 SQL 做进一步诊断。

4. 分析该 SQL 报错的原因。

   可以看到 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 
   -- ...... 以下部分省略
   

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;
   

   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. 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) || ') ';
      

   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);
      

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)
   

   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. 横向对比 Oracle 中该表的约束信息，得到如下，可以看到 Oracle 侧 SRT_ID 字段确实存在主键类型约束，但在 OceanBase 侧转为了唯一键约束。

解决方法

将获取主键的 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 数据库中，分区表是堆表结构，数据和索引分开，分区键可以不是主键或者主键的一部分；在 OceanBase 数据库中分区表是索引组织表，需要分区键是主键或主键的一部分。当 Oracle 侧分区键不是主键或主键一部分时，为了在 OceanBase 数据库侧能成功构建分区表，OMS 会对主键约束进行转换改为唯一性约束，以便能正常创建分区表。