0

I want to create tables in my procedure. I wrote my procedure like this

create or replaceprocedure my_proc is 
  var1 varchar2(4000);
  var2 varchar2(4000);
  var3 varchar2(4000);
     
BEGIN
  var1 := 'create table student
  as
  select /*+ full(st) parallel (st 16) */  *
  from my_student st
  where status = 'CO' 
  and age <> 0
  EXECUTE IMMEDIATE var1;

  var2 :=  ' create table passenger
  as
  select /*+ full(ps) parallel (ps 16) */  *
  from my_passengers ps
  where  ps.type = 3           
  and ps.age = 19';
  EXECUTE IMMEDIATE var2;

  var3 := 'create table cities
  nologging
  compress
  as
  select  /*+ parallel (ct8) parallel (ps8) parallel (st8)  */ *            
  from cities  ct , passenger ps, student st 
  where 1 = 1 
  and -----
  ------;
  EXECUTE IMMEDIATE var3;
end;

But I get some compiling errors. Do you have any idea how to create table in procedures?

My error:

Error(14,25): PLS-00103: Encountered the symbol "CO" when expecting one of the following:     * & = - + ; < / > at in is mod remainder not rem    <an exponent (**)> <> or != or ~= >= <= <> and or like like2    like4 likec between || multiset member submultiset The symbol "* was inserted before "CO" to continue.
Abra
  • 19,142
  • 7
  • 29
  • 41
tom
  • 215
  • 3
  • 11
  • 3
    Posting the compilation errors is always useful. It appears that your strings are not properly terminated (notice that the syntax highting here on SO is broken because it can't match begin and end quotes). If you want to have single quotes inside a string, you'd need to escape them with a second single quote. Or use the q quoting syntax for defining your strings (this would be my preference). – Justin Cave Feb 26 '21 at 07:47
  • Does this help? https://livesql.oracle.com/apex/livesql/file/content_CIREYU9EA54EOKQ7LAMZKRF6P.html – Abra Feb 26 '21 at 07:55
  • 1
    `status = 'CO'` should be `status = ''CO''` (with two single-quotes instead of one) or you can use `q'[CREATE TABLE students .... WHERE status = 'CO' AND age <> 0]'` – MT0 Feb 26 '21 at 09:18

0 Answers0