1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Procedure to update a column status

Discussion in 'SQL PL/SQL' started by sellyh19, Mar 11, 2011.

  1. sellyh19

    sellyh19 Active Member

    Messages:
    17
    Likes Received:
    0
    Trophy Points:
    80
    i created the following procedure that will update status to closed and it is failling:

    create or replace procedure hr_updatestatus_sp
    as
    begin
    update hr_staffhandsetbenefits_tb
    set stat_code='20'
    where sysdate >= trunc(due_date)
    end;

    can anyone help me figure out what is wrong?
     
  2. kiran.marla

    kiran.marla Forum Genius

    Messages:
    403
    Likes Received:
    52
    Trophy Points:
    505
    Location:
    Khammam
    What is your exact requirement..
    can you Mention the prerequisites...
     
  3. sellyh19

    sellyh19 Active Member

    Messages:
    17
    Likes Received:
    0
    Trophy Points:
    80
    My requirement is to update the column status after every two years, the default status is active and it must be updated to closed after every two years.
     
  4. kiran.marla

    kiran.marla Forum Genius

    Messages:
    403
    Likes Received:
    52
    Trophy Points:
    505
    Location:
    Khammam
    Can you give me table stats, i want to check the datatype??
     
  5. rajavu

    rajavu Forum Guru

    Messages:
    815
    Likes Received:
    52
    Trophy Points:
    610
    Location:
    @ Bangalore , India
    You can achieve the same using Jobs in Oracle
     
  6. sellyh19

    sellyh19 Active Member

    Messages:
    17
    Likes Received:
    0
    Trophy Points:
    80
    create table "hr_status_tb"
    ( "stat_code" number,
    "stat_description" varchar2(20),
    constraint "hr_status_tb_pk" primary key ("stat_code") enable
    )
     
  7. rajavu

    rajavu Forum Guru

    Messages:
    815
    Likes Received:
    52
    Trophy Points:
    610
    Location:
    @ Bangalore , India
    You are trying to update hr_staffhandsetbenefits_tb. But you showed us the structure of table hr_status_tb. Btw have you checked the link In posted ? (for dong the same through jobs)
     
  8. sellyh19

    sellyh19 Active Member

    Messages:
    17
    Likes Received:
    0
    Trophy Points:
    80
    In your ealier post you asked me to send you the status table structure. The table to be updated is the hr_staffhandsetbenefits_tb this tb references the status table. hope you got my point.
     
  9. neullson

    neullson Active Member

    Messages:
    6
    Likes Received:
    0
    Trophy Points:
    55
    Yeah, I same thought like Rajavu. You are trying to update Table 'hr_staffhandsetbenefits_tb', but you show us the structure of Table 'hr_status_tb'.

    How can this be done, if it has different object..?? :)
     
  10. sellyh19

    sellyh19 Active Member

    Messages:
    17
    Likes Received:
    0
    Trophy Points:
    80
    It is possible, anyway its ok i got it right.
     
  11. rajavu

    rajavu Forum Guru

    Messages:
    815
    Likes Received:
    52
    Trophy Points:
    610
    Location:
    @ Bangalore , India
    The please post the solution for rest of the community...