-
Oracle Procedure
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?
-
Ok .. here goes
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?
-
Errors
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
-
Code:
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 ?
Amar
"There is a difference between knowing the path and walking the path."
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|