Table of Contents

• 1. Key SQL Logic in Source Oracle Database
• 2. Core Challenges in Migrating to OceanBase
• 3. SQL Refactoring Plan for the Target Database
• 4. Refactoring Summary

1. Key SQL Logic in Source Oracle Database

DELIMITER $$
...
EXECUTE IMMEDIATE
'UPDATE table SET col = :1'
USING CASE test.check_result WHEN '0' THEN '√' WHEN '1' THEN '×' WHEN '2' THEN 'O' WHEN '3' THEN '—' END || substr(test.remark,1,50),  -- complex expression inside USING clause
...
END;
$$

2. Core Challenges in Migrating to OceanBase

1. OceanBase applies stricter rules to the USING clause in EXECUTE IMMEDIATE: only simple bind variables are allowed, while inline expressions or function calls are not. This differs from Oracle’s more permissive dynamic SQL behavior, so complex logic must be bridged through intermediate variables.

3. SQL Refactoring Plan for the Target Database

Decouple complex logic with an intermediate variable:

DELIMITER $$
...
var_value varchar2(100);
...
v_value := CASE test.check_result WHEN '0' THEN '√' WHEN '1' THEN '×' WHEN '2' THEN 'O' WHEN '3' THEN '—' END || substr(test.remark,1,50); -- precompute expression
EXECUTE IMMEDIATE
'UPDATE table SET col = :1'
USING v_value;  
...
END;
$$

4. Refactoring Summary

1. Parameter-passing optimization: move the CASE ... END logic from the USING clause into v_value assignment so dynamic SQL handles only simple value binding, which matches OceanBase syntax requirements.
2. Type declaration standardization: explicitly define bind-variable types before execution to reduce implicit-conversion risks.

Technical notes:

• Migration results depend on source-environment complexity and should be validated in real scenarios.
• This article demonstrates technical approaches only and does not constitute a performance commitment.

Image credit: Designed by Freepik