Mark A Long wrote:
>
> 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)
One of the big problems with your program is that you do not have any
proper exception handling and your code is not structured. If you make both
of those changes you'll have a better chance of detecting your error. One
common problem with doing updates within a loop is the "snapshot too old"
problem, but since you are commiting every 1000 records that should not
occur in your program so there is some other type of problem - but you need
to detect the problem with exception handling.
......................................................
......................................................
......................................................
......................................................
......................................................
......................................................
......................................................
......................................................
......................................................
......................................................
......................................................
......................................................
......................................................
......................................................
......................................................
......................................................
......................................................
......................................................
......................................................
......................................................
......................................................
......................................................
......................................................
......................................................
......................................................
......................................................
......................................................
......................................................
......................................................
......................................................
......................................................
......................................................
......................................................
......................................................
......................................................
......................................................
......................................................
......................................................
......................................................
......................................................
......................................................
......................................................
......................................................
......................................................
......................................................
......................................................
......................................................
......................................................
......................................................
......................................................
......................................................
......................................................
......................................................
......................................................
......................................................
......................................................
......................................................
......................................................
......................................................
......................................................
......................................................
......................................................
......................................................
......................................................
......................................................
......................................................
......................................................
......................................................
......................................................
......................................................
......................................................
......................................................
......................................................
......................................................
......................................................
......................................................
......................................................
......................................................
......................................................
......................................................
......................................................
......................................................
......................................................
......................................................
......................................................
......................................................
......................................................
......................................................
......................................................
......................................................
......................................................
......................................................
......................................................
......................................................
......................................................
......................................................
......................................................
Received on Fri Oct 29 1999 - 07:00:04 CDT
