Help creating procedure

[Sambo8]

Hi there,

I want a procedure to do the following:

update table1 set eventstate='Processed' where eventstate='Processing' and eventstarttime < sysdate-30;

here is my invalid procedure - please can you tell me where I'm going wrong?

create or replace procedure update_processed
DECLARE
estate varchar2(16) := 'Processed';
whestate varchar2(16) := 'Processing';
etime date := sysdate-30;
BEGIN
update table1
set eventstate= estate
where eventstate=whestate and eventstarttime < etime;
Commit;
End;
/

Cheers,
Sam

 

[Dagon]

You don't need the line with DECLARE on it.

For Oracle-related work, contact me through Linked-In.

 

[Thargy]

For that matter you don't need the declarations at all. What's wrong with

CREATE OR REPLACE PROCEDURE update_processed
AS

BEGIN

UPDATE table1 
   SET EVENTSTATE ='Processed'
 WHERE EVENTSTATE ='Processing'
   AND EVENTSTARTTIME < (sysdate-30);
END;

Since your procedure accepts no parameters, why bother with them?


Regards

T

 

[Dagon]

...or to do it as a procedure at all !

For Oracle-related work, contact me through Linked-In.

 

[Thargy]

No comment.

Regards

T

 

[Sambo8]

Hi all,

Many thanks for your help it needs to be a procedure as I need to schedule it as a job.

Thank you
Sam

 

[Thargy]

ok Sam,

can you let us know if you succeed with this - it would be nice to know - one way or the other.

Regards

T

 

[Sambo8]

Hi There T,

Yes it was a success - many thanks for your help!

Cheers,
Sam