Hi All,
Oracle version :- Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
I have created a procedure and scheduled it to run every 1 hour interval through dbms_job.submit.
Procedure details:
create or replace
PROCEDURE TEMP_INSERT AS
BEGIN
insert into temp_audit_event
(AUDIT_EVENT_ID,
EVENT_TYPE_CODE,
USER_ID,
EVENT_DATE,
MESSAGE,
NEW_VALUE,
OLD_VALUE
)
SELECT AUDIT_EVENT_ID,
EVENT_TYPE_CODE,
USER_ID,
EVENT_DATE,
MESSAGE,
NEW_VALUE,
OLD_VALUE
FROM ITG_AUDIT_EVENTS
where last_update_date < date'2014-6-1'
and rownum <100;
commit;
dbms_output.put_line(sql%rowcount);
END TEMP_INSERT;
The above is a test procedure where upon execution it will insert 99 rows at a time in the target table. Once the insert is complete the dbms_output will display out put as 1,2,3,.. so on.
Next step to automate i have created a job:
Variable jobno number
Begin
DBMS_JOB.SUBMIT(
Job => :jobno,
What => 'temp_insert;',
Next_date =>trunc(sysdate+1),
Interval => 'trunc(sysdate +1)'
);
Commit;
End;
After executing the above block, now i am able to automate the execution on daily basis.
I can check the status through quering dba_jobs table.
My Problem:
The dbms_output.put_line which i have used in procedure will not display any out put through this job.
Requesting any inputs/thoughts how or what is the way where one can display the out put of job on this would be really help full.
Please let me know if you need any other info.
Thanks & Regards,
Sameet Kumar Patro
Email:- sameet.patro@gmail.com