PL/SQL: How to create a RUN table and make sure the procedure runs only once a day

Question

1. How can I make sure my procedure only runs once a day? if it is running, any other attempt would be cancelled. How do I do this?
2. Additionally, how do I create a RUN table that keeps track of when the procedure runs?

create table run_log (runID NUMBER PRIMARY KEY, ModuleName VARCHAR2(35) NOT NULL, RunStartDate DATE NOT NULL, RunEndDate DATE, Outcome VARCHAR2(25), Comments VARCHAR2(255));

 create sequence seq_runID
    minvalue 1
    start with 1
    maxvalue 9999999
    increment by 1;

create or replace procedure run_table IS
    v_runlogrecord run_log%ROWTYPE;
    v_runlogID NUMBER;
    v_unique VARCHAR(20) := to_char(sysdate, 'DDMMYYYY') || '000';
    moduleRan EXCEPTION;
    c_buffer CONSTANT NUMBER :=23/24;
    c_moduleName VARCHAR(25) :='RUN_TABLE';

    Begin
        begin
            My_procedure;
        end;
        begin
            select * INTO v_runlogrecord
            from run_log
            where UPPER(moduleName) = c_moduleName
            AND outcome = 'SUCCESS'
            and RunEndDate > (sysdate-c_buffer);
            RAISE moduleRan;
            Exception
                when NO_DATA_FOUND then
                SELECT seq_runID.NEXTVAL INTO v_runLogID from dual;
                v_runlogID := v_unique + v_runlogID;
                INSERT INTO run_log(runID, ModuleName, RunStartDate, RunEndDate, Outcome, Comments)
                VALUES(v_runlogID, c_moduleName, sysdate, NULL, NULL, 'Start Program');
        end;

        UPDATE run_log 
            set runenddate = sysdate,
            outcome = 'SUCCESS',
            comments = 'Run Completed'
        where runid = v_runlogID;

        EXCEPTION 
            WHEN moduleRan THEN
            DBMS_OUTPUT.PUT_LINE('Already run!');
END;

set SERVEROUTPUT on
exec run_table;

Answer 1

Your procedure has some sever issues, so lets cover them.
Starting from the top with the procedure name. The purpose here is not to "Run_Table" but to run "My_Procedure" while enforcing the Business_Rule that it run only once daily.
Next your attempt to generate a primary key. You create a sequence, which by itself is sufficient, but you carefully construct a key format from the date as a string. Only to ADD that seq.nextval to your string for a key. But what happens seq.nextval reaches 1000. Well you just destroyed the very carefully constructed date defining key.
The execution sequence is rather convoluted. The first thing you do is run the procedure, even if it has already been run. Then you check had it run. If so you raise user defined error and catch to in an exception block. Unfortunately, that block just issues a dbms_output message (probably not available in a production environment). But it does not itself raise an exception nor issue a rollback. As a result your procedure has executed successfully a second time.
Finally, your time between runs (23/24) does not completely enforce once-a-day process,but every 23 hours. So it runs as 00:05 (12:05 AM) and again at 23:10 (11:10 PM) and satisfy your condition but still have the same calendar run date.

RECOMMENDATIONS:

1. Rename your procedure to more clearly define its purpose.
2. Abandon the idea of prefixing the PK with the date, Just use the sequence.
3. Add a column for last run date of type date, but truncated to just date. I leave both the start date and end date for the associated times.
4. Add a unique key for the new run date and module name. This does 2 things. It guarantees the module runs just once on a calendar day and permits using the same log table enforce the same once-per-day rule on additional modules.
5. Alter your procedure flow to insert a row for this procedure, handling the dup_row_on_index exception when needed. Execute the desired procedure, then upon completion set update to complete.
   
   Two items you still need to consider:
6. What happens if/when two or more users attempt to run the procedure at the same time.
7. What happens when the My_Procedure itself has an exception.

 create table run_log
    ( runid         integer 
    , last_run_date date          not null  
    , modulename    varchar2(35)  not null 
    , runstartdate  date          not null
    , runenddate    date
    , outcome       varchar2(25)
    , comments      varchar2(255)
    , constraint    run_log_pk 
                    primary key (runid) 
    , constraint    run_log_bk 
                    unique (last_run_date, modulename)
    , constraint    last_run_date_no_time_ck
                    check ( trunc(last_run_date) = last_run_date) )
    );

 create sequence seq_runid
    minvalue 1
    start with 1
    maxvalue 9999999
    increment by 1;


create or replace procedure my_procedure_daily_run is

    c_modulename  constant varchar(25) :='my_procedure';
    c_run_date    constant date        := trunc(sysdate);

begin
    -- enforce Business Rule: Run Once per Day 
    insert into run_log(runid, last_run_date, modulename, runstartdate, runenddate, outcome, comments)
      values( seq_runid.nextval, c_run_date, c_modulename, sysdate, null, null, 'Start Program');

    -- run procedure 
    my_procedure; 

    -- tag today's run message as complete.
    update run_log 
       set runenddate = sysdate
         , outcome = 'SUCCESS'
         , comments = 'Run Completed'
     where last_run_date = c_run_date
       and modulename    = c_modulename;

exception 
     when dup_val_on_index then
          raise_application_error( -20001, c_modulename || ' has already run for ' || to_char(c_run_date, 'yyyy-mm-dd'));

end my_procedure_daily_run;

--- Test ok
begin 
   my_procedure_daily_run;
end;  

--- Test second run
begin 
   my_procedure_daily_run;
end;