使用UTL_RAW.CAST_TO_VARCHAR2的HEX代码可以执行动态SQL立即执行吗

Question

我想从CLOB字段读取不同的sql语句到HEX代码然后，我想将sql脚本中的HEX代码强制转换回varchar2并执行它。生成HEX代码和强制转换可以工作，但不执行。有人能帮助我是否以及如何立即执行吗？

以下示例

1. 我想要的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)
);

2. 根据一位退休同事给我的程序，我使用RAWTOHEX和UTL_RAW.CAST_TO_RAW正确生成了HEXCODE；解密的"；使用CAST_TO_VARCHAR2的代码看起来是正确的。以下代码显示了步骤1中的create语句：

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;
/

3. 我缺少的是，代码不应该只显示，它应该被练习，所以我删除了表中的消息和表已创建在这种情况下

我的结果：

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.

Answer 1

您已经将脚本创建为的十六进制编码

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)
);

您不能用EXECUTE IMMEDIATE语句运行它，因为它不是单个SQL语句；它是两个语句，Oracle禁止将多个语句作为一个命令运行(以帮助防止SQL注入攻击(。

如果你想有一个单独的语句，那么你需要把它包装在一个匿名的PL/SQL块中，然后，由于你不能在PL/SQL中执行DDL语句，你需要把DDL语句包装成它们自己的EXECUTE IMMEDIATE语句，这样脚本就会变成：

BEGIN
EXECUTE IMMEDIATE 'drop table customers purge';
EXECUTE IMMEDIATE '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))';
END;

然后它是一个单独的语句，可以使用EXECUTE IMMEDIATE:运行

DECLARE
buffer clob;
BEGIN
buffer := buffer||UTL_RAW.CAST_TO_VARCHAR2('424547494E0A20204558454355544520494D4D454449415445202764726F70207461626C6520637573746F6D657273207075726765273B0A20204558454355544520494D4D4544494154452027435245415445205441424C4520637573746F6D65727328637573746F6D65725F6964206E756D62657228313029204E4F54204E554C4C2C202020637573746F6D65725F6E616D6520766172636861723228353029204E4F54204E554C4C2C2063697479207661726368617232283530292C20434F4E53545241494E5420637573746F6D6572735F706B205052494D415259204B45592028637573746F6D65725F69642929273B0A454E443B');
DBMS_OUTPUT.PUT_LINE(buffer);
EXECUTE IMMEDIATE buffer;
END;
/

这是有效的(前提是您有一个表要删除(。

小提琴

---

或者，您需要在SQL语句终止符上拆分脚本(假设您只有SQL语句，没有PL/SQL语句(，并在每个单独的语句上使用EXECUTE IMMEDIATE。

一个简单的例子(具有最小的错误处理(是：

DECLARE
v_data RAW(32767);
v_sql  CLOB;
v_spos  PLS_INTEGER := 1;
v_epos  PLS_INTEGER;
v_len   PLS_INTEGER;
v_stmt  CLOB;
BEGIN
v_data := UTL_RAW.CAST_TO_RAW(
'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)
); '
);
v_sql := UTL_RAW.CAST_TO_VARCHAR2(v_data);
LOOP
EXIT WHEN v_spos > LENGTH(v_sql);
v_epos := INSTR(v_sql, ';', v_spos, 1);

IF v_epos = v_spos THEN
v_spos := v_epos + 1;
CONTINUE;
END IF;
IF v_epos < 1 THEN
v_stmt := SUBSTR(v_sql, v_spos);
ELSE
v_stmt := SUBSTR(v_sql, v_spos, v_epos - v_spos);
END IF;
IF NOT REGEXP_LIKE(v_stmt, '^s*$') THEN
DBMS_OUTPUT.PUT_LINE(v_stmt);
BEGIN
EXECUTE IMMEDIATE v_stmt;
DBMS_OUTPUT.PUT_LINE('success');
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE(SQLERRM);
END;
END IF;
EXIT WHEN v_epos < 1;
v_spos := v_epos + 1;
END LOOP;
END;
/

小提琴