This discussion is archived
1 2 Previous Next 17 Replies Latest reply: Apr 19, 2013 7:00 AM by k1ng87 RSS

Windows task scheduler listen for table on Oracle?

k1ng87 Newbie
Currently Being Moderated
Is it possible for Windows Task Scheduler to listen for a table on Oracle (Unix box), and if it exists, run the task it is assigned to.

Or

Is it possible to install Oracle agent on the Windows box, listen for a table on the Oracle DB (Unix Box) and have the Oracle agent on the windows box run the windows task scheduler event?
  • 1. Re: Windows task scheduler listen for table on Oracle?
    sb92075 Guru
    Currently Being Moderated
    user13340968 wrote:
    Is it possible for Windows Task Scheduler to listen for a table on Oracle (Unix box), and if it exists, run the task it is assigned to.

    Or

    Is it possible to install Oracle agent on the Windows box, listen for a table on the Oracle DB (Unix Box) and have the Oracle agent on the windows box run the windows task scheduler event?
    what kind of sound does a table make?

    what does "listen for a table mean"?



    How do I ask a question on the forums?
    SQL and PL/SQL FAQ
  • 2. Re: Windows task scheduler listen for table on Oracle?
    k1ng87 Newbie
    Currently Being Moderated
    thats a good question...hahaha....just want it to listen if the table exists or not.
  • 3. Re: Windows task scheduler listen for table on Oracle?
    sb92075 Guru
    Currently Being Moderated
    user13340968 wrote:
    thats a good question...hahaha....just want it to listen if the table exists or not.
    SELECT COUNT(*) FROM MYSTERY_TABLE;
    when no error is thrown, then table exists.

    for all professional applications, application objects are static & fixed between version updates.
    it is amateur "design" to CREATE TABLE as part of daily application activity; so table should always exist!
  • 4. Re: Windows task scheduler listen for table on Oracle?
    Girish Sharma Guru
    Currently Being Moderated
    thats a good question...hahaha....just want it to listen if the table exists or not.
    Are you looking for below thread ?

    check if table exists
    check if table exists

    Regards
    Girish Sharma
  • 5. Re: Windows task scheduler listen for table on Oracle?
    k1ng87 Newbie
    Currently Being Moderated
    I understand how to write the SQL query, what I'm having trouble with is how to implement that in a Windows Task Scheduler so when the table does exist, the task scheduler kicks off. Not sure if its easy to do it in that or use Oracle Agent on the remote server(not even sure if that can do this)? I'm new to the oracle environment as well but am extremely familiar with SQL.

    Basically, during the ETL, an analysis runs and if a business critical event occurs, it would create a table (which the report server would use). The Task Scheduler or Oracle agent would run each minute to listen to that table and if it exists, run a BAT file on the report server which would email out the report.

    The big issue that I'm trying to resolve is essentially how to make the report box (windows) listen to the oracle box (Unix). If the report server detects that table on the Oracle box, it will execute a BAT file (located on the report server) on the report server.
  • 6. Re: Windows task scheduler listen for table on Oracle?
    EdStevens Guru
    Currently Being Moderated
    user13340968 wrote:
    I understand how to write the SQL query, what I'm having trouble with is how to implement that in a Windows Task Scheduler so when the table does exist, the task scheduler kicks off. Not sure if its easy to do it in that or use Oracle Agent on the remote server(not even sure if that can do this)? I'm new to the oracle environment as well but am extremely familiar with SQL.

    Basically, during the ETL, an analysis runs and if a business critical event occurs, it would create a table (which the report server would use). The Task Scheduler or Oracle agent would run each minute to listen to that table and if it exists, run a BAT file on the report server which would email out the report.

    The big issue that I'm trying to resolve is essentially how to make the report box (windows) listen to the oracle box (Unix). If the report server detects that table on the Oracle box, it will execute a BAT file (located on the report server) on the report server.
    This sounds eerily similar to your other two threads:
    Re: Run a BAT file on another machine
    Re: use ssh from PL/SQL to execute bat file on windows server
  • 7. Re: Windows task scheduler listen for table on Oracle?
    k1ng87 Newbie
    Currently Being Moderated
    yes, they are...but it seems extremely difficult for oracle to execute the BAT file on another machine....thinking if I can write a script in windows task scheduler which would run each minute to see if the table is there, if it is, then it can run the process....don't know if this would be easier then trying to get Oracle on Unix to run a BAT file on a windows machine.
  • 8. Re: Windows task scheduler listen for table on Oracle?
    JohnWatson Guru
    Currently Being Moderated
    user13340968 wrote:
    yes, they are...but it seems extremely difficult for oracle to execute the BAT file on another machine....
    This is not correct, it very easy for Oracle to run a file on another machine through the Scheduler. Run the file as a remote job:
    http://docs.oracle.com/cd/E11882_01/server.112/e25494/schedover003.htm#CHDCCDDF
  • 9. Re: Windows task scheduler listen for table on Oracle?
    sybrand_b Guru
    Currently Being Moderated
    This would be an appalling procedure.
    Programs have exception handlers, so you shouldn't need this crap to check the existence of a table.

    ------------
    Sybrand Bakker
    Senior Oracle DBA
  • 10. Re: Windows task scheduler listen for table on Oracle?
    EdStevens Guru
    Currently Being Moderated
    user13340968 wrote:
    yes, they are...but it seems extremely difficult for oracle to execute the BAT file on another machine....thinking if I can write a script in windows task scheduler which would run each minute to see if the table is there, if it is, then it can run the process....don't know if this would be easier then trying to get Oracle on Unix to run a BAT file on a windows machine.
    You are going about this in just about the worst way possible. Instead of fixating on some ill-advised technical solution, why not describe your business problem and ask for the best possible approach?
  • 11. Re: Windows task scheduler listen for table on Oracle?
    k1ng87 Newbie
    Currently Being Moderated
    ok..this sounds pretty dumb but I cna't seem to install the Oracle Scheduler...

    I downloaded this:
    http://www.oracle.com/technetwork/database/enterprise-edition/downloads/index.html (win64_11gR2_database_2of2)

    but there is no setup.exe. file in there. It has a Database folder then stage folder, then components folder which items in it.

    I also have win64_11gR2_database_1of2 but when I go to run the setup.exe file in there, I don't see an option for installing Oracle Scheduler.

    Seeesh...Oracle is very confusing to me right now.
  • 12. Re: Windows task scheduler listen for table on Oracle?
    k1ng87 Newbie
    Currently Being Moderated
    what I''m trying to accomplish is that at the end of a PL/SQL proc. I have an analysis that runs against the data its pulling in to identify a business critical event. If the row count of the analysis is true, it puts that data (less then 100 rows of data 99% of the time) into a a table.

    We also have a visual anlaytics server that has a report connected to that table and a email capabilities setup on the visual analytic server. The server has a BAT file that when ran, will send of a report of that analysis to specific people. Right now, the BAT file can only be run by triggering it manually or scheduling it via Windows Task Scheduler.

    What I want to do is have the end of the PL/SQL proc run the BAT file on the visual analytic server if there is a business critical event (rowcount > 0).

    I have everything put together EXCEPT for the trigger of the BAT file. Another thing to mention is that Oracle is on a Unix box and analytic server is on a Windows box, not sure if that matters or not.
  • 13. Re: Windows task scheduler listen for table on Oracle?
    JohnWatson Guru
    Currently Being Moderated
    user13340968 wrote:
    ok..this sounds pretty dumb but I cna't seem to install the Oracle Scheduler...

    I downloaded this:
    http://www.oracle.com/technetwork/database/enterprise-edition/downloads/index.html (win64_11gR2_database_2of2)

    Wrong download. The docs do say that you need the client CD:
    http://docs.oracle.com/cd/E11882_01/server.112/e25494/schedadmin001.htm#BAJDFHBE
  • 14. Re: Windows task scheduler listen for table on Oracle?
    k1ng87 Newbie
    Currently Being Moderated
    I'm confused, I went to this link from the doc: http://www.oracle.com/technology/software/products/database

    downloaded the two files and ran setup.exe but all I see is options to install Enterprise Edition, Standard, Standard One, or Personal. In the install options for Enterprise, all I see is Oracle Partitioning, OLAP, Label Security, Data Mining RDBMS files, Database Vault, Real Application testig, and Database Extensions for .NET.

    but no Oracle Scheduler...
1 2 Previous Next

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points