Home » SQL & PL/SQL » SQL & PL/SQL » Specify [INSERT INTO] to run at a specified time every day (Oracle 10g)
Specify [INSERT INTO] to run at a specified time every day [message #312730] Wed, 09 April 2008 12:15 Go to next message
molotovjester
Messages: 3
Registered: April 2008
Location: California
Karma: 0
Junior Member
Hi folks,

Newbie here, set let the flogging begin!

I have a pretty simple query such as...

INSERT INTO WIDGETTRACKING
SELECT COUNT(*) AS NUMWIDGETS
FROM WIDGET


...that I want to run at a specified time each day.

I tried using a MV to run this, but it didnt like the INSERT INTO command, and when the MV runs every day without the INSERT INTO command, it just updates the existing rows instead of appending the table.

Ideas?

Thanks,

MJ
Re: Specify [INSERT INTO] to run at a specified time every day [message #312731 is a reply to message #312730] Wed, 09 April 2008 12:20 Go to previous messageGo to next message
BlackSwan
Messages: 25578
Registered: January 2009
Location: SoCal
Karma: 0
Senior Member
use DBMS_JOB or DBMS_SCHEDULER
Re: Specify [INSERT INTO] to run at a specified time every day [message #312761 is a reply to message #312731] Wed, 09 April 2008 13:17 Go to previous messageGo to next message
molotovjester
Messages: 3
Registered: April 2008
Location: California
Karma: 0
Junior Member
Thank you for your superficially helpful response.

Could you please address the second issue of the query replacing existing rows instead of appending the table with the rows retrieved by each successive query?
Re: Specify [INSERT INTO] to run at a specified time every day [message #312763 is a reply to message #312730] Wed, 09 April 2008 13:27 Go to previous messageGo to next message
BlackSwan
Messages: 25578
Registered: January 2009
Location: SoCal
Karma: 0
Senior Member
The INSERT should work OK, but I see flaw (error of omission) in your plan.

You'll have a bunch of records in WIDGETTRACKING with various values, but you won't know when each of the counts was obtained.
Re: Specify [INSERT INTO] to run at a specified time every day [message #312769 is a reply to message #312763] Wed, 09 April 2008 13:34 Go to previous message
molotovjester
Messages: 3
Registered: April 2008
Location: California
Karma: 0
Junior Member
Good Point.

I'll probably have to roll-up and insert a SYSDATE of sort, yes?

Thank you again for your help.

MJ
Previous Topic: Oracle Connect by - all parents of child in 1 row
Next Topic: please, i need a help in procedure
Goto Forum:
  


Current Time: Tue Aug 22 10:02:39 CDT 2017

Total time taken to generate the page: 0.13213 seconds