Have been encountering an odd issue. Every now and again, certain
packages of stored procedures just become invalid. I'm aware that
dropping or altering an underlying table would render a package
invalid, but we are doing no such thing...
After banging my head on the wall for a bit I noticed that the two
stored procedures that are experiencing this behavior are procedures
that are called from within another procedure (they're not both
called, one or the other is called depending on criteria). I also came
across something in the newsgroup referring to issues with nested
procs and packages.
My question: is there a proper approach to implpementing nested stored
procedures as to avoid package becoming invalid?
I'm currently running v8, FixPak 4 on AIX. I've included the SQL of
the stored procedures in question. The first procedure, is the main
one (the caller). The other two are the callees.
Thanks AGAIN
PROCEDURE 1
P1: BEGIN
DECLARE intImageCount INTEGER;
DECLARE strSQLString VARCHAR(256);
DECLARE decArchiveRetrievalID DECIMAL(13,0);
SET decArchiveRetrievalID = (SELECT ArchiveRetrievalID FROM
AIM.AIMRetrievedItem WHERE AIMRetrievedItemID =
decAIMRetrievedItemID);
IF (SELECT ExpirationDate FROM AIM.ArchiveRetrieval WHERE
ArchiveRetrievalID = decArchiveRetrievalID) < DATE(CURRENT TIMESTAMP)
THEN
GOTO NOINSERT;
ELSE IF (SELECT EstimatedResponseTime FROM AIM.ArchiveRetrieval WHERE
ArchiveRetrievalID = decArchiveRetrievalID) > CURRENT TIMESTAMP
AND (SELECT ExpirationDate FROM AIM.ArchiveRetrieval WHERE
ArchiveRetrievalID = decArchiveRetrievalID) > DATE(CURRENT TIMESTAMP)
THEN
UPDATE AIM.AIMRetrievedItem SET
ResultErrorSeverity = intResultErrorSeverity,
ResultErrorType = strResultErrorType,
ResultErrorMessage = strResultErrorMessage,
ImageErrorSeverity = intImageErrorSeverity,
ImageErrorType = strImageErrorType,
ImageErrorMessage = strImageErrorMessage,
ImageFront = clobImageFront,
ImageFrontSize = intImageFrontSize,
ImageFrontType = strImageFrontType,
ImageBack = clobImageBack,
ImageBackSize = intImageBackSize,
ImageBackType = strImageBackType
WHERE decAIMRetrievedItemID = AIMRetrievedItemID;
SET intImageCount = (SELECT COUNT(*) FROM AIM.AIMRetrievedItem WHERE
ArchiveRetrievalID = decArchiveRetrievalID and ImageFront IS NOT
NULL);
IF intImageCount = (SELECT ItemCount FROM AIM.ArchiveRetrieval
WHERE ArchiveRetrievalID = decArchiveRetrievalID)
THEN
CALL AIM.UpdateArchiveRetrieval(decArchiveRetrievalID, 1);
ELSE
CALL AIM.UpdateExpirationDate(decArchiveRetrievalID);
END IF;
ELSE IF
(SELECT EstimatedResponseTime FROM AIM.ArchiveRetrieval WHERE
ArchiveRetrievalID = decArchiveRetrievalID) < CURRENT TIMESTAMP
AND (SELECT ExpirationDate FROM AIM.ArchiveRetrieval WHERE
ArchiveRetrievalID = decArchiveRetrievalID) > DATE(CURRENT TIMESTAMP)
THEN
UPDATE AIM.AIMRetrievedItem SET
ResultErrorSeverity = intResultErrorSeverity,
ResultErrorType = strResultErrorType,
ResultErrorMessage = strResultErrorMessage,
ImageErrorSeverity = intImageErrorSeverity,
ImageErrorType = strImageErrorType,
ImageErrorMessage = strImageErrorMessage,
ImageFront = clobImageFront,
ImageFrontSize = intImageFrontSize,
ImageFrontType = strImageFrontType,
ImageBack = clobImageBack,
ImageBackSize = intImageBackSize,
ImageBackType = strImageBackType
WHERE decAIMRetrievedItemID = AIMRetrievedItemID;
SET intImageCount = (SELECT COUNT(*) FROM AIM.AIMRetrievedItem WHERE
ArchiveRetrievalID = decArchiveRetrievalID and ImageFront IS NOT
NULL);
IF intImageCount = (SELECT ItemCount FROM AIM.ArchiveRetrieval
WHERE ArchiveRetrievalID = decArchiveRetrievalID)
THEN
CALL AIM.UpdateArchiveRetrieval(decArchiveRetrievalID, 1);
ELSE
CALL AIM.UpdateExpirationDate(decArchiveRetrievalID);
END IF;
ELSE IF
(SELECT EstimatedResponseTime FROM AIM.ArchiveRetrieval WHERE
ArchiveRetrievalID = decArchiveRetrievalID) > CURRENT TIMESTAMP
AND (SELECT ExpirationDate FROM AIM.ArchiveRetrieval WHERE
ArchiveRetrievalID = decArchiveRetrievalID) IS NULL THEN
UPDATE AIM.AIMRetrievedItem SET
ResultErrorSeverity = intResultErrorSeverity,
ResultErrorType = strResultErrorType,
ResultErrorMessage = strResultErrorMessage,
ImageErrorSeverity = intImageErrorSeverity,
ImageErrorType = strImageErrorType,
ImageErrorMessage = strImageErrorMessage,
ImageFront = clobImageFront,
ImageFrontSize = intImageFrontSize,
ImageFrontType = strImageFrontType,
ImageBack = clobImageBack,
ImageBackSize = intImageBackSize,
ImageBackType = strImageBackType
WHERE decAIMRetrievedItemID = AIMRetrievedItemID;
SET intImageCount = (SELECT COUNT(*) FROM AIM.AIMRetrievedItem WHERE
ArchiveRetrievalID = decArchiveRetrievalID and ImageFront IS NOT
NULL);
IF intImageCount = (SELECT ItemCount FROM AIM.ArchiveRetrieval
WHERE ArchiveRetrievalID = decArchiveRetrievalID)
THEN
CALL AIM.UpdateArchiveRetrieval(decArchiveRetrievalID, 1);
ELSE
CALL AIM.UpdateExpirationDate(decArchiveRetrievalID);
END IF;
--ELSE
--RETURN 100;
NOINSERT: RETURN 100;
END IF ;
END IF;
END IF;
END IF;
END
PROCEDURE 2
P1: BEGIN
DECLARE dtExpirationDate DATE;
IF
(SELECT
AIM.CHANNEL.CHANNEL
FROM
AIM.AIMCONNECTION,
AIM.CHANNEL,
AIM.CHANNELSESSION,
AIM.AIMQUERY,
AIM.AIMRETRIEVAL,
AIM.ARCHIVERETRIEVAL
WHERE
AIM.CHANNEL.CHANNELID = AIM.CHANNELSESSION.CHANNELID
AND AIM.CHANNELSESSION.CHANNELSESSIONID =
AIM.AIMCONNECTION.CHANNELSESSIONID
AND AIM.AIMCONNECTION.AIMCONNECTIONID =
AIM.AIMQUERY.AIMCONNECTIONID
AND AIM.AIMQUERY.AIMQUERYID = AIM.AIMRETRIEVAL.AIMQUERYID
AND AIM.AIMRETRIEVAL.AIMRETRIEVALID =
AIM.ARCHIVERETRIEVAL.AIMRETRIEVALID
AND AIM.ARCHIVERETRIEVAL.ARCHIVERETRIEVALID =
decArchiveRetrievalID) IN ('RIB') THEN
UPDATE AIM.ArchiveRetrieval SET ExpirationDate = DATE(CURRENT
TIMESTAMP) + 30 DAYS
WHERE ArchiveRetrievalID = decArchiveRetrievalID;
ELSE IF
(SELECT
AIM.CHANNEL.CHANNEL
FROM
AIM.AIMCONNECTION,
AIM.CHANNEL,
AIM.CHANNELSESSION,
AIM.AIMQUERY,
AIM.AIMRETRIEVAL,
AIM.ARCHIVERETRIEVAL
WHERE
AIM.CHANNEL.CHANNELID = AIM.CHANNELSESSION.CHANNELID
AND AIM.CHANNELSESSION.CHANNELSESSIONID =
AIM.AIMCONNECTION.CHANNELSESSIONID
AND AIM.AIMCONNECTION.AIMCONNECTIONID =
AIM.AIMQUERY.AIMCONNECTIONID
AND AIM.AIMQUERY.AIMQUERYID = AIM.AIMRETRIEVAL.AIMQUERYID
AND AIM.AIMRETRIEVAL.AIMRETRIEVALID =
AIM.ARCHIVERETRIEVAL.AIMRETRIEVALID
AND AIM.ARCHIVERETRIEVAL.ARCHIVERETRIEVALID =
decArchiveRetrievalID) IN ('INTRANET', 'CIB') THEN
UPDATE AIM.ArchiveRetrieval SET ExpirationDate = (AIM.NEXT_BUS_DATE
(DATE(CURRENT TIMESTAMP),5))
WHERE ArchiveRetrievalID = decArchiveRetrievalID;
END IF;
END IF;
END P1
PROCEDURE 3
P1: BEGIN
IF (SELECT
AIM.ARCHIVETYPE.ARCHIVETYPE
FROM
AIM.ARCHIVERETRIEVAL,
AIM.ARCHIVETYPE
WHERE
AIM.ARCHIVETYPE.ARCHIVEID = AIM.ARCHIVERETRIEVAL.ARCHIVEID
AND AIM.ARCHIVERETRIEVAL.ARCHIVERETRIEVALID =
decArchiveRetrievalID) = 'O'
THEN
UPDATE AIM.ArchiveRetrieval SET StatusID = decStatusID,
ArchiveRetrievalResponse = CURRENT TIMESTAMP
WHERE ArchiveRetrievalID = decArchiveRetrievalID;
ELSE IF
(SELECT
AIM.ARCHIVETYPE.ARCHIVETYPE
FROM
AIM.ARCHIVERETRIEVAL,
AIM.ARCHIVETYPE
WHERE
AIM.ARCHIVETYPE.ARCHIVEID = AIM.ARCHIVERETRIEVAL.ARCHIVEID
AND AIM.ARCHIVERETRIEVAL.ARCHIVERETRIEVALID =
decArchiveRetrievalID) = 'D'
AND
(SELECT
AIM.CHANNEL.CHANNEL
FROM
AIM.AIMCONNECTION,
AIM.CHANNEL,
AIM.CHANNELSESSION,
AIM.AIMQUERY,
AIM.AIMRETRIEVAL,
AIM.ARCHIVERETRIEVAL
WHERE
AIM.CHANNEL.CHANNELID = AIM.CHANNELSESSION.CHANNELID
AND AIM.CHANNELSESSION.CHANNELSESSIONID =
AIM.AIMCONNECTION.CHANNELSESSIONID
AND AIM.AIMCONNECTION.AIMCONNECTIONID =
AIM.AIMQUERY.AIMCONNECTIONID
AND AIM.AIMQUERY.AIMQUERYID = AIM.AIMRETRIEVAL.AIMQUERYID
AND AIM.AIMRETRIEVAL.AIMRETRIEVALID =
AIM.ARCHIVERETRIEVAL.AIMRETRIEVALID
AND AIM.ARCHIVERETRIEVAL.ARCHIVERETRIEVALID =
decArchiveRetrievalID) IN ('INTRANET', 'CIB') THEN
UPDATE AIM.ArchiveRetrieval SET StatusID = decStatusID,
ArchiveRetrievalResponse = CURRENT TIMESTAMP, ExpirationDate =
(AIM.NEXT_BUS_DATE (DATE(CURRENT TIMESTAMP),5))
WHERE ArchiveRetrievalID = decArchiveRetrievalID;
ELSE IF
(SELECT
AIM.ARCHIVETYPE.ARCHIVETYPE
FROM
AIM.ARCHIVERETRIEVAL,
AIM.ARCHIVETYPE
WHERE
AIM.ARCHIVETYPE.ARCHIVEID = AIM.ARCHIVERETRIEVAL.ARCHIVEID
AND AIM.ARCHIVERETRIEVAL.ARCHIVERETRIEVALID =
decArchiveRetrievalID) = 'D'
AND
(SELECT
AIM.CHANNEL.CHANNEL
FROM
AIM.AIMCONNECTION,
AIM.CHANNEL,
AIM.CHANNELSESSION,
AIM.AIMQUERY,
AIM.AIMRETRIEVAL,
AIM.ARCHIVERETRIEVAL
WHERE
AIM.CHANNEL.CHANNELID = AIM.CHANNELSESSION.CHANNELID
AND AIM.CHANNELSESSION.CHANNELSESSIONID =
AIM.AIMCONNECTION.CHANNELSESSIONID
AND AIM.AIMCONNECTION.AIMCONNECTIONID =
AIM.AIMQUERY.AIMCONNECTIONID
AND AIM.AIMQUERY.AIMQUERYID = AIM.AIMRETRIEVAL.AIMQUERYID
AND AIM.AIMRETRIEVAL.AIMRETRIEVALID =
AIM.ARCHIVERETRIEVAL.AIMRETRIEVALID
AND AIM.ARCHIVERETRIEVAL.ARCHIVERETRIEVALID =
decArchiveRetrievalID) IN ('RIB') THEN
UPDATE AIM.ArchiveRetrieval SET StatusID = decStatusID,
ArchiveRetrievalResponse = CURRENT TIMESTAMP, ExpirationDate =
DATE(CURRENT TIMESTAMP) + 30 DAYS
WHERE ArchiveRetrievalID = decArchiveRetrievalID;
END IF;
END IF;
END IF;
END P1
Thanks again for any help...
