job is done, but data does not show up in table for 1 hour

From: <utefan001_at_gmail.com>
Date: 13 Jul 2005 19:09:59 -0700
Message-ID: <1121306999.862142.294240@f14g2000cwb.googlegroups.com>

I am trying to figure out why all indications show the job is done, but the blasted data does not show in its_jobcost for 1 to 3 hours! I don't see any locks on the table or suspect sessions. Server load is very light. I have a job that uses 10 cursors to do one insert and 9 updates to a table. Not my code, but I am now resposible for it.

I select * from dba_jobs and get the following results (summary) job =366

last_date   =7/13/2005  8:00:02 AM
last_sec    =08:00:02
next_date   =7/14/2005  8:00:00 AM
next_sec    =08:00:02
total_time  =523
broken      =N

failures =0

A subset of the code is below
CREATE OR REPLACE PROCEDURE its_jc IS

  M_COMMIT_CNT  NUMBER;
  M_CURSOR_ID   INTEGER;
  M_ROWS_PROC   INTEGER;
  M_REC_COUNT   INTEGER;

CURSOR material IS

    SELECT act.project_id,
           act.task_id,
           cal.fiscal_year,
           cal.period,
           SUM(act.cost_amt) AMOUNT
      FROM gprod.je_sub js,
           gprod.je_det jd,
           gprod.pmxject xref,
           gprod.pmctact act,
           calendar cal
     WHERE js.je_ccn = '29'
       AND js.je_ccn = jd.je_ccn
       AND js.je = jd.je
       AND js.je_sub = jd.je_sub
       AND jd.je_ccn = xref.je_ccn
       AND jd.je = xref.je
       AND jd.je_sub = xref.je_sub
       AND jd.je_line = xref.je_line
       AND xref.ccn = act.ccn
       AND xref.project_id = act.project_id
       AND xref.task_id = act.task_id
       AND xref.pmctact_seq = act.pmctact_seq
       AND js.asof_date BETWEEN cal.beg_date
                            AND cal.end_date
       AND act.application = 'MM'
       AND SUBSTR(act.gl_acct, 1, 6) IN
('114000','114100','114102','114200','114321','114322','114325','114330','114331','114370','999994')
     GROUP BY act.project_id,
              act.task_id,
              cal.fiscal_year,
              cal.period;

/* nine more cursors */

/* Transfer Material Costs */

    M_COMMIT_CNT := 0;
    FOR mat_rec IN material LOOP

      /* Attempt to Locate Current Record */
      SELECT COUNT(*) INTO M_REC_COUNT
        FROM its_jobcost_stage
       WHERE project_id = mat_rec.project_id
         AND task_id = mat_rec.task_id
         AND fiscal_year = mat_rec.fiscal_year
         AND period = mat_rec.period;

      /* Does a Record Exist */
      IF M_REC_COUNT <> 0 THEN
        /* Update Current Record */
        UPDATE its_jobcost_stage
           SET mm_recpts = mm_recpts + mat_rec.amount
         WHERE project_id = mat_rec.project_id
           AND task_id = mat_rec.task_id
           AND fiscal_year = mat_rec.fiscal_year
           AND period = mat_rec.period;
      ELSE
        /* Add New Record */
        INSERT INTO its_jobcost_stage
        VALUES(mat_rec.project_id,
               mat_rec.task_id,
               mat_rec.fiscal_year,
               mat_rec.period,
               mat_rec.amount,
               0,
               0,
               0,
               0,
               0,
               0,
               0,
               0,
               0,
               0,
               0,
               0);
      END IF;

      /* Check for Commit */
      IF M_COMMIT_CNT = 100 THEN
        COMMIT;
        M_COMMIT_CNT := 0;
      END IF;

    END LOOP;
    INSERT INTO ITS_JC_TIMELOG VALUES(410,sysdate);     COMMIT; /* nine more insert/updates */

Last few lines of code

/* Delete Data in the User Table */

    M_CURSOR_ID := DBMS_SQL.OPEN_CURSOR;     DBMS_SQL.PARSE(M_CURSOR_ID, 'TRUNCATE TABLE its_jobcost', DBMS_SQL.NATIVE);
    M_ROWS_PROC := DBMS_SQL.EXECUTE(M_CURSOR_ID);     DBMS_SQL.CLOSE_CURSOR(M_CURSOR_ID);
/* Copy to User Table */

    INSERT INTO its_jobcost SELECT * FROM its_jobcost_stage;

/* Indicate Regen Time */

    UPDATE its_last_jc_regen SET regen_date = SYSDATE;     INSERT INTO ITS_JC_TIMELOG VALUES(918,sysdate);     COMMIT;
  END; Note how the 3rd to last line inserts a line number (918) and a timestamp into a log table that I have created. I do this about 10 times throughout the code. The job takes less then 60 seconds to run according to dba_jobs and according to my its_jc_timelog table.

This is 8i on HP Unix.

Thanks for your help. If I left any information out, let me know and I will provide it. It is late and I may have forgotten something, don't burn me at the Proverbial Newsgroup Stake because I missed something.... Received on Wed Jul 13 2005 - 21:09:59 CDT