Diagnosing and Fixing Syntax Errors in Stored Procedures After Oracle‑to‑OceanBase Migration

Background : After migrating a stored procedure from Oracle to OceanBase (OB) mode, execution fails with an ORA‑00900 syntax error. The error log shows the problematic SQL fragment near the ON () clause.

Problem Diagnosis : The typical approach is to enable trace logging ( set ob_enable_trace_log=on; ), run the failing SQL, and retrieve the trace_id via show trace; . Using the trace ID, the observer IP is found from gv$sql_audit , and the corresponding log file is grepped for the full SQL statement.

SQL Analysis : The extracted SQL reveals that the ON condition of the MERGE statement is empty, causing the syntax error. The generated SQL is built from several PL/SQL variables ( v_sql_str , v_sql_str1 , etc.) that concatenate column lists, primary‑key conditions, and the USING sub‑query.

merge into t_cc_demo a using (select ... from t_cc_demo@dblink_demo where ... ) b on ()
when matched then update set a.REMINDER_COUNT=b.REMINDER_COUNT ...
when not matched then insert ...

Root Cause : The cursor that retrieves primary‑key columns ( c_pkey ) filters constraints by constraint_type = 'P' . After migration, the original primary‑key constraint is converted to a unique key ( 'U' ) because the partition key does not match the primary key, so the cursor returns no rows and the ON clause remains empty.

Fix : Modify the cursor to include both primary‑key and unique‑key constraints and restrict names to those starting with 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 in ('U','P')
    and con.constraint_name like 'PK%'
    and col.table_name = upper(p_par_table);

After rebuilding the SQL with the corrected primary‑key list, the ON condition is populated and the procedure runs successfully.

Knowledge Extension : In Oracle, partitioned tables are heap tables; in OceanBase they are index‑organized tables requiring the partition key to be part of the primary key. OMS converts primary‑key constraints to unique constraints when this rule is violated. Examples of various table definitions illustrate when conversion occurs.

Conclusion : When the Oracle primary key does not contain the partition key, OMS changes it to a unique key, leading to empty primary‑key retrieval in PL/SQL and syntax errors in generated MERGE statements. Using trace IDs to extract the real SQL and adjusting the constraint‑retrieval logic resolves the issue.