-1
create or replace procedure sp_test as
begin
CREATE TABLE T AS SELECT col1,col2 FROM t1;
FOR N IN (SELECT * FROM T) LOOP
     UPDATE t1 SET t1.col1='value' where col2='value2';
END LOOP;
drop table T;
end;
/

I need to select data into t table from a t1 table in order to apply some modifications, and merge those modifications in t1 table (origin table) before deleting table t.

I m getting this error : PLS-00103

jarlh
  • 42,561
  • 8
  • 45
  • 63
goodlink
  • 1
  • 1
  • 1
    you might want to have a look at [Global Temporary Table](https://oracle-base.com/articles/misc/temporary-tables) – Sudipta Mondal Jul 05 '17 at 14:54
  • 2
    You can't use DDL directly into PL/SQL use `EXECUTE IMMEDIATE 'CREATE TABLE T AS SELECT col1,col2 FROM t1'` – Jair Hernandez Jul 05 '17 at 14:58
  • Also, just to point it out, I hope that the single quoted `'value'` and `'value2'` are just a test example of what you actually intend to do, and they are to be replaced by actual values taken from each `N` in your `T` table. – Jair Hernandez Jul 05 '17 at 15:05
  • You probably don't need to use another table **at all** - but hard to say based on your example. – Tony Andrews Jul 05 '17 at 15:56

2 Answers2

0


You need to use execute immediate for any ddl inside any pl/sql block. Try the below code:

create or replace procedure sp_test 
is 
begin
Execute Immediate 'CREATE TABLE T AS SELECT col1,col2 FROM t1';

FOR N IN (SELECT * FROM T) LOOP
     Execute immediate 'UPDATE t1 SET t1.col1=''value'' where col2=''value2''';
END LOOP;
execute immediate 'drop table T';
end;
/
smshafiqulislam
  • 564
  • 6
  • 12
0

Thanks everybody for your contribution, I tried something that works : A cursor for update. Thanks for help :)

goodlink
  • 1
  • 1