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
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: 25036
Registered: January 2009
Location: SoCal
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
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: 25036
Registered: January 2009
Location: SoCal
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
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: Table doesn't exist?
Next Topic: Trap the error code (ORA-02019, ORA-06550, ORA-00942) (merged 2 threads)
Goto Forum:
  


Current Time: Mon Dec 05 02:49:54 CST 2016

Total time taken to generate the page: 0.19548 seconds