CODEWITHSUNDEEP
sqldatabaseoracle-databaseplsql
Lucas Clayton
Lucas Clayton

Reputation: 23

How to search 30 minute interval in plsql?

I want to write a PL/SQL job that looks a specific table and insert some specific entries suit some condition into another table.

In specific column I mean, in status column there may be 'Ongoing'. I want to search these column that contain 'ongoing' entry at most 1 hour time interval between sysdate.

I have this code,

begin
    SYS.DBMS_JOB.CHANGE
      ( job        => 121313
       ,what       =>  q'#
                          DECLARE
                            v_say         NUMBER;
                          BEGIN
                            select rowid, a.* 
                            from project_situation a 
                            WHERE a.STATUS like '%ONGOING%' 
                              and (a.CREATION_DATE) >= '?????' 
                            order by a.creation_date desc;

                            COMMIT;  
                          END;                   
                       #'
       ,next_date  =>  TRUNC(SYSDATE + 1) + 7/24
       ,interval   => 'TRUNC(SYSDATE + 1) + 7/24'
    );
    commit;    
end;
/

There may be a lot of entries in table. I want to fetch entries whose creation time is bigger than 1 hour. Creation time format is '22/09/2018 11:52:20'

Also I want to run this job every 30 minutes.

Upvotes: 1

Views: 2368

Answers (3)

Littlefoot
Littlefoot

Reputation: 142743

This is how you get an hour ago - subtract 1 hour (1 / 24, as there are 24 hours in a day):

SQL> alter session set nls_date_format = 'dd.mm.yyyy hh24:mi:ss';

Session altered.

SQL> select sysdate right_now,
  2         sysdate - 1/24  one_hour_ago
  3  from dual;

RIGHT_NOW           ONE_HOUR_AGO
------------------- -------------------
22.06.2018 11:24:09 22.06.2018 10:24:09

SQL>

It means that condition you're looking for is

WHERE a.STATUS like '%ONGOING%' 
  AND a.creation_date >= sysdate - 1/24

Running a job every 30 minutes requires something like this:

trunc(sysdate, 'hh') + 30 / (24 * 60)
  • 30 is "minutes"
  • 24 * 60 is "24 hours in a day by 60 minutes in an hour"

A remark, if I may: I'd suggest you to move code you wrote into what into a stored procedure. It is easier to maintain it. Also, as it is PL/SQL, its select requires an into clause which you don't have, so that code would fail. Also, what do you plan to do with values the query returns? Maybe the simplest option is to insert them into some table and then do something with them.

Upvotes: 2

SantoshPawar
SantoshPawar

Reputation: 203

For Better Performance please use following code for calculating 1 hour

PERSISTED_DATE<SYSDATE-60/1440.

Where PERSISTED_DATE is your date column 60 is number of min. 60/1440 is 1 hour time.

For your example assuming you have given table and column following code should fetch you required result

select rowid, a.* from project_situation a 
         WHERE a.STATUS like '%ONGOING%' 
                and a.CREATION_DATE< SYSDATE-60/1440';

If you have fixed status than use = rather the like'%status%' operator.

Upvotes: 3

kayakpim
kayakpim

Reputation: 995

Question is confusing as you say you want the creation time to be > 1hr and above <1 hr

If you have a lot of data I'd use a single statement and ensure the table is properly indexed

INSERT INTO target_table (
 SELECT a.*
 FROM project_situation
 WHERE a.status like '%ONGOING%'
 AND a.creation_date < (sysdate - 1/24)
)

If you need to compare a string to a date and your column isn't a date you'll need to add to_date(creation_date,'DD/MM/YYYY HH24:MI:SS')

You are correct in using dbms_job, but you could also use dbms_scheduler or write a OS batch to call your script on a regular basis

Upvotes: 0

Related Questions