Printable View
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!
well post what you have come up with so far and we can suggest improvements
p.s. have you tried googling for create procedure?
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
/
and what happened when you compiled it into the database?
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?
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
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 ?Code:execute immediate 'create table oradba.modmast as (select * from plmprod.modmast);