How to search a range of values and to do update with the result

Hi experts, I have:
SELECT * FROM A_5MIN_TST1
WHERE (TO_CHAR(utctime, 'MI')) 
NOT IN ('00','05','10','15','20','25','30','35','40','45','50','55')
OR (TO_CHAR(utctime, 'SS')) NOT IN ('00') ;

Open in new window

This query gets:
UTCTIME|POINTNUMBER|SITEID|VALOR_INST|TLQ_INST|VALOR_PROM|TLQ_PROM|VALOR_MAX|TLQ_MAX|UTCTIME_MAX|VALOR_MIN|TLQ_MIN|UTCTIME_MIN
01/08/2013 12:06:00 a.m.|330062|1|1|55|0,11948|32|0,11948|32|01/08/2013|1|32|31/07/2013 11:59:56 p.m.
01/08/2013 12:06:32 a.m.|330062|1|5|55|0,11948|32|0,11948|32|01/08/2013|1|32|31/07/2013 11:59:56 p.m.
01/08/2013 12:11:00 a.m.|330065|2|10|55|0|23|0|32|01/08/2013 12:05:00 a.m.|0|32|01/08/2013 12:05:00 a.m.
01/08/2013 12:11:32 a.m.|330065|2|10|55|0|23|0|32|01/08/2013 12:05:00 a.m.|0|32|01/08/2013 12:05:00 a.m.
01/08/2013 12:01:00 p.m.|330065|2|0|0|0|32|0|32|31/07/2013 11:55:00 p.m.|0|32|31/07/2013 11:55:00 p.m.
01/08/2013 12:01:32 p.m.|330065|2|0|0|0|32|0|32|31/07/2013 11:55:00 p.m.|0|32|31/07/2013 11:55:00 p.m.
01/08/2013 12:11:23 a.m.|330065|2|10|55|0|23|0|32|01/08/2013 12:05:00 a.m.|0|32|01/08/2013 12:05:00 a.m.
01/08/2013 12:09:00 a.m.|330062|1|10|55|2|32|5|32|01/08/2013 12:05:00 a.m.|1|32|01/08/2013 12:05:00 a.m.
01/08/2013 12:14:00 a.m.|330062|1|15|55|0,11948|32|0,11948|32|01/08/2013 12:10:00 a.m.|0|32|01/08/2013 12:10:00 a.m.

Open in new window

I need to make a small block, to perform this search, and then run a update with the results:
update_test.sql

Open in new window

from a range of tables:
a_5min_tst1
a_5min_tst2

Open in new window

Then for each pointnumber, perform calculations in 5 minutes and perform the update on the same table, in the 5 minute mark.
Then remove the data from the first select.

Example, with this rows:
01/08/2013 12:06:00 a.m.|330062|1|1|55|0,11948|32|0,11948|32|01/08/2013|1|32|31/07/2013 11:59:56 p.m.
01/08/2013 12:06:32 a.m.|330062|1|5|55|0,11948|32|0,11948|32|01/08

Open in new window

Do the math and make update on:
'01/ 08/2013 12:05:00 am ' 

Open in new window

with tis rows (examples):
01/08/2013 12:11:00 a.m.|330062|1|1|55|0,11948|32|0,11948|32|01/08/2013|1|32|31/07/2013 11:59:56 p.m.
01/08/2013 12:14:32 a.m.|330062|1|5|55|0,11948|32|0,11948|32|01/08

Open in new window

An update on:
'01/ 08/2013 12:10:00 am ' 

Open in new window

I do not know how to handle every 5 minutes.
Also, how to do it for all tables.
Could you help me with some code esqueleton, or example?
Thankyou!
update-test.sql
a-5min-tst1.sql
a-5min-tst2.sql
inserts-tst1.sql
inserts-tst2.sql
carlino70Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

oleggoldCommented:
for every 5 minutes you could use oracle dbms_job:
http://docs.oracle.com/cd/B19306_01/appdev.102/b14258/d_job.htm
0
oleggoldCommented:
here some good examples:
declare
  my_job number;
begin
  dbms_job.submit(job => my_job, 
    what => 'my_procedure(foo);'
    next_date => sysdate+1,
    interval => 'sysdate+1');
end;
/

Open in new window

0
carlino70Author Commented:
Thanks, but I don't need to create a job, but if I want to know how to handle the select and update intervals
0
carlino70Author Commented:
I could solve with:

 str_date :=
                  'select TRUNC(TO_DATE(min(utctime),''DD/MM/YYYY HH24:MI:SS'')) 
                                           + CEIL((((TO_DATE(min(utctime),''DD/MM/YYYY HH24:MI:SS'') 
                                           - TRUNC(TO_DATE(min(utctime),''DD/MM/YYYY HH24:MI:SS''))) * 1440) - 4) / 5) * 5 / 1440 DESDE, 
                                           TRUNC(TO_DATE(min(utctime),''DD/MM/YYYY HH24:MI:SS'')) 
                                           + CEIL((((TO_DATE(min(utctime),''DD/MM/YYYY HH24:MI:SS'') 
                                           - TRUNC(TO_DATE(min(utctime),''DD/MM/YYYY HH24:MI:SS''))) * 1440) / 5))  * 5 / 1440 HASTA 
                                          from  '
               || v_tables.table_name
               || '
                                          where (to_char(utctime, ''mi'')) 
                                                 not in (''00'',''05'',''10'',''15'',''20'',''25'',''30'',''35'',''40'',''45'',''50'',''55'')
                                          or (to_char(utctime, ''ss'')) not in (''00'') ORDER BY UTCTIME'
                                          ;

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
carlino70Author Commented:
I could solve it myself
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.