This discussion is archived
1 2 Previous Next 17 Replies Latest reply: Apr 19, 2013 7:00 AM by k1ng87 Go to original post RSS
  • 15. Re: Windows task scheduler listen for table on Oracle?
    Mark Malakanov (user11181920) Expert
    Currently Being Moderated
    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).
    It is possible with external proc, but is wrong because is not secure and complex.

    Your scheduled bat should check whatever condition, like is table existing or not, or, better, are some expected rows in some table or not, then it should proceed or exit.

    to check table from bat you can call sqlplus with SQL code.
    like
    file CHECKTABLE.SQL
    WHENEVER SQLERROR EXIT 1
    select * from ATABLE;
    file MY.BAT.CMD
    sqlplus %USER%/%PASSWORD%@ORACLE @CHECKTABLE.SQL
    if %ERRORLEVEL% = 1 goto :EX
    do something
    :EX
    Edited by: Mark Malakanov (user11181920) on Apr 10, 2013 2:20 PM
  • 16. Re: Windows task scheduler listen for table on Oracle?
    k1ng87 Newbie
    Currently Being Moderated
    Hi Mark,

    This is perfect! I have one issue thought, when I run select count(*) from dmsn.ds3r_1xrtt_voice_trigger in Toad, I get a row count of 21 (which I correct), but when I run this:

    @echo select count(*) from dmsn.ds3r_1xrtt_voice_trigger; | sqlplus user/pass@server I get this:

    SQL>
    Count(*)
    0

    why is not giving the same result as toad?

    Edited by: k1ng87 on Apr 19, 2013 6:47 AM

    NVM....I forgot to put COMMIT; at the end of the insert proc in toad. Didn't know that Oracle doesn't auto commit like SQL Server :-). New to the oracle env

    Edited by: k1ng87 on Apr 19, 2013 6:52 AM
  • 17. Re: Windows task scheduler listen for table on Oracle?
    k1ng87 Newbie
    Currently Being Moderated
    Hi Mark,

    So following you guide above, could I do something like this?

    CheckRowCount.SQL

    SELECT COUNT(*) FROM dmsn.ds3r_1xrtt_voice_trigger

    Trigger.BAT.CMD file

    sqlplus %USER%/%PASSWORD%@ORACLE @CheckRowCount.SQL
    if ROWCOUNT < 1 goto :EX
    c:\Autobatch\Spotfire.Dxp.Automation.ClientJobSender.exe http://SERVER/spotfireautomation/JobExecutor.asmx c:\AutoBatch\backup\Trigger_Test.xml
    :EX


    for some reason I don't think the if ROWCOUNT would work. Also, After completion of this:
    c:\Autobatch\Spotfire.Dxp.Automation.ClientJobSender.exe http://SERVER/spotfireautomation/JobExecutor.asmx c:\AutoBatch\backup\Trigger_Test.xml

    if i wanted to run a another SQL command, how would I do that? like a drop table
1 2 Previous Next

Legend

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