暂无图片
暂无图片
暂无图片
暂无图片
暂无图片
首页 / OceanBase存储过程字符集乱码导致无法编辑

OceanBase存储过程字符集乱码导致无法编辑

IT那活儿 2025-06-23
262

点击上方“IT那活儿”公众号--专注于企业全栈运维技术分享，不管IT什么活儿，干就完了！！！

一

问题现象

调用存储过程报错，且无法编辑，通过如下语句获取ddl操作报错：
obclient> select dbms_metadata.get_ddl(upper('PACKAGE BODY'),upper(' P_LOCxxx_MTxxON '),upper('xx')) from dual;
ORA-00600: internal error code, arguments: -4258, Incorrect stringvalue
二

问题分析

2.1 通过sql_audit视图来查看报错代码
MySQL [oceanbase]> select * from gv$sql_audit where request_time>1706497726000000 and query_sql like '%select dbms_metadata.get_ddl% P_LOCxxx_MTxxON%' and RET_CODE<>0\G
*************************** 1. row ***************************
                 SVR_IP: xx.xx.xx.xxx
               SVR_PORT: 2882
             REQUEST_ID: 242892140721
            SQL_EXEC_ID: 257475516626
               TRACE_ID: YB428650F7DC-0005FBD8A016B70E
                    SID: 3222169938
              CLIENT_IP: 134.85.15.131
            CLIENT_PORT: 8740
              TENANT_ID: 1014
            TENANT_NAME: obzy1
    EFFECTIVE_TENANT_ID: 1014
                USER_ID: 1114904790564867
              USER_NAME: SYS
         USER_CLIENT_IP: xx.xx.xx.xxx
                  DB_ID: 1114904790564870
                DB_NAME: SYS
                 SQL_ID: F3906F59E578612BC920FD54A4CE95AD
              QUERY_SQL: select dbms_metadata.get_ddl(upper('PACKAGE BODY'),upper('P_LOCxxx_MTxxON'),upper('IM')) from dual
                PLAN_ID: 102811586
          AFFECTED_ROWS: 0
            RETURN_ROWS: 0
          PARTITION_CNT: 0
               RET_CODE: -4258
                  QC_ID: 0
                 DFO_ID: 0
                 SQC_ID: 0
              WORKER_ID: 0
                  EVENT: system internal wait
                 P1TEXT: 
                     P1: 0
                 P2TEXT: 
                     P2: 0
                 P3TEXT: 
                     P3: 0
                  LEVEL: 0
          WAIT_CLASS_ID: 100
            WAIT_CLASS#: 0
             WAIT_CLASS: OTHER
                  STATE: MAX_WAIT TIME ZERO
        WAIT_TIME_MICRO: 0
  TOTAL_WAIT_TIME_MICRO: 0
            TOTAL_WAITS: 0
              RPC_COUNT: 0
              PLAN_TYPE: 1
           IS_INNER_SQL: 0
        IS_EXECUTOR_RPC: 0
            IS_HIT_PLAN: 1
           REQUEST_TIME: 1706497743441187
           ELAPSED_TIME: 16552
               NET_TIME: 0
          NET_WAIT_TIME: 0
             QUEUE_TIME: 18
            DECODE_TIME: 0
          GET_PLAN_TIME: 152
           EXECUTE_TIME: 16358
  APPLICATION_WAIT_TIME: 0
  CONCURRENCY_WAIT_TIME: 0
      USER_IO_WAIT_TIME: 0
          SCHEDULE_TIME: 0
          ROW_CACHE_HIT: 0
 BLOOM_FILTER_CACHE_HIT: 0
        BLOCK_CACHE_HIT: 57
  BLOCK_INDEX_CACHE_HIT: 10
             DISK_READS: 0
              RETRY_CNT: 0
             TABLE_SCAN: 0
      CONSISTENCY_LEVEL: 3
MEMSTORE_READ_ROW_COUNT: 4
 SSSTORE_READ_ROW_COUNT: 307
    REQUEST_MEMORY_USED: 2096128
  EXPECTED_WORKER_COUNT: 0
      USED_WORKER_COUNT: 0
             SCHED_INFO: 
     FUSE_ROW_CACHE_HIT: 0
      PS_CLIENT_STMT_ID: -1
       PS_INNER_STMT_ID: -1
       TRANSACTION_HASH: 0
           REQUEST_TYPE: 2
  IS_BATCHED_MULTI_STMT: 0
          OB_TRACE_INFO: NULL
              PLAN_HASH: 7051807823916179110
     LOCK_FOR_READ_TIME: 0
  WAIT_TRX_MIGRATE_TIME: 0
1row inset (1 min 31.508 sec)
2.2 查看对应的observer.log日志信息
通过上述步骤查询的trace_id来过滤信息，发现关键信息：charset convert failed 字符转换失败。
observer.log.20240129110919:[2024-01-29 11:09:03.456247] WARN  [COMMON] string_string (ob_obj_cast.cpp:4378) [86348][3460][YB428650F7DC-0005FBD8A016B70E] [lt=89] [dc=0] charset convert failed(ret=-4258, in.get_collation_type()=46, params.dest_collation_=87, params.is_ignore_=false, cast_mode=2305843009213693952)
observer.log.20240129110919:[2024-01-29 11:09:03.456258] WARN  [COMMON] lob_string (ob_obj_cast.cpp:6291) [86348][3460][YB428650F7DC-0005FBD8A016B70E] [lt=9] [dc=0] cast string to string failed(ret=-4258)
observer.log.20240129110919:[2024-01-29 11:09:03.456265] WARN  [COMMON] to_type (ob_obj_cast.cpp:9310) [86348][3460][YB428650F7DC-0005FBD8A016B70E] [lt=5] [dc=0] failed to cast obj(ret=-4258, in_obj={"LOB":LobLocator:{magic_code:1280262689, version:1, snapshot_version:0, table_id:0, column_id:0, flags:1, option:0, payload_offset:0, payload_size:5011, data:"PACKAGE BODY P_LOCxxx_MTxxON IS
observer.log.20240129110919:[2024-01-29 11:09:03.456289] WARN  [SQL.ENG] cast_operand_type (ob_expr_operator.cpp:230) [86348][3460][YB428650F7DC-0005FBD8A016B70E] [lt=22] [dc=0] fail to convert type(ret=-4258, res_type.get_calc_type()=30, res_obj={"LOB":LobLocator:{magic_code:1280262689, version:1, snapshot_version:0, table_id:0, column_id:0, flags:1, option:0, payload_offset:0, payload_size:5011, data:"PACKAGE BODY P_LOCxxx_MTxxON IS
2.3 通过sys租户中的__all_virtual_package视图来查看无法ddl的存储过程
通过如下，可以看到中文字符集乱码：
MySQL [oceanbase]> select * from __all_virtual_package where package_name='P_LOCxxx_MTxxON '\G
*************************** 1. row ***************************
     tenant_id:1014
    package_id:1114904790565897
    gmt_create:2021-05-0710:21:31.574232
gmt_modified:2021-05-0710:21:31.574232
   database_id:1114904790565943
package_name: P_LOCxxx_MTxxON 
schema_version:1620354091572840
          type:1
          flag:0
      owner_id:1114904790565893
     comp_flag:0
      exec_env:2151677954,45,45,87,
        source: P_LOCxxx_MTxxON
PROCEDURE P_LOCxxx_MTxxON (insysdate IN VARCHAR2);
PROCEDURE P_LOCxxx_MTxxON (insysdate IN VARCHAR2);
PROCEDURE P_LOCxxx_MTxxON (insysdate IN VARCHAR2);
PROCEDURE P_LOCxxx_MTxxON (insysdate IN VARCHAR2);
PROCEDURE P_LOCxxx_MTxxON;
END P_LOCxxx_MTxxON
       comment: NULL
     route_sql: NULL
*************************** 2. row ***************************
     tenant_id:1014
    package_id:1114904790565903
    gmt_create:2021-05-0710:21:47.179743
gmt_modified:2021-05-0710:21:47.179743
   database_id:1114904790565943
package_name: P_LOCxxx_MTxxON 
schema_version:1620354107177640
          type:2
          flag:0
      owner_id:1114904790565893
     comp_flag:0
      exec_env:2151677954,45,45,87,
        source: PACKAGE BODY P_LOCxxx_MTxxON IS
PROCEDURE P_LOCxxx_MTxxON (insysdate IN VARCHAR2) IS
BEGIN
--閿€鍞嚭搴撲腑闂磋〃姹囨€诲埌閿€鍞嚭搴撹〃
P_LOCxxx_MTxxON (insysdate);
--璋冩嫧涓棿琛ㄦ眹鎬诲埌璋冩嫧搴撹〃
P_LOCxxx_MTxxON (insysdate);
--閿€鍞€€搴撲腑闂磋〃姹囨€诲埌閿€鍞€€搴撹〃
P_LOCxxx_MTxxON (insysdate);
--涓棿琛ㄤ繚鐣�澶╋紝姝ｅ紡琛ㄤ繚鐣欎笁涓湀鏁版嵁
P_LOC_SAVE_DATA;
COMMIT;
END P_LOCxxx_MTxxON;
--閿€鍞嚭搴撲腑闂磋〃鏁版嵁鎻掑叆閿€鍞嚭搴撹〃
PROCEDURE P_LOCxxx_MTxxON (insysdate IN VARCHAR2) IS
v_sql VARCHAR(4096);
BEGIN
DELETE FROM LOC_SALES_OUTLET t
WHERE t.CREATE_TIME = insysdate;
v_sql := 'INSERT INTO xxxxx (CREATE_TIME,SUBLIBRARY_CODE,MATERIAL_CODE,SALE_NUMBER,ERP_BATCH_ID,ACTIVITY_CODE,ACCOUNT_NAME,OUTLET_STAFF,OUID)
SELECT lo.create_time,lo.sublibrary_code,lo.material_code,count(1),lo.erp_batch_id,lo.activity_code,lo.account_name,lo.outlet_staff,lo.ouid
from xxxxxx lo WHERE lo.create_time='||insysdate||' AND lo.activity_code IS NOT NULL AND lo.account_name IS NOT NULL GROUP BY lo.create_time,lo.sublibrary_code,lo.material_code,lo.erp_batch_id,lo.activity_code,lo.account_name,lo.outlet_staff,lo.ouid';
EXECUTE IMMEDIATE v_sql;
COMMIT;
EXCEPTION WHEN no_data_found THEN
dbms_output.put_line('-->Error:No data found');
dbms_output.put_line('-->SQLCODE:'||SQLCODE||' '|| SQLERRM);
RETURN;
WHEN others THEN
dbms_output.put_line('-->SQLCODE:'||SQLCODE||' '|| SQLERRM);
END P_LOCxxx_MTxxON;
--璋冩嫧涓棿琛ㄦ暟鎹彃鍏ヨ皟鎷ㄨ〃
PROCEDURE xxxx (insysdate IN VARCHAR2) IS
v_sql VARCHAR(4096);
BEGIN
--鏍规嵁鏃堕棿鍜屽湴甯傛竻闄よ〃鏁版嵁
DELETE FROM xxxx t
WHERE t.CREATE_TIME = insysdate;
v_sql := 'INSERT INTO xxxx (CREATE_TIME,ORGANIZATION_CODE_OUT,ORGANIZATION_CODE_IN,ALLOT_TIME
,MATERIAL_CODE,ALLOT_NUMBER,ERP_BATCH_ID,ALLOT_STAFF,OUID)
SELECT lo.CREATE_TIME,lo.ORGANIZATION_CODE_OUT,lo.ORGANIZATION_CODE_IN,lo.ALLOT_TIME,lo.MATERIAL_CODE,count(1),lo.ERP_BATCH_ID,lo.ALLOT_STAFF,lo.OUID
from xxxx lo WHERE lo.create_time='||insysdate||' GROUP BY lo.CREATE_TIME,lo.ORGANIZATION_CODE_OUT,lo.ORGANIZATION_CODE_IN,lo.ALLOT_TIME,lo.MATERIAL_CODE,lo.ALLOT_NUMBER,lo.ERP_BATCH_ID,lo.ALLOT_STAFF,lo.OUID';
EXECUTE IMMEDIATE v_sql;
COMMIT;
EXCEPTION WHEN no_data_found THEN
dbms_output.put_line('-->Error:No data found');
dbms_output.put_line('-->SQLCODE:'||SQLCODE||' '|| SQLERRM);
RETURN;
WHEN others THEN
dbms_output.put_line('-->SQLCODE:'||SQLCODE||' '|| SQLERRM);
END P_LOC_ALLOT_INLET_INSERT;
--閿€鍞€€搴撲腑闂磋〃鏁版嵁鎻掑叆閿€鍞€€搴撹〃
PROCEDURE P_LOC_SALES_CANCEL_INSERT(insysdate IN VARCHAR2) IS
v_sql VARCHAR(4096);
BEGIN
--鏍规嵁鏃堕棿鍜屽湴甯傛竻闄よ〃鏁版嵁
DELETE FROM xxxx t
WHERE t.CREATE_TIME = insysdate;
v_sql := 'INSERT INTO LOC_SALES_CANCEL_STOCKS(CREATE_TIME,SUBLIBRARY_CODE,MATERIAL_CODE,OUTLET_NUMBER,OUTLET_CODE,ERP_BATCH_ID,ACTIVITY_CODE,ACCOUNT_NAME,OUTLET_STAFF,OUID)
SELECT lo.create_time,lo.sublibrary_code,lo.material_code,count(1),lo.outlet_code,lo.erp_batch_id,lo.activity_code,lo.account_name,lo.outlet_staff,lo.ouid
from v lo WHERE lo.create_time='||insysdate||' AND lo.activity_code IS NOT NULL AND lo.account_name IS NOT NULL GROUP BY lo.create_time,lo.sublibrary_code,lo.material_code,lo.outlet_code,lo.erp_batch_id,lo.activity_code,lo.account_name,lo.outlet_staff,lo.ouid';
EXECUTE IMMEDIATE v_sql;
COMMIT;
EXCEPTION WHEN no_data_found THEN
dbms_output.put_line('-->Error:No data found');
dbms_output.put_line('-->SQLCODE:'||SQLCODE||' '|| SQLERRM);
RETURN;
WHEN others THEN
dbms_output.put_line('-->SQLCODE:'||SQLCODE||' '|| SQLERRM);
END P_LOC_SALES_CANCEL_INSERT;
--涓棿琛ㄤ繚鐣�澶╋紝姝ｅ紡琛ㄤ繚鐣欎笁涓湀鏁版嵁
PROCEDURE P_LOCxxx_MTxxON IS
BEGIN
DELETE FROM xxxx ls WHERE to_date(ls.create_time,'yyyymmdd')<trunc(SYSDATE,'dd')-7;
DELETE FROM xxxx ls WHERE to_date(ls.create_time,'yyyymmdd')<trunc(SYSDATE,'dd')-7;
DELETE FROM xxxx ls WHERE to_date(ls.create_time,'yyyymmdd')<trunc(SYSDATE,'dd')-7;
DELETE FROM xxxx ls WHERE to_date(ls.create_time,'yyyymmdd')<add_months(trunc(SYSDATE,'mm'),-3);
DELETE FROM xxxx ls WHERE to_date(ls.create_time,'yyyymmdd')<add_months(trunc(SYSDATE,'mm'),-3);
DELETE FROM xxxx ls WHERE to_date(ls.create_time,'yyyymmdd')<add_months(trunc(SYSDATE,'mm'),-3);
COMMIT;
EXCEPTION WHEN no_data_found THEN
dbms_output.put_line('-->Error:No data found');
dbms_output.put_line('-->SQLCODE:'||SQLCODE||' '|| SQLERRM);
RETURN;
WHEN others THEN
dbms_output.put_line('-->SQLCODE:'||SQLCODE||' '|| SQLERRM);
END P_LOC_SAVE_DATA;
END P_LOCxxx_MTxxON
       comment: NULL
     route_sql: NULL
2 rows in set (0.007 sec) from xxxx lo WHERE lo.create_time='||insysdate||' GROUP BY lo.CREATE_TIME,lo.ORGANIZATION_CODE_OUT,lo.ORGANIZATION_CODE_IN,lo.ALLOT_TIME,lo.MATERIAL_CODE,lo.ALLOT_NUMBER,lo.ERP_BATCH_ID,lo.ALLOT_STAFF,lo.OUID';
EXECUTE IMMEDIATE v_sql;
COMMIT;
EXCEPTION WHEN no_data_found THEN
dbms_output.put_line('-->Error:No data found');
dbms_output.put_line('-->SQLCODE:'||SQLCODE|| '' || SQLERRM);
RETURN;
WHEN others THEN
dbms_output.put_line('-->SQLCODE:'||SQLCODE|| '' || SQLERRM);
END P_LOC_ALLOT_INLET_INSERT;
--閿€鍞€€搴撲腑闂磋〃鏁版嵁鎻掑叆閿€鍞€€搴撹〃
PROCEDURE xxxx (insysdate IN VARCHAR2) IS
v_sql VARCHAR(4096);
BEGIN
--鏍规嵁鏃堕棿鍜屽湴甯傛竻闄よ〃鏁版嵁
DELETE FROM xxxx t
WHERE t.CREATE_TIME = insysdate;
v_sql := 'INSERT INTO LOC_SALES_CANCEL_STOCKS(CREATE_TIME,SUBLIBRARY_CODE,MATERIAL_CODE,OUTLET_NUMBER,OUTLET_CODE,ERP_BATCH_ID,ACTIVITY_CODE,ACCOUNT_NAME,OUTLET_STAFF,OUID)
SELECT lo.create_time,lo.sublibrary_code,lo.material_code,count(1),lo.outlet_code,lo.erp_batch_id,lo.activity_code,lo.account_name,lo.outlet_staff,lo.ouid
from xxxx lo WHERE lo.create_time='||insysdate||' AND lo.activity_code IS NOT NULL AND lo.account_name IS NOT NULL GROUP BY lo.create_time,lo.sublibrary_code,lo.material_code,lo.outlet_code,lo.erp_batch_id,lo.activity_code,lo.account_name,lo.outlet_staff,lo.ouid';
EXECUTE IMMEDIATE v_sql;
COMMIT;
EXCEPTION WHEN no_data_found THEN
dbms_output.put_line('-->Error:No data found');
dbms_output.put_line('-->SQLCODE:'||SQLCODE|| '' || SQLERRM);
RETURN;
WHEN others THEN
dbms_output.put_line('-->SQLCODE:'||SQLCODE|| '' || SQLERRM);
END P_LOC_SALES_CANCEL_INSERT;
--涓棿琛ㄤ繚鐣�澶╋紝姝ｅ紡琛ㄤ繚鐣欎笁涓湀鏁版嵁
PROCEDURE P_LOC_SAVE_DATA IS
BEGIN
DELETE FROM xxxx ls WHERE to_date(ls.create_time,'yyyymmdd')<trunc(SYSDATE,'dd')-7;
DELETE FROM xxxx ls WHERE to_date(ls.create_time,'yyyymmdd')<trunc(SYSDATE,'dd')-7;
DELETE FROM xxxx ls WHERE to_date(ls.create_time,'yyyymmdd')<trunc(SYSDATE,'dd')-7;
DELETE FROM xxxx ls WHERE to_date(ls.create_time,'yyyymmdd')<add_months(trunc(SYSDATE,'mm'),-3);
DELETE FROM xxxx ls WHERE to_date(ls.create_time,'yyyymmdd')<add_months(trunc(SYSDATE,'mm'),-3);
DELETE FROM xxxx ls WHERE to_date(ls.create_time,'yyyymmdd')<add_months(trunc(SYSDATE,'mm'),-3);
COMMIT;
EXCEPTION WHEN no_data_found THEN
dbms_output.put_line('-->Error:No data found');
dbms_output.put_line('-->SQLCODE:'||SQLCODE|| '' || SQLERRM);
RETURN;
WHEN others THEN
dbms_output.put_line('-->SQLCODE:'||SQLCODE|| '' || SQLERRM);
END P_LOC_SAVE_DATA;
END P_LOCxxx_MtxxON
    comment: NULL
     route_sql: NULL
2 rows in set (0.007 sec)
三

问题解决

将上述的存储过程粘贴到文本中，并将中文乱码移除，在目标库环境将存储过程中间既可。

END


本文作者：李行行(上海新炬中北团队）

本文来源：“IT那活儿”公众号

trunc存储过程dateoceanbase
文章转载自IT那活儿，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

评论