能否通过UTL_RAW.CAST_TO_VARCHAR2执行十六进制动态SQL？

我需要从CLOB字段读取SQL语句，转成十六进制编码后，在SQL脚本里转回VARCHAR2并执行。目前编码和解码都正常，但转换后的SQL没法执行，想知道能不能实现即时执行，以及具体方法。

示例步骤 #

• 存储在CLOB中的目标SQL：

drop table customers purge;

CREATE TABLE customers
( customer_id number(10) NOT NULL,
  customer_name varchar2(50) NOT NULL,
  city varchar2(50),
  CONSTRAINT customers_pk PRIMARY KEY (customer_id)
);

• 已通过RAWTOHEX和UTL_RAW.CAST_TO_RAW生成正确十六进制编码，用UTL_RAW.CAST_TO_VARCHAR2能正确还原SQL，当前还原代码如下：

SET LINESIZE 10000
SET serveroutput on size 300000 FORMAT WRAPPED

DECLARE
buffer clob;

BEGIN

    buffer := buffer||UTL_RAW.CAST_TO_VARCHAR2('64726F70207461626C6520637573746F6D6572732070757267653B0A0A435245415445205441424C4520637573746F6D6572730A2820637573746F6D65725F6964206E756D62657228313029204E4F54204E554C4C2C0A2020637573746F6D65725F6E61');
    buffer := buffer||UTL_RAW.CAST_TO_VARCHAR2('6D6520766172636861723228353029204E4F54204E554C4C2C0A202063697479207661726368617232283530292C0A2020434F4E53545241494E5420637573746F6D6572735F706B205052494D415259204B45592028637573746F6D65725F6964290A29');
    buffer := buffer||UTL_RAW.CAST_TO_VARCHAR2('3B');

    dbms_output.put_line(buffer);

END;
/

• 当前仅能显示转换后的SQL，无法执行，期望执行后得到Table dropped.和Table created.的提示。

当前执行结果 #

SQL> SET LINESIZE 10000
SQL> SET serveroutput on size 300000 FORMAT WRAPPED
SQL>
SQL> DECLARE
  2  buffer clob;
  3
  4  BEGIN
  5
  6      buffer := buffer||UTL_RAW.CAST_TO_VARCHAR2('64726F70207461626C6520637573746F6D6572732070757267653B0A0A435245415445205441424C4520637573746F6D6572730A2820637573746F6D65725F6964206E756D62657228313029204E4F54204E554C4C2C0A2020637573746F6D65725F6E61');
  7      buffer := buffer||UTL_RAW.CAST_TO_VARCHAR2('6D6520766172636861723228353029204E4F54204E554C4C2C0A202063697479207661726368617232283530292C0A2020434F4E53545241494E5420637573746F6D6572735F706B205052494D415259204B45592028637573746F6D65725F6964290A29');
  8      buffer := buffer||UTL_RAW.CAST_TO_VARCHAR2('3B');
  9
 10      dbms_output.put_line(buffer);
 11
 12  END;
 13  /
drop table customers purge;

CREATE TABLE customers
( customer_id number(10) NOT NULL,
  customer_name varchar2(50) NOT NULL,
  city varchar2(50),
  CONSTRAINT customers_pk PRIMARY KEY (customer_id)
);

PL/SQL procedure successfully completed.

可以通过动态SQL实现转换后SQL的即时执行，核心是使用EXECUTE IMMEDIATE语句执行还原后的CLOB内容。需要注意拆分多语句、过滤无效内容，具体实现如下：

修改后的执行代码 #

SET LINESIZE 10000
SET serveroutput on size 300000 FORMAT WRAPPED
SET FEEDBACK ON

DECLARE
    buffer clob;
    v_sql varchar2(32767);
    v_start number := 1;
    v_end number;
BEGIN
    -- 还原十六进制编码为SQL语句
    buffer := buffer||UTL_RAW.CAST_TO_VARCHAR2('64726F70207461626C6520637573746F6D6572732070757267653B0A0A435245415445205441424C4520637573746F6D6572730A2820637573746F6D65725F6964206E756D62657228313029204E4F54204E554C4C2C0A2020637573746F6D65725F6E61');
    buffer := buffer||UTL_RAW.CAST_TO_VARCHAR2('6D6520766172636861723228353029204E4F54204E554C4C2C0A202063697479207661726368617232283530292C0A2020434F4E53545241494E5420637573746F6D6572735F706B205052494D415259204B45592028637573746F6D65725F6964290A29');
    buffer := buffer||UTL_RAW.CAST_TO_VARCHAR2('3B');

    -- 拆分并执行多语句SQL
    LOOP
        -- 定位下一个分号位置
        v_end := INSTR(buffer, ';', v_start);
        EXIT WHEN v_end = 0;

        -- 提取单条SQL并去除前后空白
        v_sql := TRIM(SUBSTR(buffer, v_start, v_end - v_start));
        
        -- 执行非空有效语句
        IF v_sql IS NOT NULL THEN
            EXECUTE IMMEDIATE v_sql;
            DBMS_OUTPUT.PUT_LINE('执行完成: ' || SUBSTR(v_sql, 1, 50) || CASE WHEN LENGTH(v_sql) > 50 THEN '...' ELSE '' END);
        END IF;

        -- 移动到下一条语句起始位置
        v_start := v_end + 1;
    END LOOP;

END;
/

效果说明 #

• 执行后会依次执行DROP TABLE和CREATE TABLE语句，返回结果类似：

执行完成: drop table customers purge
执行完成: CREATE TABLE customers
( customer_id number(10) NOT NULL,...

PL/SQL procedure successfully completed.

• 若要看到Table dropped.和Table created.这类原生提示，确保SET FEEDBACK ON开启（默认开启），不过动态SQL的这类系统提示不会直接输出，可通过DBMS_OUTPUT自定义输出内容。

注意事项 #

• 权限：执行用户需具备对应SQL的操作权限（如DROP TABLE、CREATE TABLE）。
• 长语句处理：如果单条SQL超过VARCHAR2(32767)限制，需改用DBMS_SQL包处理CLOB类型的动态SQL。
• 安全风险：动态SQL存在SQL注入风险，确保CLOB中的SQL来自可信来源。

内容的提问来源于stack exchange，提问作者Cologne2202