DBMS_JOB Procedure!!!

[see_one]

I have a very simple procdure that I want to run using dbms_job every, say, 30 sec. How would I accomplish it using dbms_job procedure. This is my procedure for printing name.

CREATE OR REPLACE PROCEDURE PRINT_NAME IS

BEGIN

DBMS_OUTPUT.PUT_LINE('MY NAME IS JOHNNY');

END;

I want to know how to submit this print_name procedure to my dbms_job. Thanks.

[marist89]

Have you tried http://download-west.oracle.com/docs...b.htm#ARPLS019 !!!

[see_one]

Thanks for the link, i found many like u gave me. I have trouble submitting my print_name procedure, if, by chance you read it my post.

I have this example, which runs fine.

jobno number;
begin
dbms_job.submit(:jobno,'declare 
x number;
begin
select count(*) into x from dual;
end;',
sysdate );
end;

I am not sure how to submit my print_name and have my output. If someone can shed some light that would be great, no stupid replies ever appreciated. Thanks.

P.S. Using Oracle8i.

[marist89]

Originally posted by see_one
If someone can shed some light that would be great, no stupid replies ever appreciated.

WTF?

[gandolf989]

Originally posted by see_one
If someone can shed some light that would be great, no stupid replies ever appreciated. [/B]

Originally posted by marist89
WTF?

I appreciate stupid replies!!! Besides your a Super Genious, since when have you given a stupid reply outside of obfuscation unlimited? In any event you would think that see_one knows his name is Johnny by now! Why would he need Oracle to remind him every 5 seconds?

CREATE OR REPLACE PROCEDURE PRINT_NAME IS

BEGIN

DBMS_OUTPUT.PUT_LINE('MY NAME IS JOHNNY');

END;

[gandolf989]

see_one, you should either create a stored procedure using utl_file to log to a file or log to a table, or you should just create a cron job calling a stored procedure and redirect the output.

[Bonker]

after changing the procedure to print using utl_file or insert into table you can submit it by

variable x number;
exec dbms_job.submit(:x,'print_name;',trunc(sysdate),'trunc(sysdate)+30/24/60/60');
commit;

'trunc(sysdate)+30/24/60/60' to run every 30 seconds

[toms]

My DBA tells me DBMS_OUTPUT.PUT_LINE output is not retrievable when using DBMS_JOB to run stored Procedures that trap EXCEPTIONs and produce information using PUT_LINE.

Hard to believe.

Anyone know a way to make it work?

Thanks,

Tom

[davey23uk]

dont use dbms_output - its only use is printing debug information to screen - no good in jobs (or even in normal procedures) Where would you expect the output to go when a job runs?

Write lines out to a text file or insert into a table