CODEWITHSUNDEEP
oracle-databaseplsql
Aman Kumar Singh
Aman Kumar Singh

Reputation: 9

Executing dynamic Sql to create table

I'm trying to execute below query but getting invalid identifier error

EXECUTE IMMEDIATE 'create table temp parallel (degree 4) nologging as 
select e.* from employee e where e.emp_id between r0 and r1';
/

r0 and r1 I'm passing as input variable of datatype number

Upvotes: 0

Views: 154

Answers (2)

Reza Davoudian
Reza Davoudian

Reputation: 155

If you want to use dynamic SQL in a PL/SQL block try this:

DECLARE
  r0 number :=  100;
  r1 number := 1100;
BEGIN
  EXECUTE IMMEDIATE 'CREATE TABLE temp parallel (degree 4) nologging AS 
                     SELECT * 
                       FROM employee 
                      WHERE emp_id BETWEEN '||r0||' AND '||r1||' ';
END;
/

Upvotes: 3

p3consulting
p3consulting

Reputation: 4670

r0 and r1 are not bind variables: change to :r0 and :r1 and add USING clause to EXECUTE IMMEDIATE. (read the online documentation: https://docs.oracle.com/en/database/oracle/oracle-database/12.2/lnpls/EXECUTE-IMMEDIATE-statement.html)

Upvotes: 0

Related Questions