Dynamic SQL Immediate Execute possible with HEX-code using UTL_RAW.CAST_TO_VARCHAR2?

Question

I want to read different sql statements from a CLOB field to HEX-code I then want to cast the HEX-code back within an a sql-script to varchar2 and to execute it. Generating HEX-code and casting works, but it does not execute. Can anyone please help me if and how Immediate Execute is possible?

Following example

1. My sql statement I want in a CLOB field

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. With a procedure I was given from a retired colleague I generated HEXCODE corretly using RAWTOHEX and UTL_RAW.CAST_TO_RAW. The HEXCODE is correct because the "decrypted" code using CAST_TO_VARCHAR2 looks correct. The following code shows the create statement from step 1:

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. What I am missing is that the code should not just be displayed, it should be excetuted so I got the messages Table dropped. and Table created. in this case.

My result:

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

You have created the script as the hex-encoding of:

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

You cannot run that with an EXECUTE IMMEDIATE statement because it is not a single SQL statement; it is two statements and Oracle forbids running multiple statements as a single command (to help prevent SQL injection attacks).

If you want to have a single statement then you will need to wrap it in an anonymous PL/SQL block and then, since you cannot execute DDL statements in PL/SQL, you would need to wrap the DDL statements their own EXECUTE IMMEDIATE statements so the script would become:

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;

Then it is a single statement and can be run using EXECUTE IMMEDIATE:

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

Which works (provided that you have a table to drop).

fiddle

---

Alternatively, you would need to split the script on the SQL statement terminator (assuming that you just have SQL statements and no PL/SQL statements) and use EXECUTE IMMEDIATE on each individual statement.

A simple example (with minimal error handling) is:

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

fiddle