Script freezes before first line

From: Joe Mellors <joemellors_at_aol.com>
Date: 19 Mar 2002 15:26:57 -0800
Message-ID: <53566469.0203191526.26dfb648_at_posting.google.com>

Hiya,

If anyone can help it would be greatly appreciated. I have created a script shown below to populate a table (tblprojipptable) according to some rules. It has autocommit on and inserts into a table (tbldebug) intending to inform me of the scripts progress as I predicted it would take quite some time as the datasets involved are very large. I started the script running in sqlplus on a floppy on the server but it seems to have frozen without even getting to line 1 because the insert statement immediately after begin is not in the table. Here is the script. Thanks in advance, Joe

SET AUTOCOMMIT ON DECLARE CURSOR GET_PROJECTS IS

	SELECT PROJID
	FROM APACR.TBLINTPROJECT
	WHERE PDC = 'I';

PROJ_DATA GET_PROJECTS%ROWTYPE; CURSOR GET_IPPS (PPROJID APACR.TBLPROJECTIPPN.PROJID%TYPE) IS

	SELECT IPP, IS_MASTER
	FROM APACR.TBLPROJECTIPPN
	WHERE PROJID = PPROJID;

IPP_DATA GET_IPPS%ROWTYPE; CURSOR PART_CHANGES (PPROJID APACR.TBLLOCATIONS.PROJID%TYPE, PIPP APACR.TBLLOCATIONS.IPP%TYPE) IS

	SELECT DISTINCT A.PNR, A.MFC, B.PICKUPMKEY, B.SEQNO, B.DATESTAMP
	FROM APACR.STBLPROJECTPARTS B, APACR.TBLLOCATIONS A
	WHERE B.MFC = A.MFC
	AND B.PNR = A.PNR
	AND B.PROJID = A.PROJID
	AND B.CHG = 'R'
	AND A.IPP = PIPP
	AND A.PROJID = PPROJID;

PART_DATA PART_CHANGES%ROWTYPE; MINDATE DATE; CURSOR LATER_THAN_CHANGE (PPROJID APACR.STBLOUTMSGHEADER.PROJID%TYPE, PIPP APACR.STBLOUTMSGHEADER.IPP%TYPE) IS         SELECT A.MKEY, A.NEW_ISS FROM APACR.STBLOUTMSGHEADER A, APACR.TBLOUTMSGHEADER B

	WHERE B.MKEY = A.MKEY
	AND B.PROJID = A.PROJID
	AND B.IPP = A.IPP
	AND B.STATUS <> 'REJECTED'
	AND A.DATESTAMP >= MINDATE
	AND A.OLD_STATUS IS NULL
	AND A.NEW_STATUS = 'EXTRACTED'
	AND A.IPP = PIPP
	AND A.PROJID = PPROJID
	ORDER BY A.DATESTAMP;

LATER_DATA LATER_THAN_CHANGE%ROWTYPE; VPDC CHAR(1);
ICOUNT NUMBER(6);
HAPPY BOOLEAN;
MINMKEY NUMBER(7);
ROW_INSERTED BOOLEAN; BEGIN
        INSERT INTO APACR.TBLDEBUG VALUES ('STARTING FILLTABLE SCRIPT', TO_CHAR(SYSDATE, 'DD/MM/YYYY HH24:MI:SS'));         

	FOR PROJ_DATA IN GET_PROJECTS LOOP
		FOR IPP_DATA IN GET_IPPS(PROJ_DATA.PROJID) LOOP
			-- MINMKEY IS THE EARLIEST MESSAGE WE ARE INTERESTED IN SHOWING
PART CHANGES FOR IN TBLPROJIPPNTABLE
			-- THIS IS BECAUSE ENTRIES FOR AFTER ANY MESSAGE THAT CAN STILL BE

REJECTED ARE NEEDED                         

			SELECT MIN(MKEY) INTO MINMKEY FROM APACR.TBLOUTMSGHEADER
			WHERE ((STATUS = 'EXTRACTED') OR (STATUS = 'SENT'))
			AND IPP = IPP_DATA.IPP
			AND PROJID = PROJ_DATA.PROJID;
			
			INSERT INTO APACR.TBLDEBUG VALUES ('SELECTED MINMKEY',

TO_CHAR(SYSDATE, 'DD/MM/YYYY HH24:MI:SS'));                         

			IF IPP_DATA.IS_MASTER = 'Y' THEN
				FOR PART_DATA IN PART_CHANGES(PROJ_DATA.PROJID, IPP_DATA.IPP) LOOP
					


					-- IF MKEY FROM STBLPROJECTPARTS APPLIES TO THIS IPP WE CAN USE


IT


					-- OTHERWISE WE HAVE TO COMPARE STBLPROJECTPARTS AND


STBLOUTMSGHEADER DATESTAMPS


					-- IF MKEY IN STBLPROJECTPARTS IS NULL AND THE PART DOESN'T EXIST

IN OTHER IPPS WE CAN ENTER A NULL RECORD                                         

					HAPPY := TRUE;
					IF PART_DATA.PICKUPMKEY IS NOT NULL THEN
						SELECT COUNT(*) INTO ICOUNT FROM APACR.TBLOUTMSGHEADER
						WHERE MKEY = PART_DATA.PICKUPMKEY
						AND IPP = IPP_DATA.IPP
						AND PROJID = PROJ_DATA.PROJID;
						
						INSERT INTO APACR.TBLDEBUG VALUES ('CHECKED TO SEE IF MKEY

BELONGS TO IPP', TO_CHAR(SYSDATE, 'DD/MM/YYYY HH24:MI:SS'));                                                 

						IF ICOUNT > 0 THEN
							IF PART_DATA.PICKUPMKEY >= MINMKEY THEN
								BEGIN
									SELECT 1 INTO ICOUNT FROM DUAL WHERE EXISTS
									(SELECT * FROM APACR.TBLPROJIPPTABLE
									WHERE PICKUPMKEY = PART_DATA.PICKUPMKEY
									AND MFC = PART_DATA.MFC
									AND PNR = PART_DATA.PNR
									AND IPP = IPP_DATA.IPP
									AND PROJID = PROJ_DATA.PROJID);
								EXCEPTION
									WHEN NO_DATA_FOUND THEN
									ICOUNT := 0;
								END;
								
								IF ICOUNT = 0 THEN
									INSERT INTO APACR.TBLPROJIPPTABLE
									VALUES (PROJ_DATA.PROJID, IPP_DATA.IPP, PART_DATA.PNR,
									PART_DATA.MFC, PART_DATA.SEQNO, PART_DATA.PICKUPMKEY);
								END IF;
							END IF;
						ELSE
							HAPPY := FALSE;
						END IF;
						
					ELSE
						SELECT COUNT(*) INTO ICOUNT FROM APACR.TBLLOCATIONS A,
APACR.TBLPROJECTIPPN B
						WHERE B.IS_MASTER <> 'Y'
						AND A.PROJID = B.PROJID
						AND A.IPP = B.IPP
						AND A.MFC = PART_DATA.MFC
						AND A.PNR = PART_DATA.PNR
						AND A.IPP <> IPP_DATA.IPP
						AND A.PROJID = PROJ_DATA.PROJID;
					
						INSERT INTO APACR.TBLDEBUG VALUES ('COUNTED JOIN LOCATIONS

PROJECTIPPN', TO_CHAR(SYSDATE, 'DD/MM/YYYY HH24:MI:SS'));                                                 

						IF ICOUNT = 0 THEN
							BEGIN
								SELECT 1 INTO ICOUNT FROM DUAL WHERE EXISTS
								(SELECT * FROM APACR.TBLPROJIPPTABLE
								WHERE PICKUPMKEY IS NULL
								AND MFC = PART_DATA.MFC
								AND PNR = PART_DATA.PNR
								AND IPP = IPP_DATA.IPP
								AND PROJID = PROJ_DATA.PROJID);
							EXCEPTION
								WHEN NO_DATA_FOUND THEN
									ICOUNT := 0;
							END;
							
							IF ICOUNT = 0 THEN
								INSERT INTO APACR.TBLPROJIPPTABLE
								VALUES (PROJ_DATA.PROJID, IPP_DATA.IPP, PART_DATA.PNR,
								PART_DATA.MFC, PART_DATA.SEQNO, PART_DATA.PICKUPMKEY);
							END IF;
						ELSE
							HAPPY := FALSE;
						END IF;
					END IF;
					
					IF NOT(HAPPY) THEN
						SELECT MIN(DATESTAMP) INTO MINDATE FROM APACR.STBLOUTMSGHEADER
						WHERE DATESTAMP > PART_DATA.DATESTAMP
						AND OLD_STATUS IS NULL
						AND NEW_STATUS = 'EXTRACTED'
						AND IPP = IPP_DATA.IPP
						AND PROJID = PROJ_DATA.PROJID;
						
						INSERT INTO APACR.TBLDEBUG VALUES ('FOUND DATE OF EXTRACTION',

TO_CHAR(SYSDATE, 'DD/MM/YYYY HH24:MI:SS'));                                                 

						IF MINDATE IS NULL THEN
							BEGIN
								SELECT 1 INTO ICOUNT FROM DUAL WHERE EXISTS
								(SELECT * FROM APACR.TBLPROJIPPTABLE
								WHERE PICKUPMKEY IS NULL
								AND MFC = PART_DATA.MFC
								AND PNR = PART_DATA.PNR
								AND IPP = IPP_DATA.IPP
								AND PROJID = PROJ_DATA.PROJID);
							EXCEPTION	
								WHEN NO_DATA_FOUND THEN
									ICOUNT := 0;
							END;
							
							IF ICOUNT = 0 THEN
								INSERT INTO APACR.TBLPROJIPPTABLE
								VALUES (PROJ_DATA.PROJID, IPP_DATA.IPP, PART_DATA.PNR,
								PART_DATA.MFC, PART_DATA.SEQNO, NULL);
							END IF;
						ELSE
							ROW_INSERTED := FALSE;
							FOR LATER_DATA IN LATER_THAN_CHANGE(PROJ_DATA.PROJID,
IPP_DATA.IPP) LOOP
								IF SUBSTR(LATER_DATA.NEW_ISS, 1, 1) = 'M' THEN
									IF LATER_DATA.MKEY >= MINMKEY THEN
										BEGIN
											SELECT 1 INTO ICOUNT FROM DUAL WHERE EXISTS
											(SELECT * FROM APACR.TBLPROJIPPTABLE
											WHERE PICKUPMKEY = LATER_DATA.MKEY
											AND MFC = PART_DATA.MFC
											AND PNR = PART_DATA.PNR
											AND IPP = IPP_DATA.IPP
											AND PROJID = PROJ_DATA.PROJID);
										EXCEPTION
											WHEN NO_DATA_FOUND THEN
											ICOUNT := 0;
										END;
									
										IF ICOUNT = 0 THEN
											INSERT INTO APACR.TBLPROJIPPTABLE
											VALUES (PROJ_DATA.PROJID, IPP_DATA.IPP, PART_DATA.PNR,
											PART_DATA.MFC, PART_DATA.SEQNO, LATER_DATA.MKEY);
										END IF;
									
										ROW_INSERTED := TRUE;
									
										EXIT;
									END IF;
								END IF;
							END LOOP;
							
							IF NOT(ROW_INSERTED) THEN
								BEGIN
									SELECT 1 INTO ICOUNT FROM DUAL WHERE EXISTS
									(SELECT * FROM APACR.TBLPROJIPPTABLE
									WHERE PICKUPMKEY IS NULL
									AND MFC = PART_DATA.MFC
									AND PNR = PART_DATA.PNR
									AND IPP = IPP_DATA.IPP
									AND PROJID = PROJ_DATA.PROJID);
								EXCEPTION
									WHEN NO_DATA_FOUND THEN
									ICOUNT := 0;
								END;
																	
								IF ICOUNT = 0 THEN
									INSERT INTO APACR.TBLPROJIPPTABLE
									VALUES (PROJ_DATA.PROJID, IPP_DATA.IPP, PART_DATA.PNR,
									PART_DATA.MFC, PART_DATA.SEQNO, NULL);
								END IF;
							END IF;
						END IF;			
					END IF;
				END LOOP;
			ELSE
				-- IF THE IPP IS STILL AT DRAFT THEN ALL PART CHANGES CAN BE

ENTERED WITH MKEY OF NULL                                 

				FOR PART_DATA IN PART_CHANGES(PROJ_DATA.PROJID, IPP_DATA.IPP) LOOP
					BEGIN
						SELECT 1 INTO ICOUNT FROM DUAL WHERE EXISTS
						(SELECT * FROM APACR.TBLPROJIPPTABLE
						WHERE PICKUPMKEY IS NULL
						AND MFC = PART_DATA.MFC
						AND PNR = PART_DATA.PNR
						AND IPP = IPP_DATA.IPP
						AND PROJID = PROJ_DATA.PROJID);
					EXCEPTION
						WHEN NO_DATA_FOUND THEN
						ICOUNT := 0;
					END;
																						
					IF ICOUNT = 0 THEN
						INSERT INTO APACR.TBLPROJIPPTABLE
						VALUES (PROJ_DATA.PROJID, IPP_DATA.IPP, PART_DATA.PNR,
						PART_DATA.MFC, PART_DATA.SEQNO, NULL);
					END IF;
				END LOOP;
			END IF;
			INSERT INTO APACR.TBLDEBUG VALUES ('FINISHED IPP ' || IPP_DATA.IPP
|| PROJ_DATA.PROJID, TO_CHAR(SYSDATE, 'DD/MM/YYYY HH24:MI:SS'));
		END LOOP;
	END LOOP;

END; /
INSERT INTO APACR.TBLDEBUG VALUES ('FINISHED FILLTABLE', TO_CHAR(SYSDATE, 'DD/MM/YYYY HH24:MI:SS')); SET AUTOCOMMIT OFF Received on Wed Mar 20 2002 - 00:26:57 CET