CODEWITHSUNDEEP
oracle11g
PS078
PS078

Reputation: 461

00900. 00000 - "invalid SQL statement" for EXECUTE IMMEDIATE

I am trying to use Dynamic query in my code below but getting error (00900. 00000 - "invalid SQL statement"). Kindly suggest where i am mistaking in the code.

create or replace PROCEDURE CreateInsertTmpTable
AS
crttmp VARCHAR2(200);
intrtmp VARCHAR2(200);
printTableValues VARCHAR2(1000);

BEGIN
  crttmp := 'CREATE GLOBAL TEMPORARY TABLE my_temp_table ON COMMIT PRESERVE ROWS AS SELECT * FROM VWBLKDATA WHERE 1=0';
  EXECUTE IMMEDIATE crttmp;
  intrtmp := 'INSERT INTO my_temp_table SELECT * FROM VWBLKDATA';
  EXECUTE IMMEDIATE intrtmp;
  printTableValues := ' for data in(SELECT * from my_temp_table)
                        loop
                             dbms_output.put_line(data.ID); 
                        end loop';
  EXECUTE IMMEDIATE printTableValues;
  COMMIT;
END CreateInsertTmpTable;

Upvotes: 0

Views: 2303

Answers (1)

Patrick Marchand
Patrick Marchand

Reputation: 3445

I think you're overdoing the EXECUTE IMMEDIATE; you can run INSERT statements and PL/SQL without them like:

begin 
for i in 1..10 loop
   insert into test (some_column) values (to_char(i));
end loop;
end;

But anyways, it looks like you're last EXECUTE IMMEDIATE is trying to execute a partial PL/SQL anonymous block; it's missing a "begin" and "end;"

I would suggest just executing the for loop like so:

for data in (SELECT * from my_temp_table) 
loop
      dbms_output.put_line(data.ID); 
end loop;

or else you'll need to add a begin/end around it in the text (and the "end loop" needs a trailing ";"):

printTableValues := 'begin 
                        for data in (SELECT * from my_temp_table)
                        loop
                             dbms_output.put_line(data.ID); 
                        end loop;
                     end;';

Upvotes: 2

Related Questions