Execute update query in the stored procedure
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 5 of 5

Thread: Execute update query in the stored procedure

  1. #1
    Join Date
    Jun 2003
    Posts
    132

    Execute update query in the stored procedure

    Hi,

    Does anyone know how to execute an update queary within the stored procedure.

    I would like to execute the following update query in the stored procedure and then use the trigger AFTER insert or update to execute the stored procedure.

    ----------------------------------------
    Select UPPER(company_name)
    From company_leads;

    Update company_leads
    set company_name = Upper(company_name);
    ----------------------------------------

    Thanks for your help.

    Roman

  2. #2
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    You seem to want to execute an update to set the company name to upper case, and use a trigger to do the same thing. How is this different frm your previous post on the subject? Pando gave you the exact trigger code you need.
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  3. #3
    Join Date
    Jun 2003
    Posts
    132
    Hi,

    I do not want to bother anybody, I just do not understand Pando's example.

    -----------------------------------------------------
    create or replace trigger trg_seq_maestra_agregados
    before insert on maestra_agregados
    for each row
    begin
    select seq_maestra_agregados.nextval into :new.cod_agregado from dual;
    :new.des_agregado := upper(:new.des_agregado);
    -------------------------------------------------------------

    I do not understand what new.code.agregado means.

    If I were to apply to my taks:

    ------------------------------------------------
    begin
    select company_name into :new.company_name
    from dual;
    :new.company_name := upper(:new.company_name);
    end;
    ------------------------------------------------

    It is an invalid trigger, and I do not know how to fix it. Can someone please direct me to solve my problem

    Thank you

    Roman

  4. #4
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,095
    Code:
    SQL> create table xyz (company_name varchar2(20));
    
    Table created.
    
    SQL> create or replace trigger xyz_bia
      2  before insert or update on xyz
      3  for each row
      4  begin
      5     :new.company_name := upper(:new.company_name);
      6* end;
    SQL> /
    
    Trigger created.
    
    SQL> commit;
    SQL> insert into xyz values ('foobar');
    
    1 row created.
    
    SQL> commit;
    
    Commit complete.
    
    SQL> select * from xyz;
    
    COMPANY_NAME
    --------------------
    FOOBAR
    
    SQL>  insert into xyz values ('foobar2');
    
    1 row created.
    
    SQL> commit;
    
    Commit complete.
    
    SQL> select * from xyz;
    
    COMPANY_NAME
    --------------------
    FOOBAR
    FOOBAR2
    
    SQL> update xyz set company_name = 'foobar1' where company_name = 'FOOBAR';
    
    1 row updated.
    
    SQL> commit;
    
    Commit complete.
    
    SQL> select * from xyz;
    
    COMPANY_NAME
    --------------------
    FOOBAR1
    FOOBAR2
    
    SQL>
    Jeff Hunter
    marist89@yahoo.com
    http://marist89.blogspot.com/
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."

  5. #5
    Join Date
    Jun 2003
    Posts
    132
    Thank you for the clarification

    Roman

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