One of the procedures that am working on is failing with : normal, successful completion error and need some help troubleshooting this issue. I am PL?SQL newbie and unable to identify where exactly the proedure is failing.
Here is the error log from the procedure run:
ORA-06512: at Check_status_done, line 40
ORA-06512: at line 3 (DBD ERROR: OCIStmtExecute) at ./runsql line 89
DBD::Oracle::st execute failed: ORA-20001:Job Name: STATUS = DONE ERR: COMMENT = Error happened in processig records ORA-xxxx: normal, successful completion
ORA-06512: at "check_status_done", line 40
ORA-06512: at line 3 (DBD ERROR: OCIStmtExecute) at ./runsql line 89.
create or replace PROCEDURE proc1 AS
CURSOR c1 IS
SELECT DISTINCT col1 as col1_temp
,col2 as col2_temp
,col3
,col4
FROM tab1
WHERE process_dt IS NULL
AND col5 = 3
AND nvl(col4,'C') <> 'C'
ORDER by col1_temp, col4;
CURSOR c3(v2 INTEGER) IS
SELECT distinct col6, col7
FROM tab2 WHERE col8 = v2;
v1 INTEGER ;
v2 INTEGER ;
v3 INTEGER ;
v4 INTEGER ;
v5 INTEGER ;
v6 INTEGER;
v7 INTEGER;
v8 INTEGER;
v9 INTEGER;
v10 INTEGER;
error_flag NUMBER := 0; /* 0=Normal
1=Abnormal */
v_name err_log_tbl.name%TYPE := 'Update Order transactions';
v_class err_log_tbl.class_code%TYPE := 'ODS';
v_text err_log_tbl.COMMENT_TEXT%TYPE := null;
v_total INTEGER := 0 ;
BEGIN
v_text := 'Program Just Started';
err_tab( v_name,'Initial',v_text ,v_class);
FOR c1_rec IN c1 LOOP
if c1_rec.col4 = 'I' then
SELECT col2_temp
INTO v2
FROM tab3
WHERE col3 = c1_rec.col3
AND col1_temp = c1_rec.col1_temp
;
v1 := c1_rec.col2_temp;
UPDATE tab3
SET col2_temp = c1_rec.col2_temp
WHERE col3 = c1_rec.col3
AND col1_temp = c1_rec.col1_temp
;
if SQL%rowcount != 1 then
v_text := 'Error in Updating tab3 record ' || to_char(c1_rec.col1_temp) || ' ' || SQLERRM;
error_flag := 1;
exit;
end if;
pkg1.p1(v2
,v1
,error_flag
,v_text);
IF error_flag > 0 THEN
exit;
END IF;
BEGIN
v5:= 0;
FOR c3_rec IN c3(v2) LOOP
BEGIN
SELECT tab2_id
INTO v4
FROM tab2
WHERE p_id = c3_rec.col6
AND col2_temp = v1
;
EXCEPTION when no_data_found then
v4 := null;
END;
IF v4 is not null THEN
tab2_pkg.del_from_tab2(c3_rec.tab2_id
,v4
,error_flag
,v_text);
if error_flag <> 0 then
exit;
end if;
ELSIF v4 is null THEN
v6 := tab2_pkg.v6_fac (v1
,v2
,c3_rec.tab2_id
,error_flag
,v_text);
IF error_flag <> 0 then
exit;
END IF;
if v6 > 0 then
UPDATE tab_cl tc
SET tab2_ver_id = (SELECT tab2_ver_id
FROM tab2_VER
WHERE tab2_id = c3_rec.tab2_id
AND greatest(tc.beg_dt,to_date('12-DEC-99'))
BETWEEN tab2_ver_beg_dt AND tab2_ver_end_dt
)
WHERE tab2_ver_id in (SELECT tab2_ver_id
FROM tab2_VER
WHERE tab2_id = c3_rec.tab2_id
)
;
end if;
tab2_pkg.p1(c3_rec.tab2_id
,v2
,v1
,error_flag
,v_text)
;
IF error_flag <> 0 then
exit;
END IF;
END IF; /* End of IF v4 is not null */
v5:= v5+ 1 ;
END LOOP;
END ;
if error_flag <> 0 then
exit;
end if;
DELETE tab4 WHERE col2_temp = v2;
DELETE tab6 WHERE col2_temp = v2;
if SQL%rowcount != 1 then
v_text := 'Error in deleting record ' || to_char(v2) || ' ' || SQLERRM;
error_flag := 1;
exit;
end if;
UPDATE tab1
SET PROCESS_DT = trunc(sysdate)
,del_col2 = v2
WHERE col1 = c1_rec.col1_temp
AND col2 = c1_rec.col2_temp
AND col3 = c1_rec.col3
AND col5 = 3
AND col4 = 'A';
if SQL%rowcount != 1 then
v_text := 'Error Found in Update'|| to_char(c1_rec.col1_temp) || ' ' || SQLERRM;
error_flag := 1;
exit;
end if;
elsif c1_rec.col4 = 'R' then
UPDATE tab1
SET PROCESS_DT = trunc(sysdate)
WHERE col1 = c1_rec.col1_temp
AND col2 = c1_rec.col2_temp
AND col3 = c1_rec.col3
AND col5 = 3
AND col4 = 'R';
if SQL%rowcount != 1 then
v_text := 'Error Found in Update'|| to_char(c1_rec.col1_temp) || ' ' || SQLERRM;
error_flag := 1;
exit;
end if;
UPDATE tab6
SET fac_temp_flag ='N'
WHERE col2_temp in (SELECT col2_temp
FROM tab3
WHERE col1_temp = c1_rec.col1_temp
AND col3 = c1_rec.col3
)
;
end if;
if error_flag = 1 then
exit;
end if;
v_total := v_total + 1;
IF mod(v_total,100) = 0 then
v_text := 'Processed ' || to_char(v_total) || ' succesfully ' ;
err_tab(v_name ,'Running' ,substr(v_text,1,120) ,v_class);
commit;
END IF;
END LOOP;
if error_flag = 0 then
commit ;
v_text := 'Complete Processing of ' || to_char(v_total) || ' succesfully ' ;
err_tab(v_name ,'Done OK' ,substr(v_text,1,120) ,v_class);
else
rollback;
v_text := 'Error Happened in processing records' || SQLERRM ;
err_tab(v_name ,'Done Err' ,substr(v_text,1,120) ,v_class);
end if;
EXCEPTION when OTHERS then
rollback;
v_text := v_text || SQLERRM;
err_tab(v_name ,'Done Err' ,substr(v_text,1,120) ,v_class);
END;
Any help is greatly appreciated.
Thanks.
Santhosh