1

I have a job does work fine, but now I need to send him a parameter when a table is updated. I try this code below but does not work:

   CREATE OR REPLACE TRIGGER "STK_PRUEBA"
   BEFORE INSERT OR UPDATE OR DELETE ON STK_TABLA
   REFERENCING OLD AS O
   NEW AS N
   FOR EACH ROW

   DECLARE
   sms varchar2(200);
   BEGIN  

   IF UPDATING THEN   
      --JOB
      SYS.DBMS_SCHEDULER.CREATE_JOB( job_name => 'ejemplo_job',
         job_type => 'EXECUTABLE',
         job_action => 'C:\WINDOWS\system32\cmd.exe',
         job_class => 'DEFAULT_JOB_CLASS',
         comments => 'Job to call batch script on Windows',
         auto_drop => FALSE,
         number_of_arguments => 4,
         enabled => FALSE);        
      ---Select
      select replace('Informa. Emision ' ||
         fd.tmovimiento || ' ' ||
         fd.fecha || ' Por: ' ||             
         trim(fd.moneda) || ' Venc:'   
      into sms
      from fin_tabla fd
      where fd.clave=:N.FIN_CLAVE;
      ---End Select
      SYS.DBMS_SCHEDULER.SET_JOB_ARGUMENT_VALUE( job_name => 'ejemplo_job', argument_position => 1, argument_value => '/q'); 
      SYS.DBMS_SCHEDULER.SET_JOB_ARGUMENT_VALUE( job_name => 'ejemplo_job', argument_position => 2, argument_value => '/c'); 
      SYS.DBMS_SCHEDULER.SET_JOB_ARGUMENT_VALUE( job_name => 'ejemplo_job', argument_position => 3, argument_value => '"C:\job\ejemplo.bat"'); 
      SYS.DBMS_SCHEDULER.SET_JOB_ARGUMENT_VALUE( job_name => 'ejemplo_job', argument_position => 4, argument_value => sms);    
      SYS.DBMS_SCHEDULER.ENABLE( 'ejemplo_job' ); 
      -- End Job
     END IF;
   END;

When I update a value it show this error:

  ORA-27486:privilegios insuficientes
  ORA-06512: en "SYS.DBMS_ISCHED", linea 124
  ORA-06512: en "SYS.DBMS_SCHEDULER", linea 271
  ORA-06512: en "AD.STK_TABLA", linea 6
  ORA-04088: errod durante la ejecucion del disparador AD.STK_PRUEBA"SYS.DBMS_ISCHED", linea 124

Or how can a I send a parameter to a JOB for my select?

Jorge
  • 11
  • 1
  • 4
    The immediate error appears to indicate that the owner of the trigger doesn't have access to the `dbms_scheduler` package. Once you solve that, however, you should get an error because `create_job` is going to commit and you can't commit inside a trigger. Then there is the issue that you're doing something non-transactional in a trigger which is a generally bad idea. Triggers can get called multiple times, transactions can roll back, etc. I'd strongly suggest looking for an alternate architectural approach. – Justin Cave Jul 17 '15 at 20:12
  • I change the approach and finally use the UTL_HTTP package http://docs.oracle.com/cd/B19306_01/appdev.102/b14258/u_http.htm#CHDIAFFA – Jorge Jul 21 '15 at 13:24
  • If you're making `utl_http` calls in a trigger, that would generally be problematic since the `utl_http` call isn't going to have the same transactional scope that the underlying operation has. Your trigger might fire multiple times for a single row change. Your trigger might fire for a change that is ultimately rolled back. Your trigger might fire hours before the underlying transaction commits. The server you made the `utl_http` call to won't know any of those things. You generally want an asynchronous process that makes the `utl_http` call only after the transaction commits. – Justin Cave Jul 21 '15 at 13:32

0 Answers0