Executing dynamic Sql to create table-CodePudding

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

CodePudding user response：

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)

CodePudding user response：

If you want to use dynamic sql in a pl/sql block try this:

declare

  r0 number;
  r1 number;

begin

  r0:= 100;
  r1 :=1100;


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

end;