1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Hi i need details on dbms_job

Discussion in 'SQL PL/SQL' started by Shanmugapriya, Jul 19, 2016.

  1. Shanmugapriya

    Shanmugapriya Active Member

    Messages:
    32
    Likes Received:
    0
    Trophy Points:
    80
    Location:
    Bangalore
    Hi,
    I am trying to run a job for every 10 seconds and my code is as below: I tried to check in user_jobs but does not seem to run every 10 seconds. Also, I tried to enter sysdate+5/1440 for interval, it throws error. Kindly advise.

    declare
    my_job number;
    begin
    dbms_job.submit(job => my_job,
    what => 'begin PIPE_SEND_RECEIVE_PKG.pipe_receive_proc:)pipe_name); end;',
    next_date => (sysdate+5/1440),
    interval => 'null');
    end;
    /
     
  2. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    You can't run an internal job that often. What problem are you really trying to solve? Why do you 'need' to run an internal job that often? Even cron won't schedule any shorter interval than a minute. The task the job is running probably takes longer than 10 seconds to complete.
     
  3. Shanmugapriya

    Shanmugapriya Active Member

    Messages:
    32
    Likes Received:
    0
    Trophy Points:
    80
    Location:
    Bangalore
    Thank you for your reply. But unfortunately my requirement keeps changing. Came up with new problem now.

    I am trying to call the receive proc which is below through dbms_job to run frequently. I have the create pipe inside this receive proc. So, say my dbms_job runs 50 per day, then the same pipe_name gets created again and again 50 times. However, my requirement is to make sure it is created only once. Is there any way to achieve this?

    PROCEDURE pipe_receive_error_proc
    AS
    message1 varchar2(50);

    BEGIN
    STATUS := dbms_pipe.create_pipe(pipe1_c);
    -- listen for incoming messages on the pipe
    STATUS := dbms_pipe.receive_message(pipename => pipe1_c, timeout => dbms_pipe.maxwait);

    -- message received successfully.
    IF STATUS = 0 THEN

    dbms_pipe.unpack_message(message1);
    dbms_output.put_line('message1 received: ' || message1);

    INSERT INTO nmp15be_batch_log (MONTH_YEAR, CREATED_BY, column1)
    VALUES (SYSDATE, USER, message1);
    COMMIT;


    END IF;

    EXCEPTION
    WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE('ERROR IN RECEIVE PROC' || SUBSTR(SQLERRM,1,200));
    END pipe_receive_error_proc;
     
  4. krasnoslobodtsev_si

    krasnoslobodtsev_si Forum Genius

    Messages:
    626
    Likes Received:
    129
    Trophy Points:
    810
    Location:
    Russian Federation
    Hi.
    Why to launch reading the channel each 10 seconds?

    What task at you?
    If within one transaction, then it is possible to use dbms_alert.

    It is possible just to create the job...
    For example :
    Code (SQL):
    BEGIN
        dbms_scheduler.create_job
            (
                job_name         => 'your_jobs',
                job_type         => 'plsql_block',
                start_date       => sysdate + 10/24/60 ,
                repeat_interval  => 'freq=secondly; interval=10;' ,
                job_action       => 'pipe_receive_error_proc;'
                 enabled   => TRUE,  
                 comments         => 'pipe_receive'
             );
    END;  
     
     
    Last edited: Jul 20, 2016
  5. Shanmugapriya

    Shanmugapriya Active Member

    Messages:
    32
    Likes Received:
    0
    Trophy Points:
    80
    Location:
    Bangalore
    Hi Sergey,
    I understood your point but just wanted to ask one question which is in my mind.
    The procedure 'pipe_receive_error_proc;' which is written to receive messages by running in the scheduler as you have mentioned.

    But in the actual 'pipe_receive_error_proc;' , we have the syntax of creating the pipe as highlighted below.

    So, whenever my scheduler calls this proc "pipe_receive_error_proc", the "STATUS := dbms_pipe.create_pipe(pipe1_c);" keeps getting created.
    I need this to be created only once irrespective of how many ever times my scheduler runs. Is there any way to do this?



    PROCEDURE pipe_receive_error_proc
    AS
    message1 varchar2(50);

    BEGIN
    STATUS := dbms_pipe.create_pipe(pipe1_c);

    IF STATUS = 0 THEN

    dbms_pipe.unpack_message(message1);
    dbms_output.put_line('message1 received: ' || message1);

    END IF;

    EXCEPTION
    WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE('ERROR IN RECEIVE PROC' || SUBSTR(SQLERRM,1,200));
    END pipe_receive_error_proc;
     
  6. krasnoslobodtsev_si

    krasnoslobodtsev_si Forum Genius

    Messages:
    626
    Likes Received:
    129
    Trophy Points:
    810
    Location:
    Russian Federation
    If you use the maximum wait time when reading the channel then it is necessary to use the permanent job.
    It is possible to use implicit creation of the channel.
     
    Last edited: Jul 20, 2016
  7. Shanmugapriya

    Shanmugapriya Active Member

    Messages:
    32
    Likes Received:
    0
    Trophy Points:
    80
    Location:
    Bangalore
    So, if I use
    STATUS := dbms_pipe.receive_message(pipename => pipe1_c); without maxwait, would it work.
    So need to write create pipe too?
    STATUS := dbms_pipe.create_pipe(pipe1_c);


    Sorry to keep bugging on this
     
  8. krasnoslobodtsev_si

    krasnoslobodtsev_si Forum Genius

    Messages:
    626
    Likes Received:
    129
    Trophy Points:
    810
    Location:
    Russian Federation
    pseudo-code of procedure
    Code (SQL):
    DECLARE
        l_message varchar2(1000);
        l_status INT;
        l_pipename varchar2(32) := 'TESTPIPE';
    BEGIN
     
        l_status := dbms_pipe.create_pipe(l_pipename);
        loop
            l_status := dbms_pipe.receive_message(pipename => l_pipename, timeout => dbms_pipe.maxwait);
            -- message received successfully.
            IF l_status = 0 THEN
                dbms_pipe.unpack_message(l_message);    
                -- write to table...
            END IF;
            exit WHEN l_status != 0 OR l_message =' Stop_Job';
        END loop;
    END;
     
    code of job:
    Code (SQL):

    BEGIN
        dbms_scheduler.create_job
            (
                job_name         => 'your_jobs',
                job_type         => 'plsql_block',
                start_date       => systimestamp,
                repeat_interval  => NULL,
                job_action       => ' your_procedure '
                enabled   => TRUE
             );
    END;
    /
     
    Last edited: Jul 20, 2016