Executing dynamic Sql to create table

Question

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

I tried but it's giving error that bind variables are not allowed in data definition operations — Aman Kumar Singh
– Aman Kumar Singh, Commented Oct 12, 2022 at 6:43
I've tried this not working PROCEDURE temp_prc ( r0 in integer,r1 in integer ) IS TEMP_TBL varchar2(4000); begin TEMP_TBL:='create table temp parallel (degree 4) nologging as select e.* from employee e where e.emp_id between :r0 and : r1'; EXECUTE IMMEDIATE TEMP_TBL using r0,r1; end temp_prc; — Aman Kumar Singh
– Aman Kumar Singh, Commented Oct 12, 2022 at 7:42
Yes, you should get ora-01027 error. @Reza Davoudian 's answer(even along with a Stored Procedure) would be suitable for your case I think. — Barbaros Özhan
– Barbaros Özhan, Commented Oct 12, 2022 at 12:40

Reza Davoudian · Accepted Answer · 2022-10-13 07:46:22Z

3

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

edited Oct 13, 2022 at 7:46

answered Oct 12, 2022 at 11:11

Reza Davoudian

1636 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Reza Davoudian Over a year ago

I edited the answer just now. run the block again please. @Aman Kumar Singh

p3consulting · Accepted Answer · 2022-10-12 06:37:27Z

0

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)

answered Oct 12, 2022 at 6:37

p3consulting

5,4212 gold badges14 silver badges15 bronze badges

3 Comments

Aman Kumar Singh Over a year ago

I tried but it's giving error that bind variables are not allowed in data definition operations

p3consulting Over a year ago

Indeed I forgot this point, only for select, update, delete, ... then you have to concat the values to built the string.

Aman Kumar Singh Over a year ago

You mean like this right ? I've tried this not workingPROCEDURE temp_prc ( r0 in integer,r1 in integer ) IS TEMP_TBL varchar2(4000); begin TEMP_TBL:='create table temp parallel (degree 4) nologging as select e.* from employee e where e.emp_id'|| 'between '|| 'r0 ' ||'and ' ||'r1'; EXECUTE IMMEDIATE TEMP_TBL; end temp_prc;

Collectives™ on Stack Overflow

Executing dynamic Sql to create table

2 Answers 2

1 Comment

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related