How to handle dynamic SQL generation safely in OceanBase stored procedures

Environment

• OceanBase community edition 4.2.1 (MySQL Mode)
• Tables partitioned by range (archive_2023Q1, archive_2023Q2, etc.)
• The user has full DML privileges on all archive tables

I'm implementing a data archiving procedure in OceanBase that needs to dynamically generate table names. While this works in standard MySQL, I'm encountering unexpected behavior in OceanBase:

CREATE PROCEDURE archive_data(IN table_suffix VARCHAR(10))
BEGIN
    DECLARE dyn_sql VARCHAR(1000);
    
    -- Dynamic table name generation
    SET @dyn_sql = CONCAT('DELETE FROM archive_', table_suffix, 
                         ' WHERE create_time < DATE_SUB(NOW(), INTERVAL 1 YEAR)');
    
    PREPARE stmt FROM @dyn_sql;
    EXECUTE stmt;  -- Fails with "Table not found" even when table exists
    DEALLOCATE PREPARE stmt;
END;

Observed results

1. Table Existence Verification Fails

   • Manually running the generated SQL works
   • Procedure reports "Table archive_2023Q1 does not exist" when called

2. Privilege Issues

   • User has direct table access
   • Dynamic SQL seems to lose privileges

I've tried to use explicit table verification, but fails.

SELECT COUNT(*) INTO @tbl_exists 
FROM information_schema.tables 
WHERE table_name = CONCAT('archive_', table_suffix);

Question

How to handle dynamic SQL generation safely in OceanBase stored procedures?

CREATE PROCEDURE archive_data(IN table_suffix VARCHAR(10))
BEGIN
    DECLARE dyn_sql TEXT;
    DECLARE tbl_exists INT;

    -- Check if the dynamic table exists
    SELECT COUNT(*) INTO tbl_exists
    FROM information_schema.tables
    WHERE table_schema = DATABASE()
      AND table_name = CONCAT('archive_', table_suffix);

    IF tbl_exists = 1 THEN
        -- Build the dynamic SQL safely
        SET dyn_sql = CONCAT('DELETE FROM ', DATABASE(), '.archive_', table_suffix,
                             ' WHERE create_time < DATE_SUB(NOW(), INTERVAL 1 YEAR)');

        -- Prepare and execute
        PREPARE stmt FROM dyn_sql;
        EXECUTE stmt;
        DEALLOCATE PREPARE stmt;
    ELSE
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Archive table not found';
    END IF;
END;