Oracle Procedure
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 7 of 7

Thread: Oracle Procedure

  1. #1
    Join Date
    Jun 2005
    Posts
    11

    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!

  2. #2
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,333
    well post what you have come up with so far and we can suggest improvements

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

  3. #3
    Join Date
    Jun 2005
    Posts
    11

    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
    /

  4. #4
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,333
    and what happened when you compiled it into the database?

  5. #5
    Join Date
    Jun 2005
    Posts
    11

    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?

  6. #6
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,333
    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

  7. #7
    Join Date
    Nov 2002
    Location
    New Delhi, INDIA
    Posts
    1,796
    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."

    Amar's Blog  Get Firefox!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width