how to fill errors in background job Text per item in a Custom API as scheduled database task

Hi Alhagmo,

You can try Transaction_Sys.Log_Error__ method. If you know which exception it would throw you can catch it, else just try to wrap this with a normal Exception block. Product-related code doesn’t throw a certain exception type it's usually throwing an exception by Error_Sys.Record_General.

Hi infaz,

thank you for your hint. I have used 2 functions from this Package:

                transaction_sys.Log_Error__(id_,info_);
                Transaction_Sys.Log_Status_Info(info_);

Only the second one filled information into text rows of background job.

Thank you very much for your help.

This is my code:

BEGIN
info_ := '';
FOR rec_ in get_part_no LOOP
      DECLARE
      BEGIN
        id_ := Transaction_Sys.Get_Current_Job_Id();
              
           Client_SYS.Clear_Attr(attr_); 
           Client_SYS.Add_To_Attr('STD_ORDER_SIZE', rec_.NEW_LOTSIZE , attr_);

           IFSAPP.INVENTORY_PART_PLANNING_API.Modify__(info_, rec_.objid, rec_.objversion, attr_, 'DO');
              info_ := 'PART_NO ' || rec_.Part_no || ' old STD_ORDER_SIZE: ' || rec_.STD_ORDER_SIZE_OLD || ' new STD_ORDER_SIZE: ' || rec_.NEW_LOTSIZE || ' ' || SQLERRM;
              Transaction_Sys.Log_Status_Info(info_);
            EXCEPTION
              WHEN OTHERS THEN
              info_ := 'PART_NO ' || rec_.Part_no || ' old STD_ORDER_SIZE: ' || rec_.STD_ORDER_SIZE_OLD || ' new STD_ORDER_SIZE: ' || rec_.NEW_LOTSIZE || ' ' || SQLERRM;
                transaction_sys.Log_Error__(id_,info_);
                Transaction_Sys.Log_Status_Info(info_);
      END;

END LOOP;

Best regards 

Martina

Hi Martina,

My pleasure, I hope you want to stop and not commit any inventory part planning data if an error occurred, else you should try something like an Oracle Savepoint and commit whatever went ok.

Hi @ALHAGMO,

I have done a few changes to your code.
Could you try the below?

BEGIN
   info_ := '';

   FOR rec_ IN get_part_no LOOP
      DECLARE
      BEGIN
         --(+) START
         SAVEPOINT before_change_;
         --(+) FINISH

         id_ := Transaction_SYS.Get_Current_Job_Id();

         Client_SYS.Clear_Attr(attr_);
         Client_SYS.Add_To_Attr('STD_ORDER_SIZE', rec_.new_lotsize, attr_);

         IFSAPP.INVENTORY_PART_PLANNING_API.Modify__(info_, rec_.objid, rec_.objversion, attr_, 'DO');

         --(-/+) START
         -- info_ := 'PART_NO ' || rec_.part_no || ' old STD_ORDER_SIZE: ' || rec_.std_order_size_old || ' new STD_ORDER_SIZE: ' || rec_.new_lotsize || ' ' || SQLERRM;
         -- Transaction_SYS.Log_Status_Info(info_);
         info_ := 'PART_NO ' || rec_.part_no || ' old STD_ORDER_SIZE: ' || rec_.std_order_size_old || ' new STD_ORDER_SIZE: ' || rec_.new_lotsize;

         Fnd_Context_SYS.Set_Value('ERROR_FORMATTED_KEY', SUBSTR(info_, 1, 2000));
         Transaction_SYS.Log_Status_Info(SUBSTR('Successful', 1, 2000), 'INFO');
         --(-/+) FINISH
      EXCEPTION
         WHEN OTHERS THEN
            --(-/+) START
            -- info_ := 'PART_NO ' || rec_.part_no || ' old STD_ORDER_SIZE: ' || rec_.std_order_size_old || ' new STD_ORDER_SIZE: ' || rec_.new_lotsize || ' ' || SQLERRM;
            -- transaction_SYS.Log_Error__(id_, info_);
            -- Transaction_SYS.Log_Status_Info(info_);
            ROLLBACK TO before_change_;

            info_ := 'PART_NO ' || rec_.part_no || ' old STD_ORDER_SIZE: ' || rec_.std_order_size_old || ' new STD_ORDER_SIZE: ' || rec_.new_lotsize;

            Fnd_Context_SYS.Set_Value('ERROR_FORMATTED_KEY', SUBSTR(info_, 1, 2000));
            Transaction_SYS.Log_Status_Info(SUBSTR(SQLERRM, 1, 2000), 'WARNING');
            --(-/+) FINISH
      END;

      --(+) START
      COMMIT;
      --(+) FINISH
   END LOOP;
END;

Cheers !
Dhananjaya.

Hi Dhananjaya,

thank you very much for your proposals!

It works great! 

Hi @ALHAGMO,

You are welcome and I'm happy to help 😊

Cheers !
Dhananjaya.

The column TRANSACTION_SYS_STATUS_TAB.TEXT is limited to 2000 characters, but sometimes you might want to write a longer status text in multiple pieces, like for a really big attr_ string.

You might not also know whether your code is being run as a background job, interactively in SQL*Plus or PL/SQL Developer for debugging, or inside of the IFS Enterprise Explorer application.

I wrote my own general-purpose trace function to handle all these use cases. When it breaks apart a long status, it attempts to wrap by word.

PROCEDURE trace__(
   text_        IN CLOB,
   status_type_ IN VARCHAR2 DEFAULT 'INFO')
IS
   max_length_                PLS_INTEGER;
   piece_length_              PLS_INTEGER;
   remaining_length_          PLS_INTEGER;
   position_of_next_space_    PLS_INTEGER;
   position_of_next_lf_       PLS_INTEGER;
   position_of_next_start_    PLS_INTEGER;
   sid_                       NUMBER;
   prefix_                    VARCHAR2(32767);
   program_                   VARCHAR2(32767);
   text_buffer_               CLOB;
   reversed_buffer_           VARCHAR2(32767);
   first_space_in_rev_buffer_ PLS_INTEGER;
   next_text_                 CLOB;
   iteration_count_           PLS_INTEGER := 0;
   timestamp_                 TIMESTAMP(9) := SYSTIMESTAMP;
BEGIN
   timestamp_ := SYSTIMESTAMP;
   prefix_ := TO_CHAR(timestamp_, 'YYYY-MM-DD HH24:MI:SS.FF') || ':';
   max_length_ := 200;

   text_buffer_ := prefix_ || text_;
   text_buffer_ := REPLACE(text_buffer_, CHR(13), CHR(10));
   text_buffer_ := REPLACE(text_buffer_, CHR(10) || CHR(10), CHR(10));
   text_buffer_ := REPLACE(text_buffer_, CHR(10) || CHR(10), CHR(10));
   text_buffer_ := RTRIM(text_buffer_, CHR(10));
   text_buffer_ := LTRIM(text_buffer_, CHR(10));
   text_buffer_ := TRIM(text_buffer_);
   LOOP
      iteration_count_ := iteration_count_ + 1;
      EXIT WHEN iteration_count_ > max_iteration_count_;

      remaining_length_ := DBMS_LOB.GETLENGTH(text_buffer_);

      position_of_next_lf_ := DBMS_LOB.INSTR(text_buffer_, CHR(10), 1);
      IF remaining_length_ < max_length_ AND position_of_next_lf_ = 0 THEN
         next_text_ := NULL;
      ELSE
         IF position_of_next_lf_ > 0 AND position_of_next_lf_ < max_length_ THEN
            piece_length_ := position_of_next_lf_ - 1;
            position_of_next_start_ := position_of_next_lf_ + 1;
            next_text_ := SUBSTR(text_buffer_, position_of_next_start_);
            text_buffer_ := SUBSTR(text_buffer_, 1, piece_length_);
         ELSE
            piece_length_ := max_length_;
            position_of_next_start_ := max_length_ + 1;
            next_text_ := SUBSTR(text_buffer_, position_of_next_start_);
            text_buffer_ := SUBSTR(text_buffer_, 1, piece_length_);
            -- attempt to word wrap by searching for a space from the end
            SELECT REVERSE(DBMS_LOB.SUBSTR(text_buffer_, max_length_, 1))
              INTO reversed_buffer_
              FROM DUAL;
            first_space_in_rev_buffer_ := INSTR(reversed_buffer_, ' ');
            IF first_space_in_rev_buffer_ BETWEEN 2 AND 20 THEN
               text_buffer_ := text_buffer_ || next_text_;
               piece_length_ := max_length_ - first_space_in_rev_buffer_;
               position_of_next_start_ := piece_length_ + 2;
               next_text_ := SUBSTR(text_buffer_, position_of_next_start_);
               text_buffer_ := SUBSTR(text_buffer_, 1, piece_length_);
            END IF;
         END IF;
      END IF;

      IF transaction_sys.is_session_deferred THEN
         IF status_type_ IN ('INFO', 'WARNING') THEN
            transaction_sys.log_status_info(
               info_ => text_buffer_,
               status_type_ => status_type_);
         ELSIF status_type_ = 'PROGRESS' THEN
            transaction_sys.set_progress_info(text_buffer_);
         ELSE
            transaction_sys.log_status_info(
               info_ => text_buffer_,
               status_type_ => 'INFO');
         END IF;
      ELSIF USER = 'IFSSYS' THEN
         trace_sys.put_line(text_ => text_buffer_);
      ELSE
         sid_ := SYS_CONTEXT('USERENV', 'SID');
         SELECT program
           INTO program_
           FROM v$session
          WHERE sid = sid_;
         IF    UPPER(program_) LIKE 'SQLPLUS%'
            OR UPPER(program_) LIKE 'PLSQLDEV%'
         THEN
            dbms_output.put_line(text_buffer_);
         END IF;
      END IF;

      EXIT WHEN next_text_ IS NULL;
      timestamp_ := timestamp_ + INTERVAL '0.000000001' SECOND;
      prefix_ := TO_CHAR(timestamp_, 'YYYY-MM-DD HH24:MI:SS.FF') || ':';
      text_buffer_ := prefix_ || next_text_;
   END LOOP;
END trace__;

The column TRANSACTION_SYS_STATUS_TAB.TEXT is limited to 2000 characters, but sometimes you might want to write longer status texts, like for a really big attr_ string.

You might want to output trace information in your code without knowing in advance whether you're executing the code inside SQL*Plus or PL/SQL Developer for debugging, inside IFS Enterprise Explorer, or inside a background job.

I wrote a general-purpose trace function to accomplish these tasks. It attempts to word-wrap a long status line.

PROCEDURE trace__(
   text_        IN CLOB,
   status_type_ IN VARCHAR2 DEFAULT 'INFO')
IS
   max_length_                PLS_INTEGER;
   piece_length_              PLS_INTEGER;
   remaining_length_          PLS_INTEGER;
   position_of_next_space_    PLS_INTEGER;
   position_of_next_lf_       PLS_INTEGER;
   position_of_next_start_    PLS_INTEGER;
   sid_                       NUMBER;
   prefix_                    VARCHAR2(32767);
   program_                   VARCHAR2(32767);
   text_buffer_               CLOB;
   reversed_buffer_           VARCHAR2(32767);
   first_space_in_rev_buffer_ PLS_INTEGER;
   next_text_                 CLOB;
   iteration_count_           PLS_INTEGER := 0;
   timestamp_                 TIMESTAMP(9) := SYSTIMESTAMP;
BEGIN
   timestamp_ := SYSTIMESTAMP;
   prefix_ := TO_CHAR(timestamp_, 'YYYY-MM-DD HH24:MI:SS.FF') || ':';
   max_length_ := 200;

   text_buffer_ := prefix_ || text_;
   text_buffer_ := REPLACE(text_buffer_, CHR(13), CHR(10));
   text_buffer_ := REPLACE(text_buffer_, CHR(10) || CHR(10), CHR(10));
   text_buffer_ := REPLACE(text_buffer_, CHR(10) || CHR(10), CHR(10));
   text_buffer_ := RTRIM(text_buffer_, CHR(10));
   text_buffer_ := LTRIM(text_buffer_, CHR(10));
   text_buffer_ := TRIM(text_buffer_);
   LOOP
      iteration_count_ := iteration_count_ + 1;
      EXIT WHEN iteration_count_ > max_iteration_count_;

      remaining_length_ := DBMS_LOB.GETLENGTH(text_buffer_);

      position_of_next_lf_ := DBMS_LOB.INSTR(text_buffer_, CHR(10), 1);
      IF remaining_length_ < max_length_ AND position_of_next_lf_ = 0 THEN
         next_text_ := NULL;
      ELSE
         IF position_of_next_lf_ > 0 AND position_of_next_lf_ < max_length_ THEN
            piece_length_ := position_of_next_lf_ - 1;
            position_of_next_start_ := position_of_next_lf_ + 1;
            next_text_ := SUBSTR(text_buffer_, position_of_next_start_);
            text_buffer_ := SUBSTR(text_buffer_, 1, piece_length_);
         ELSE
            piece_length_ := max_length_;
            position_of_next_start_ := max_length_ + 1;
            next_text_ := SUBSTR(text_buffer_, position_of_next_start_);
            text_buffer_ := SUBSTR(text_buffer_, 1, piece_length_);
            -- attempt to word wrap by searching for a space from the end
            SELECT REVERSE(DBMS_LOB.SUBSTR(text_buffer_, max_length_, 1))
              INTO reversed_buffer_
              FROM DUAL;
            first_space_in_rev_buffer_ := INSTR(reversed_buffer_, ' ');
            IF first_space_in_rev_buffer_ BETWEEN 2 AND 20 THEN
               text_buffer_ := text_buffer_ || next_text_;
               piece_length_ := max_length_ - first_space_in_rev_buffer_;
               position_of_next_start_ := piece_length_ + 2;
               next_text_ := SUBSTR(text_buffer_, position_of_next_start_);
               text_buffer_ := SUBSTR(text_buffer_, 1, piece_length_);
            END IF;
         END IF;
      END IF;

      IF transaction_sys.is_session_deferred THEN
         IF status_type_ IN ('INFO', 'WARNING') THEN
            transaction_sys.log_status_info(
               info_ => text_buffer_,
               status_type_ => status_type_);
         ELSIF status_type_ = 'PROGRESS' THEN
            transaction_sys.set_progress_info(text_buffer_);
         ELSE
            transaction_sys.log_status_info(
               info_ => text_buffer_,
               status_type_ => 'INFO');
         END IF;
      ELSIF USER = 'IFSSYS' THEN
         trace_sys.put_line(text_ => text_buffer_);
      ELSE
         sid_ := SYS_CONTEXT('USERENV', 'SID');
         SELECT program
           INTO program_
           FROM v$session
          WHERE sid = sid_;
         IF    UPPER(program_) LIKE 'SQLPLUS%'
            OR UPPER(program_) LIKE 'PLSQLDEV%'
         THEN
            dbms_output.put_line(text_buffer_);
         END IF;
      END IF;

      EXIT WHEN next_text_ IS NULL;
      timestamp_ := timestamp_ + INTERVAL '0.000000001' SECOND;
      prefix_ := TO_CHAR(timestamp_, 'YYYY-MM-DD HH24:MI:SS.FF') || ':';
      text_buffer_ := prefix_ || next_text_;
   END LOOP;
END trace__;