CODEWITHSUNDEEP
phppythonxmloracle-databaseplsql
Dustin
Dustin

Reputation: 11

Query and Display Results Every 'X' amount of Minutes from Oracle Database

Is there a way that I can query an Oracle 10g database, and display the results in a dynamically refreshed html file every 3 minutes, for example?

Here is my predicament: I have several queries that I would LOVE to display the results of to a whole organization on a basic HTML web page with some CSS. The problem is that I do NOT want a user to be able to constantly refresh a page in his/her browser, and thus severely bog down the database. I have no problem writing the queries, or writing the HTML and CSS needed to display the tables. It's almost as if I would like to query, export results to XML every 3 minutes, and constantly have an HTML or PHP file that is pointing to the dynamically updated XML file. I am open to other options as well...

I have basic user access with the Oracle DB...nothing Admin like. I do have access to a server, though, and have experience with PHP, PL/SQL, and HTML. Perhaps I would have to get into a lower level programming language like Python? I am kind of stuck here. Any kind of help would be appreciated!

Upvotes: 1

Views: 233

Answers (2)

tbone
tbone

Reputation: 15473

Setup a materialized view(mv), point your app to this mv, and then setup a scheduler job to refresh it on whatever frequency you like.

See dbms_scheduler for setting up scheduler jobs in Oracle.

One note: you may want to do an atomic_refresh=>true to do deletes/inserts into the mv instead of truncate/insert (if atomic_refresh=>false, there will be 0 rows in mv until refresh is completed).

An simple example mv creation:

create materialized view MY_MV
tablespace MY_TS
build immediate
refresh complete on demand
with primary key
as
SELECT a.foo, b.bar
from table_a a, table_b b
where a.col1 = b.col2
and a.baz='BLAH'
;

An example refresh call:

dbms_mview.refresh('MY_MV', 'C', atomic_refresh=>true);

Upvotes: 1

hex4
hex4

Reputation: 695

you can also execute an Ajax Request every 3 minutes using the setTimeout() function.

Using jQuery framework

$(document).ready(function() {
    setTimeout("getFeed()", 180000); //180000 = 3 minutes in milliseconds
});

function getFeed() {
    //ajaxRequest here
}

For more info on ajax you can go here: http://api.jquery.com/jQuery.ajax/

Upvotes: 2

Related Questions