Re: about dbms_job

A sample code :
(I have Oracle 9i,

 I don't know if previous versions works well).

(

 report_maker is table

• temporary - no index - just for the sample :

CREATE TABLE REPORT_MAKER ( REPORT_MAKER_ID NUMBER (9) NOT NULL, REPORT_NAME VARCHAR2 (30) NOT NULL, LAST_TIMESTAMP DATE, USER_ID NUMBER) )

CREATE OR REPLACE package S_GENERAL is

function getReportTimeStamp(

p_report_name in varchar2,

p_user_id in integer) return date;

function updateReportTimeStamp(

p_report_name in varchar2,

p_user_id in integer) return date;

pragma restrict_references(getReportTimeStamp, wnds, wnps);

end S_GENERAL;

/

CREATE OR REPLACE package body S_GENERAL is

function getReportTimeStamp(

p_report_name in varchar2,

p_user_id in integer) return date is

cursor c is

select LAST_TIMESTAMP

from REPORT_MAKER

where REPORT_NAME = p_report_name

and user_id = p_user_id;

res date;

begin

res:= null;

fetch c into res;

close c;

return res;

end;

function updateReportTimeStamp(

p_report_name in varchar2,

p_user_id in integer) return date is

res date;

PRAGMA AUTONOMOUS_TRANSACTION; begin

res:= sysdate;

update report_maker

set LAST_TIMESTAMP = res

where REPORT_NAME = p_report_name

and user_id = p_user_id;

if sql%notfound then

insert into report_maker(

REPORT_MAKER_ID, REPORT_NAME, LAST_TIMESTAMP, USER_ID) values(

SEQ_REPORT_MAKER.nextVal,

p_report_name,

res,

p_user_id);

end if;

commit;

return res;

end;

end S_GENERAL;

/

and in the select statement :

select s_general.updateReportTimeStamp('xxx', 0)

  from dual; Received on Mon May 08 2006 - 06:51:39 CDT