Native Dynamic SQL DDL statements not executing with bind variables in WHERE clause

Question

I was practising for Dynamic SQL and Procedures in order to implement some scripts for weekly, daily, and monthly dashboards. These dashboards require that we create tables, hence I decided to stick with Dynamic SQL. While practising for these, I am getting some errors, and to simulate the errors, I have created the following tables and copied the procedures.

1. Generating the EMP table

CREATE TABLE EMP
(EMPNO NUMERIC(4) NOT NULL,
ENAME VARCHAR(10),
JOB VARCHAR(9),
MGR NUMERIC(4),
HIREDATE DATE,
SAL NUMERIC(7, 2),
COMM NUMERIC(7, 2),
DEPTNO NUMERIC(2));

INSERT INTO EMP VALUES
(7369, 'SMITH', 'CLERK', 7902, '17-DEC-1980', 800, NULL, 20);
INSERT INTO EMP VALUES
(7499, 'ALLEN', 'SALESMAN', 7698, '20-FEB-1981', 1600, 300, 30);
INSERT INTO EMP VALUES
(7521, 'WARD', 'SALESMAN', 7698, '22-FEB-1981', 1250, 500, 30);
INSERT INTO EMP VALUES
(7566, 'JONES', 'MANAGER', 7839, '2-APR-1981', 2975, NULL, 20);
INSERT INTO EMP VALUES
(7654, 'MARTIN', 'SALESMAN', 7698, '28-SEP-1981', 1250, 1400, 30);
INSERT INTO EMP VALUES
(7698, 'BLAKE', 'MANAGER', 7839, '1-MAY-1981', 2850, NULL, 30);
INSERT INTO EMP VALUES
(7782, 'CLARK', 'MANAGER', 7839, '9-JUN-1981', 2450, NULL, 10);
INSERT INTO EMP VALUES
(7788, 'SCOTT', 'ANALYST', 7566, '09-DEC-1982', 3000, NULL, 20);
INSERT INTO EMP VALUES
(7839, 'KING', 'PRESIDENT', NULL, '17-NOV-1981', 5000, NULL, 10);
INSERT INTO EMP VALUES
(7844, 'TURNER', 'SALESMAN', 7698, '8-SEP-1981', 1500, 0, 30);
INSERT INTO EMP VALUES
(7876, 'ADAMS', 'CLERK', 7788, '12-JAN-1983', 1100, NULL, 20);
INSERT INTO EMP VALUES
(7900, 'JAMES', 'CLERK', 7698, '3-DEC-1981', 950, NULL, 30);
INSERT INTO EMP VALUES
(7902, 'FORD', 'ANALYST', 7566, '3-DEC-1981', 3000, NULL, 20);
INSERT INTO EMP VALUES
(7934, 'MILLER', 'CLERK', 7782, '23-JAN-1982', 1300, NULL, 10);

2. Generating the EmpSalary table

CREATE TABLE empSalary
(ename varchar(20),
salary number
);


INSERT INTO empSalary VALUES ('SMITH', 2000);
INSERT INTO empSalary VALUES ('WARD', 300);
COMMIT;

The following procedure works fine:

---- Formulating a Procedure - Try 1

CREATE OR REPLACE PROCEDURE getData(dataType IN VARCHAR2)
IS
  newTbl VARCHAR2(20);
  sqlQuery  VARCHAR2(100);
  empName VARCHAR2(20) := 'SMITH';
BEGIN
  IF dataType = 'Weekly' THEN
      newTbl := 'empTblWeekly'||TO_CHAR(SYSDATE, 'ddmon');
      sqlQuery := 'CREATE TABLE '||newTbl||' as SELECT * FROM emp';
      EXECUTE IMMEDIATE sqlQuery;
  ELSIF dataType = 'Daily' THEN
      newTbl := 'empTblDaily'||TO_CHAR(SYSDATE, 'ddmon');
      sqlQuery := 'CREATE TABLE '||newTbl||' as SELECT * FROM empSalary';
      EXECUTE IMMEDIATE sqlQuery;
  ELSIF dataType = 'Monthly' THEN
      newTbl := 'empTblMonthly'||TO_CHAR(SYSDATE, 'ddmon');
      sqlQuery := 'CREATE TABLE '||newTbl||' as SELECT * FROM emp WHERE ENAME = ''SMITH''';
      EXECUTE IMMEDIATE sqlQuery;
  END IF;
EXCEPTION
  WHEN NO_DATA_FOUND THEN
  DBMS_OUTPUT.PUT_LINE('No data found!');
  COMMIT;
END;

BEGIN
getData('Monthly');
END;

SELECT *
FROM empTblMonthly21Feb;

The following 2 formulations return errors:

---- Formulating a Procedure  - Try 2

CREATE OR REPLACE PROCEDURE getData(dataType IN VARCHAR2)
IS
  newTbl VARCHAR2(20);
  sqlQuery  VARCHAR2(100);
  empName VARCHAR2(20) := 'SMITH';
BEGIN
  IF dataType = 'Weekly' THEN
      newTbl := 'empTblWeekly'||TO_CHAR(SYSDATE, 'ddmon');
      sqlQuery := 'CREATE TABLE '||newTbl||' as SELECT * FROM emp';
      EXECUTE IMMEDIATE sqlQuery;
  ELSIF dataType = 'Daily' THEN
      newTbl := 'empTblDaily'||TO_CHAR(SYSDATE, 'ddmon');
      sqlQuery := 'CREATE TABLE '||newTbl||' as SELECT * FROM empSalary';
      EXECUTE IMMEDIATE sqlQuery;
  ELSIF dataType = 'Monthly' THEN
      newTbl := 'empTblMonthly'||TO_CHAR(SYSDATE, 'ddmon');
      sqlQuery := 'CREATE TABLE '||newTbl||' as SELECT * FROM emp WHERE ENAME = :empName';
      EXECUTE IMMEDIATE sqlQuery USING empName;
  END IF;
EXCEPTION
  WHEN NO_DATA_FOUND THEN
  DBMS_OUTPUT.PUT_LINE('No data found!');
  COMMIT;
END;

    BEGIN
    getData('Monthly');
    END;

    SELECT *
    FROM empTblMonthly21Feb;

---ORA-01027: bind variables not allowed for data definition operations ORA-06512: at "SYS.GETDATA", line 18 ORA-06512: at line 2


---- Formulating a Procedure  - Try 3

CREATE OR REPLACE PROCEDURE getData(dataType IN VARCHAR2)
IS
  newTbl VARCHAR2(20);
  sqlQuery  VARCHAR2(100);
  empName VARCHAR2(20) := 'SMITH';
BEGIN
  IF dataType = 'Weekly' THEN
      newTbl := 'empTblWeekly'||TO_CHAR(SYSDATE, 'ddmon');
      sqlQuery := 'CREATE TABLE '||newTbl||' as SELECT * FROM emp';
      EXECUTE IMMEDIATE sqlQuery;
  ELSIF dataType = 'Daily' THEN
      newTbl := 'empTblDaily'||TO_CHAR(SYSDATE, 'ddmon');
      sqlQuery := 'CREATE TABLE '||newTbl||' as SELECT * FROM empSalary';
      EXECUTE IMMEDIATE sqlQuery;
  ELSIF dataType = 'Monthly' THEN
      newTbl := 'empTblMonthly'||TO_CHAR(SYSDATE, 'ddmon');
      sqlQuery := 'CREATE TABLE '||newTbl||' as SELECT * FROM emp WHERE ENAME ='||empName;
      EXECUTE IMMEDIATE sqlQuery;
  END IF;
EXCEPTION
  WHEN NO_DATA_FOUND THEN
  DBMS_OUTPUT.PUT_LINE('No data found!');
  COMMIT;
END;

    BEGIN
    getData('Monthly');
    END;

    SELECT *
    FROM empTblMonthly21Feb;

---ORA-00904: "SMITH": invalid identifier ORA-06512: at "SYS.GETDATA", line 18 ORA-06512: at line 2

But I am unable to understand why the errors, because in the 2nd try, I am not using a bind variable for a Table name, and in the 3rd try I am simply concatenating a variable...

Thanks for the assistance...

I think the error message is quite clear: `bind variables not allowed for data definition operations (i.e. DDL Statements)`. It's mainly because Oracle defined this by design. Anyway, it is a very bad idea to create tables dynamically on the fly. You have many other possibilities to achive your requirements, in your case for example with partitions. — Wernfried Domscheit, Feb 21 '15 at 12:09
@Wernfried thank you for the response. Could you please explain more about partitions? Pardon the example with the toy data, but my original task comprises of JOINs and other operations before a final table is formed, which I am attempting to create dynamically, so in future we may query it again, if need be. The aim is also to automate the entire process. Also the bind variables with DDL statements: I thought we could at least concatenate the variable (like I did for table name)? Could you enlighten me why that is not working either? — info_seekeR, Feb 21 '15 at 12:17
In this case it sounds more like a [Materialized View](http://docs.oracle.com/cd/B28359_01/server.111/b28326/repmview.htm#i34980) or even a simple `VIEW`, Since views don't consume any space (at least allmost) it does not matter if you create hundreds of them according to your need. — Wernfried Domscheit, Feb 21 '15 at 12:38
Third try sould work I think, but put single quotes around the string. Try: ... where ENAME = ''' || empName || '''; — tbone, Feb 21 '15 at 12:40
`EXECUTE IMMEDIATE sqlQuery;` will never raise any `NO_DATA_FOUND` exception, because you don't make a `SELECT`, you make a `CREATE TABLE` statement. Why do you make a `COMMIT`? — Wernfried Domscheit, Feb 21 '15 at 12:41
@Wernfried I am making a commit so that the created table be available to us in future (we may require analysing such tables in future). — info_seekeR, Feb 21 '15 at 12:49
Each DDL makes an implicit COMMIT anyway, so it is useless. And it is "even more useless" when you do it only in case of an exception but not after normal execution. — Wernfried Domscheit, Feb 21 '15 at 13:00
@Wernfried ah, I see. I meant to have done it after the END IF; but now as you remind, we don't need to do that either, since DDLs implicitly commit. I was confusing myself with the need to commit after INSERTs, MERGEs, etc. — info_seekeR, Feb 21 '15 at 13:06
See [Why cannot I use bind variables in DDL/SCL statements in dynamic SQL?](http://stackoverflow.com/q/25489002/1461424) — sampathsris, Jul 15 '15 at 03:39

score 1 · Accepted Answer · answered Feb 21 '15 at 12:49

1

Your third attempt would have worked, but you forgot the single quotes in the sql statement (around ename variable). Try something like:

declare
  l_tabname varchar2(100) := 'MY_TAB1';
  l_name varchar2(100) := 'SMITH';
  l_sql varchar2(1000);
begin
  l_sql := 'create table ' || l_tabname || ' as select * from emp where ename = ''' || l_name || '''';
  execute immediate l_sql;
end;

No bind vars used, but its still creating the table using variables.

answered Feb 21 '15 at 12:49

tbone

15,107
3
33
40

Thank you, this works. But a question: Why do we have to add the quotes like ''' || l_name || ''''; I would have added only '' || l_name || '';... I understand that if we want to include a quotation within a quotation we use 2 quotes ''SMITH'', but I was not informed about what you suggested... – info_seekeR Feb 21 '15 at 13:02
its just escaping the single quote. Just remember if you want a single quote inside a single quoted string, you need two consecutive quotes. – tbone Feb 22 '15 at 03:59
1

The other quote is to end the quoted string. Most IDEs (like Toad or Sql Developer) will color code just like SO above, so you can see if you need another single quote to end the string. – tbone Feb 22 '15 at 04:07

Native Dynamic SQL DDL statements not executing with bind variables in WHERE clause

1. Generating the EMP table

2. Generating the EmpSalary table

The following 2 formulations return errors:

1 Answers1