Cursor Loop Problem

From: Mark A Long <mark.a.long_at_boeing.com>
Date: Thu, 28 Oct 1999 15:40:49 GMT
Message-ID: <38186E81.B44C210B@boeing.com>

I am having a problem with a cursor loop. It appears to be accomplishing it's task but I after the updates are made the program hangs. I using a cursor loop to update because I have been exceeding roll back segments. The reason I am updating the date field in the table is because the data has a lot of garbage in it. Any help you could give me would be appreciated. As you can tell I am rookie to cursor loops.

CREATE OR REPLACE PROCEDURE RTM_PR_LOAD_ADDMOD_DATES IS

counter		Number;
commit_me	Number;

v_date_time_tr_ch		rtm.rtm_ta_tims_ptss_trans.date_time_tr_ch%type;
v_date_tr			rtm.rtm_ta_tims_ptss_trans.date_tr%type;
v_date_time_tr			rtm.rtm_ta_tims_ptss_trans.date_time_tr%type;

CURSOR c_ptsstrans IS
	SELECT
	  date_time_tr_ch,
	  date_tr
	FROM
	  rtm.rtm_ta_tims_ptss_trans;

BEGIN counter := 1;
commit_me := 1000;

open c_ptsstrans;
LOOP

	FETCH c_ptsstrans INTO
	      v_date_time_tr_ch,
	      v_date_tr;

	EXIT WHEN c_ptsstrans%NOTFOUND;	



/* ----------------------------*/

/* Update the ptss_trans table */
/* ----------------------------*/

/* Update the DATE_TIME_TR from the DATE_TIME_TR_CH field */

        IF to_number(substr(v_date_time_tr_ch,10,2)) < 24 AND v_date_tr IS NOT NULL         THEN v_date_time_tr := to_date(v_date_time_tr_ch,'YYYYMMDD:HH24MISS');

        ELSE v_date_time_tr := NULL;

        END IF;

	Update rtm.rtm_ta_tims_ptss_trans set date_time_tr =
		v_date_time_tr;



/* ----------------------------*/

/* Commit after 1000 records */
/* ----------------------------*/

        IF counter >= commit_me

        THEN

	  COMMIT;
  	  counter := 1;
	  DBMS.OUTPUT(v_date_time_tr_ch, v_date_tr, v_date_time_tr);

	ELSE

  	  counter := counter + 1;

	END IF;

END LOOP; END; Here is a description of the table.

Column Name                    Null?    Type
------------------------------ -------- ----
SITE_ID                        NOT NULL VARCHAR2(8)
DATE_TIME_TR                            DATE
DATE_TR                                 VARCHAR2(8)
DATE_TIME_TR_CH                         VARCHAR2(15)
TR                                      VARCHAR2(8)
MATERIAL_CODE                  NOT NULL VARCHAR2(16)
SSN                            NOT NULL CHAR(12)
PART_NO                                 VARCHAR2(24)
CODE_MC                                 CHAR(2)
CODE_TRANS                              CHAR(2)
DOC_NO                                  VARCHAR2(10)
ACCT_CHRG_NO                            VARCHAR2(16)
ORG_NO                                  VARCHAR2(6)
TRANS_VALUE                             NUMBER(11,2)
FLAG_PDET_TERM                          CHAR(2)
CODE_CONTROL                            CHAR(4)
TYPE_RECORD                             CHAR(2)
QTY_ORIGINAL                            NUMBER(11)
QTY_TRANS                               NUMBER(11)
AVG_UNIT_PRICE                          NUMBER(11,4)
REMARKS                                 VARCHAR2(30)
LAST_USER                               VARCHAR2(8)
PROGRAM_NAME                            VARCHAR2(8)

Thanks

Mark Long
Business Systems Programmer
Boeing Company
(253)931-3119 Received on Thu Oct 28 1999 - 10:40:49 CDT