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!

Procedure

Discussion in 'SQL PL/SQL' started by pedrojosmen, Nov 23, 2014.

  1. pedrojosmen

    pedrojosmen Active Member

    Messages:
    2
    Likes Received:
    0
    Trophy Points:
    55
    Hi everyone,
    So i would like to make a procedure that it pick up the values of my server(server status,numbers of consultations and connections created )every 3 hours. I want store these values in one table whit this name:
    Test.status_server(idstatus,consultants_number and connections_number)

    I need help for this procedure. Maybe i can make a trigger or what?

    Thanks in advance
     
  2. ocprep

    ocprep Forum Advisor

    Messages:
    277
    Likes Received:
    76
    Trophy Points:
    410
    Location:
    Orlando, Florida
    That's not enough information to make a reasonable suggestion. I can rule out using a trigger if you want the information collected every 'X' hours. Triggers fire when a particular event occurs -- not based on elapsed time. For something that needs to be repeated based on time, you create the procedure that performs the action you want and then create a DBMS_SCHEDULER job to run the procedure on the timescale desired.
     
  3. krasnoslobodtsev_si

    krasnoslobodtsev_si Forum Genius

    Messages:
    626
    Likes Received:
    129
    Trophy Points:
    810
    Location:
    Russian Federation
    Hi.

    Procedure, the job the launching procedure can be written without problems here.
    But it won't be useful for you since you won't gain experience.

    Therefore look at examples in documentation, realize the option according to your job.
    And if there are errors in the course of operation then will help.

    Additional links :

    1) procedure
    2) jobs
    dbms_scheduler
    http://oracle-base.com/articles/10g/scheduler-10g.php


    3) Book
    http://books.google.ru/books/about/Learning_Oracle_PL_SQL.html?id=mHz8a5SZfwYC&redir_esc=y
     
  4. pedrojosmen

    pedrojosmen Active Member

    Messages:
    2
    Likes Received:
    0
    Trophy Points:
    55
    CREATE PROCEDURE test.status (idstatus,connections_number,consultans_number)
    BEGIN
    UPDATE idstatus,connections_number,consultans_number FROM test.status_server
    Dbms_scheduler.create_schedule(‘job2_schedule’,repeat_interval=>
    ‘job1_schedule+OFFSET:3H’);
    END;

    the procedure is Ok?
    I don´t know how i can take the values "number_connection", number_consultans and generate idconsultant" and write it over my table.

    Thanks
     
  5. krasnoslobodtsev_si

    krasnoslobodtsev_si Forum Genius

    Messages:
    626
    Likes Received:
    129
    Trophy Points:
    810
    Location:
    Russian Federation
    No, procedure is written with errors.

    The job is formulated not completely.
    That is: idstatus, connections_number, consultans_number?
    It fields from what sources?

    p.s. parameters of procedure of the job then it is necessary to describe parameters for procedure of the job.