Oracle Procedure

[jrdba39]

Hello Gurus,

I'm totally new to the Oracle world and I need help writing the following prodecure:

Create a database prodecure to delete the data from the table (modmast) in the ORADBA schema and refresh it from the external table plmprd.modmast in Production.

Any help would be great!!!

Thanks!

[davey23uk]

well post what you have come up with so far and we can suggest improvements

p.s. have you tried googling for create procedure?

[jrdba39]

This is what I came up with:

create procedure MOD_UPDATE
as
begin
truncate table oradba.modmast;
insert into oradba.modmast (MBSEG,CO,PLANT,MODEL,WIDTH,LNGTH,HLNGTH,PRLIN#,MDLEDT,BEGCAT,ENDCAT,BASEPR,BASECS,FLRNUM,MODES,MODP RE,MODSUF,WOMOD,AUTUPD,LSTUPD,BOMNAM,UPDDT8,AUDDT8,CRAP,FASTNR,MSPRF1,MSPRF2,MSPRF3) values (select * from plmprod.modmast);
end
/

[davey23uk]

and what happened when you compiled it into the database?

[jrdba39]

create or replace procedure MOD_UPDATE
as
begin
execute immediate 'truncate table oradba.modmast';
execute immediate 'create table oradba.modmast as (select * from plmprod.modmast);
end
/

I receive the following error:

PLS-00103: Encountered the symbol "create table oradba.modmast as
(select * from plmprod.modmast);
" when expecting one of the following:
( - + case mod new not null
avg
count current exists max min prior sql stddev sum variance
execute forall merge time timestamp interval date

pipe

Any ideas?

[davey23uk]

put the truncate line from your second version into your first version and then it should work

the create table statement is plain wrong as it already exists

[adewri]

execute immediate 'create table oradba.modmast as (select * from plmprod.modmast);

This should be an insert statement rather than a create statement. You already have the table as davey has mentioned above, why do you want to create it again ?