5 Replies Latest reply: Aug 2, 2013 4:23 AM by PsmakR RSS

    DBMS Scheduler Job runs but doesn't finish.

    PsmakR

      Hi!

       

      I've created a job which is supposed to run stored procedure. It runs ok but it doesn't look like it's finished...How I know? My procedure logs every action and after running the job I can only see one line of log. The first one from the code...

       

      This is the stored procedure code:

       

      create or replace
      PROCEDURE get_survey_data
      AS
         dir_name         VARCHAR2(100);
         nip_no           VARCHAR2 (1024);
         employee_name    VARCHAR2 (1024);
         company_name     VARCHAR2 (1024);
         hire_date        VARCHAR2 (1024);
         agreement_date   VARCHAR2 (1024);
         stmnt_1          VARCHAR2 (5000);
         stmnt_2          VARCHAR2 (5000);
         t_check          NUMBER;
         s_id             NUMBER;
         ecode            NUMBER;
         emesg            VARCHAR2 (200);
      BEGIN
         DIR_NAME := 'F:\SURVEY_FILES\IN';
         --pobranie ID z sekwencji
         SELECT adecco_apex.survey_job_seq.NEXTVAL
           INTO s_id
           FROM DUAL;
      
      
         INSERT INTO adecco_apex.survey_job_log
                     (ID, start_date, description
                     )
              VALUES (s_id, SYSTIMESTAMP, 'Rozpoczynam Survey JOB'
                     );
                     COMMIT;
      
      
         --SPRAWDZENIE CZY ISTNIEJE TABELA
         SELECT COUNT (*)
           INTO t_check
           FROM all_tables
          WHERE owner = 'ADECCO_APEX' and table_name = 'EXT_IN_INVOICE';
      
      
         IF t_check > 0
         THEN
            EXECUTE IMMEDIATE 'DROP TABLE ADECCO_APEX.EXT_IN_INVOICE';
         END IF;
      
      
         --
         stmnt_1 :=
            'CREATE TABLE ADECCO_APEX.EXT_IN_INVOICE(R NUMBER,FIELD_NAME   VARCHAR2(255 BYTE),FIELD_VALUE  VARCHAR2(255 BYTE)) ORGANIZATION EXTERNAL(  TYPE ORACLE_LOADER DEFAULT DIRECTORY SURVEY_IN ACCESS PARAMETERS ( RECORDS DELIMITED BY NEWLINE CHARACTERSET EE8MSWIN1250 STRING SIZES ARE IN BYTES NOBADFILE NODISCARDFILE NOLOGFILE FIELDS TERMINATED BY '';'' OPTIONALLY ENCLOSED BY ''"'' AND ''"'' NOTRIM MISSING FIELD VALUES ARE NULL ( "R" RECNUM, "FIELD_NAME" CHAR, "FIELD_VALUE" CHAR )) LOCATION (SURVEY_IN:''';
         stmnt_2 := ''')) REJECT LIMIT UNLIMITED NOPARALLEL NOMONITORING';
      
      
         --sprawdzenie czy sa jakies pliki
         SELECT COUNT (*)
           INTO t_check
           FROM (SELECT SUBSTR (ret_val,
                                LENGTH (UPPER (dir_name)) + 2,
                                LENGTH (ret_val)
                               ) file_name
                   FROM TABLE (SYS.ls_directory (UPPER (dir_name)))
                  WHERE UPPER (ret_val) LIKE '%.CSV'
                    AND ret_val NOT LIKE '%OUT_SURVEY.CSV');
      
      
         IF t_check > 1
         THEN
            INSERT INTO adecco_apex.survey_job_log
                        (ID, start_date,
                         description
                        )
                 VALUES (s_id, SYSTIMESTAMP,
                         'Rozpoczynam petle po plikach .csv z folderu IN'
                        );
                        COMMIT;
      
      
            FOR i IN (SELECT SUBSTR (ret_val,
                                     LENGTH (UPPER (dir_name)) + 2,
                                     LENGTH (ret_val)
                                    ) file_name
                        FROM TABLE (SYS.ls_directory (UPPER (dir_name)))
                       WHERE UPPER (ret_val) LIKE '%.CSV'
                         AND ret_val NOT LIKE '%OUT_SURVEY.CSV')
            LOOP
               --DBMS_OUTPUT.ENABLE ();
      
      
               --DBMS_OUTPUT.PUT_LINE(stmnt_1 || i.file_name || stmnt_2);
               BEGIN
                  EXECUTE IMMEDIATE stmnt_1 || i.file_name || stmnt_2;
               EXCEPTION
                  WHEN OTHERS
                  THEN
                     INSERT INTO adecco_apex.survey_job_log
                                 (ID, start_date,
                                  description
                                 )
                          VALUES (s_id, SYSTIMESTAMP,
                                     'Blad podczas tworzenia tabeli: '
                                  || ecode
                                  || '-'
                                  || emesg
                                 );
      
      
                     COMMIT;
               END;
      
      
               INSERT INTO adecco_apex.survey_job_log
                           (ID, start_date, description
                           )
                    VALUES (s_id, SYSTIMESTAMP, 'Czytam plik: ' || i.file_name
                           );
                           COMMIT;
      
      
               EXECUTE IMMEDIATE 'SELECT FIELD_VALUE FROM ADECCO_APEX.EXT_IN_INVOICE WHERE R = 2'
                            INTO company_name;
      
      
               EXECUTE IMMEDIATE 'SELECT FIELD_VALUE FROM ADECCO_APEX.EXT_IN_INVOICE WHERE R = 6'
                            INTO nip_no;
      
      
               EXECUTE IMMEDIATE 'SELECT FIELD_VALUE FROM ADECCO_APEX.EXT_IN_INVOICE WHERE R = 9'
                            INTO employee_name;
      
      
               EXECUTE IMMEDIATE 'SELECT FIELD_VALUE FROM ADECCO_APEX.EXT_IN_INVOICE WHERE R = 11'
                            INTO hire_date;
      
      
               EXECUTE IMMEDIATE 'SELECT FIELD_VALUE FROM ADECCO_APEX.EXT_IN_INVOICE WHERE R = 12'
                            INTO agreement_date;
      
      
               --DBMS_OUTPUT.PUT_LINE(to_date(hire_Date,'YYYY-MM-DD'));
               --DBMS_OUTPUT.PUT_LINE(to_date(agreement_Date,'YYYY-MM-DD'));
               EXECUTE IMMEDIATE 'TRUNCATE TABLE ADECCO_APEX.STG_SURVEY';
      
      
               BEGIN
                  INSERT INTO adecco_apex.stg_survey
                              (nip, employee_name, company_name, hire_date,
                               agreement_date
                              )
                       VALUES (nip_no, employee_name, company_name, hire_date,
                               agreement_date
                              );
                              COMMIT;
               EXCEPTION
                  WHEN OTHERS
                  THEN
                     INSERT INTO adecco_apex.survey_job_log
                                 (ID, start_date,
                                  description
                                 )
                          VALUES (s_id, SYSTIMESTAMP,
                                     'Blad podczas dodawania wierszy do tabeli STG: '
                                  || ecode
                                  || '-'
                                  || emesg
                                 );
      
      
                     COMMIT;
               END;
      
      
               BEGIN
                  MERGE INTO adecco_apex.survey a
                     USING (SELECT nip, employee_name, company_name, hire_date,
                                   agreement_date
                              FROM adecco_apex.stg_survey) b
                     ON (a.nip = b.nip AND a.employee_name = b.employee_name)
                     WHEN MATCHED THEN
                        UPDATE
                           SET a.hire_date = b.hire_date,
                               a.agreement_date = b.agreement_date
                     WHEN NOT MATCHED THEN
                        INSERT (nip, employee_name, company_name, hire_date,
                                agreement_date)
                        VALUES (TO_NUMBER (b.nip), b.employee_name, b.company_name,
                                TO_DATE (b.hire_date, 'YYYY-MM-DD'),
                                TO_DATE (b.agreement_date, 'YYYY-MM-DD'));
                                COMMIT;
               EXCEPTION
                  WHEN OTHERS
                  THEN
                     INSERT INTO adecco_apex.survey_job_log
                                 (ID, start_date,
                                  description
                                 )
                          VALUES (s_id, SYSTIMESTAMP,
                                     'Blad podczas merge do tabeli docelowej: '
                                  || ecode
                                  || '-'
                                  || emesg
                                 );
      
      
                     COMMIT;
               END;
      
      
               EXECUTE IMMEDIATE 'DROP TABLE ADECCO_APEX.EXT_IN_INVOICE';
      
      
               --Wygenerowanie pliku wynikowego
               INSERT INTO adecco_apex.survey_job_log
                           (ID, start_date,
                            description
                           )
                    VALUES (s_id, SYSTIMESTAMP,
                            'Generuje plik wynikowy OUT_SURVEY.CSV'
                           );
                           COMMIT;
      
      
               BEGIN
                  adecco_apex.dump_table_to_csv ('ADECCO_APEX.SURVEY',
                                     'SURVEY_OUT',
                                     'OUT_SURVEY.CSV'
                                    );
               EXCEPTION
                  WHEN OTHERS
                  THEN
                     INSERT INTO adecco_apex.survey_job_log
                                 (ID, start_date,
                                  description
                                 )
                          VALUES (s_id, SYSTIMESTAMP,
                                     'Blad podczas generowania pliku OUT: '
                                  || ecode
                                  || '-'
                                  || emesg
                                 );
                                 COMMIT;
               END;
      
      
               INSERT INTO adecco_apex.survey_job_log
                           (ID, start_date, description
                           )
                    VALUES (s_id, SYSTIMESTAMP, 'Zamykam plik: ' || i.file_name
                           );
                           COMMIT;
                utl_file.fremove('SURVEY_IN', i.file_name);
            END LOOP;
         ELSE
            INSERT INTO adecco_apex.survey_job_log
                        (ID, start_date, description
                        )
                 VALUES (s_id, SYSTIMESTAMP, 'Brak plikow .CSV w folderze IN'
                        );
                        COMMIT;
         END IF;
      
      
         INSERT INTO adecco_apex.survey_job_log
                     (ID, start_date, description
                     )
              VALUES (s_id, SYSTIMESTAMP, 'Koncze Survey JOB'
                     );
                     COMMIT;
      END;
      

       

      What can be wrong?

       

      Help gurus!

       

      Regards,

       

      PsmakR