DBMS_JOB Procedure!!!
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 9 of 9

Thread: DBMS_JOB Procedure!!!

Hybrid View

  1. #1
    Join Date
    Jun 2004
    Posts
    125

    DBMS_JOB Procedure!!!

    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.

    Code:
    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.

  2. #2
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    Jeff Hunter
    marist89@yahoo.com
    http://marist89.blogspot.com/
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."

  3. #3
    Join Date
    Jun 2004
    Posts
    125
    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.
    Code:
    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.

  4. #4
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    Originally posted by see_one
    If someone can shed some light that would be great, no stupid replies ever appreciated.
    WTF?
    Jeff Hunter
    marist89@yahoo.com
    http://marist89.blogspot.com/
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."

  5. #5
    Join Date
    Nov 2000
    Location
    Pittsburgh, PA
    Posts
    3,997
    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?

    Code:
    CREATE OR REPLACE PROCEDURE PRINT_NAME IS
    
    BEGIN
    
    DBMS_OUTPUT.PUT_LINE('MY NAME IS JOHNNY');
    
    END;
    this space intentionally left blank

  6. #6
    Join Date
    Nov 2000
    Location
    Pittsburgh, PA
    Posts
    3,997
    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.
    this space intentionally left blank

  7. #7
    Join Date
    May 2005
    Posts
    31
    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
    Last edited by Bonker; 05-05-2005 at 07:52 AM.
    Experience is a hard teacher because she gives the test first, the lesson afterwards.

  8. #8
    Join Date
    Jul 2008
    Posts
    1

    Non-trivial DBMS_JOB PUT_LINE output

    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

  9. #9
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,333
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width