This discussion is archived
6 Replies Latest reply: Jan 2, 2013 10:22 AM by rp0428 RSS

how to run batch file using trigger?

961076 Newbie
Currently Being Moderated
Hi, i am trying run a batch file using a trigger on a table.
say i have a table XYZ(col_1 varchar2 primary key, col_2 varchar2)
the values of col_2 keeps changing and col_1 are constant
whenever the value of col_2 in any row changes from "on" to "off" i want to run a batch file C:\Users\ABC\Desktop\test.bat

i did some searching but couldn't find any solution. Thanks in advance

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
  • 1. Re: how to run batch file using trigger?
    Marwim Expert
    Currently Being Moderated
    Hello,

    you can start a os script from PL/SQL using dbms_scheduler. You can find an example here {message:id=3895983} or search the forum http://www.google.com/search?q=site%3Aforums.oracle.com+dbms_scheduler+shell

    Be careful when you do things like sending a mail or starting an os script from a trigger. A trigger might be executed more than once within a transaction and a rollback might not suppress the execution of the initiated action.

    Regards
    Marcus
  • 2. Re: how to run batch file using trigger?
    BluShadow Guru Moderator
    Currently Being Moderated
    As Marwim indicates, this is not what triggers are intended for. Using a trigger to execute a batch file is an abuse of the database engine and therefore essentially the wrong tool for the job.

    Whatever application is changing the value on the table should be coded to do whatever needs to be done to execute the batch file as part of it's sequential processing. Do not abuse triggers for application level processing.
  • 3. Re: how to run batch file using trigger?
    961076 Newbie
    Currently Being Moderated
    Thank you Marwim but its not about sending mail.

    whenever a row is updated from "on"->"off", i will be running a set of goldengate commands then some sql statements and then some goldnegate commands again.
    To automate this sequence i am trying to use batch file.
    i have considered using scheduler but then i thought there might have been a better/ easier way to do it. So trying triggers.


    hey Blushadow, I do not have access to modify the application. That is why i have considered using triggers.
  • 4. Re: how to run batch file using trigger?
    Marwim Expert
    Currently Being Moderated
    things like sending a mail or starting an os script from a trigger
    Sending a mail is only one example that falls in the same category as starting an os script.

    How often will the script be started. Can you eliminate the possibility that the stript is started more than once before it has been running successfuly?

    A suggestion: can you schedule a job that checks on regular intervalls whether the table has changed and that will then start the batch script?

    Regards
    Marcus
  • 5. Re: how to run batch file using trigger?
    961076 Newbie
    Currently Being Moderated
    the script will be started around 60-70 times a day

    I am looking for options that do not include scheduler. I came across one option where i can install os_command package and run the OS COMMANDS from sql.
    reference PDF: http://www.oracle.com/technetwork/database/enterprise-edition/calling-shell-commands-from-plsql-1-1-129519.pdf
  • 6. Re: how to run batch file using trigger?
    rp0428 Guru
    Currently Being Moderated
    >
    the script will be started around 60-70 times a day
    >
    No - as marwim said the script will be executed every time the trigger is fired. And that trigger, depending on how it is written, might be fired several times for the same transaction.

    And if the trigger is fired the script will be executed even if the transaction that caused the trigger to fire issues a ROLLBACK.

    Triggers are non-transactional. You need to clearly define the business rules for when the script should run. Should it run only when the transaction is COMMITTED? Or should it run if the transaction executes even if the transaction performs a ROLLBACK?

    A trigger is the wrong solution for your problem.

    Please clarify what the business rules are that should control the execution of the script.

Legend

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