2 Replies Latest reply: Dec 3, 2011 8:09 PM by 704302 RSS

    dbms_job.submit strange behavior

    704302
      I'm Using Oracle DB 11G
      I have a trigger that submits jobs using dbms_job.submit, the trigger is working correctly, passing the job parameters all find, only problem is that those jobs fails with error no data found, where if i take the "What" and run it the statement is processed with no issues what so ever.
      This is a bit strange, but whats' more bizarre for me is that if i issue a run command to that job, it's completed successfully without any errors.
      PS. i'm removing any names/logins/server paths from the code.
      The code of the trigger is:
      create or replace trigger "TTS_REQUEST_MOVE_EMAILS"
      BEFORE
      update on "TTS_REQUEST"
          REFERENCING OLD AS OLD NEW AS NEW
      for each row
      begin
      DECLARE
        jobno NUMERIC;
        submit_code varchar2(4000);
      BEGIN
          if updating and :NEW.CURRENT_PATH != :NEW.NEW_PATH and :NEW.NEW_PATH is not null
      then
      submit_code :=
      'DECLARE
        v_msg mail_t;
        v_msg_id        NUMBER;
        v_username      VARCHAR2(1000);
        v_current_path  VARCHAR2(1000);
        v_new_path      VARCHAR2(1000);
        v_requester     VARCHAR(1000);
        v_received_time VARCHAR(1000);
        v_request_id       number;
        v_submit_code varchar(4000);
        v_error_code number;
        v_error_message varchar2(255);
      BEGIN
        v_username      := '|| '''' || :NEW.USERNAME || '''' || ';
          v_current_path  := '|| '''' || replace(replace(:NEW.CURRENT_PATH, 'IBGroup\', ''),'\','/') || '''' || ';
        v_new_path      := '|| '''' || :NEW.NEW_PATH|| '''' || ';
        v_requester     := '|| '''' || :NEW.REQUESTER || '''' || ';
        v_received_time := '|| '''' || to_char(:NEW.RECEIVED_TIME,'DD-MON-YYYY HH24:MI:SS') || '''' || ';
        v_request_id      := ' || :NEW.ID || ';
      
        mail_client.connect_server( p_hostname => ''email_server_path'', p_port => 993, p_protocol => mail_client.protocol_IMAP, p_userid => ''domain\username'', p_passwd => ''password'', p_ssl => true );
        mail_client.open_inbox;
        mail_client.Open_Folder(v_current_path);
        SELECT msg_number
        INTO v_msg_id
        FROM TABLE(mail_client.get_mail_headers_p())
              WHERE (TO_DATE(v_received_time, ''DD-MON-YYYY HH24:MI:SS'') - sent_date) *24*60*60 between 0 and 2
        AND SENDER                                           = v_requester;
        v_msg                                               := mail_client.get_message(v_msg_id);
        v_msg.move_message(v_new_path);
        mail_client.expunge_folder;
        mail_client.Close_Folder();
        mail_client.disconnect_server;
      END;';
        dbms_job.submit(jobno, submit_code , sysdate,NULL);
       --commit;
       --DBMS_JOB.RUN(jobno);
      :NEW.CURRENT_PATH:= :NEW.NEW_PATH;
      :NEW.NEW_PATH := Null;
      end if;
      END;
      end;
      and a sample of the what of a trigger is:
      DECLARE
        v_msg mail_t;
        v_msg_id        NUMBER;
        v_username      VARCHAR2(1000);
        v_current_path  VARCHAR2(1000);
        v_new_path      VARCHAR2(1000);
        v_requester     VARCHAR(1000);
        v_received_time VARCHAR(1000);
        v_request_id       number;
        v_submit_code varchar(4000);
        v_error_code number;
        v_error_message varchar2(255);
      BEGIN
        v_username      := 'USER';
          v_current_path  := 'current_path';
        v_new_path      := 'new_path';
        v_requester     := 'requester_email';
        v_received_time := '29-NOV-2011 14:07:57';
        v_request_id      := 37565;
      
        mail_client.connect_server( p_hostname => 'email_server', p_port => 993, p_protocol => mail_client.protocol_IMAP, p_userid => 'username', p_passwd => 'password', p_ssl => true );
        mail_client.open_inbox;
        mail_client.Open_Folder(v_current_path);
        SELECT msg_number
        INTO v_msg_id
        FROM TABLE(mail_client.get_mail_headers_p())
              WHERE (TO_DATE(v_received_time, 'DD-MON-YYYY HH24:MI:SS') - sent_date) *24*60*60 between 0 and 2
        AND SENDER                                           = v_requester;
        v_msg                                               := mail_client.get_message(v_msg_id);
        v_msg.move_message(v_new_path);
        mail_client.expunge_folder;
        mail_client.Close_Folder();
        mail_client.disconnect_server;
      END;
      Only thing in my mind is that i'm editing the data on APEX 4.1, so when i look at the job on SQL Developer i find the following info:
      *
      Log_User = ANONYMOUS
      PRIVILEGE USER = IBG(CORRECT)
      SCHEMA_USER = IBG(CORRECT)
      *
      does the log_user = anonymous effect the procedure in any way? and if so how can i fix this?

      Last thing is the error i found on the OEM(Alerts Log):
      Errors in file FILE_PATH: ORA-12012: error on auto execute of job 422 ORA-01403: no data found ORA-06512: at line 24  
      any reason why this issue is happening, and what should i do to fix it?

      Edited by: BrainWashed on Dec 1, 2011 4:51 AM -- Fixing Bold Tags
        • 1. Re: dbms_job.submit strange behavior
          spajdy
          No_DATA_FOUND exception is raised when SELECT INTO FROM SQL statement not found any data.
          I you code I see
          SELECT msg_number
          INTO v_msg_id
          FROM TABLE(mail_client.get_mail_headers_p())
          WHERE (TO_DATE(v_received_time, 'DD-MON-YYYY HH24:MI:SS') - sent_date) *24*60*60 between 0 and 2
          AND SENDER = v_requester;

          Keep in mind that job is run in new session. So question is what return this function mail_client.get_mail_headers_p() after new session is created.
          • 2. Re: dbms_job.submit strange behavior
            704302
            Thank you for your response.
            The reason why i said it's a strange behavior, is if i issue a Run command for that particular job(which was already errored with no data found.) the job runs successfully.
            so it doesn't find data if the command is ran through the submit from a trigger, but there's no problem if i submit it or run it by issuing the command through SQL developer or SQL Command line in APEX SQL Workshop.