Stored Procedure Question

From: Jennifer Caudullo <jennifer_caudullo_at_acwins.com>
Date: 1998/05/13
Message-ID: <6jckgg$q20@jupiter.planet.net>#1/1

I am creating a package that one of the procedures within needs a temp table. As far as I am aware you cannot create a temp table in Oracle. If I am wrong, please let me know. So what I did was within my package, I created a table, inserted into it and then updated it and then dropped it. Here is the code. Could you please tell me what is wrong with it? My email is jennifer_caudullo_at_acwins.com

Thank you. I appreciate it.

CREATE TABLE IBNR.TEMP
(
ACCIDENT_YEAR_GROUP NUMBER NOT NULL,
SELECT_ARM NUMBER NOT NULL,
GAAP_ADJUSTMENT_AMOUNT NUMBER NOT NULL, STATUTORY_ADJUSTMENT_AMOUNT NUMBER NOT NULL) INSERT INTO IBNR.TEMP SELECT ACCIDENT_YEAR_GROUP, SELECT_ARM, 0, 0 FROM ACCIDENT_YEAR_GROUP

WHERE ACCIDENT_YEAR_GROUP.SCENARIO = SCENARIOIN AND
 ACCIDENT_YEAR_GROUP.RESERVING_LINE = RESERVING_LINE AND
 ACCIDENT_YEAR_GROUP.CLAIM_TYPE = CLAIM_TYPEIN AND
 ACCIDENT_YEAR_GROUP.COMPANY=COMPANYIN AND
 ACCIDENT_YEAR_GROUP.BUSINESS_TYPE=BUSINESS_TYPEIN AND
 ACCIDENT_YEAR_GROUP.CATEGORY=CATEGORYIN;

UPDATE TEMP
SET GAAP_ADJUSTMENT_AMOUNT=(SELECT GAAP_ADJUSTMENT_AMOUNT FROM IBNR.ADJUSTMENT), STATUTORY_ADJUSTMENT_AMOUNT=(SELECT STATUTORY_ADJUSTMENT_AMOUNT
FROM IBNR.ADJUSTMENT)
WHERE ADJUSTMENT.SCENARIO=SCENARIOIN AND

 ADJUSTMENT.RESERVING_LINE = RESERVING_LINE AND
 ADJUSTMENT.CLAIM_TYPE = CLAIM_TYPEIN AND
 ADJUSTMENT.COMPANY = COMPANYIN AND
 ADJUSTMENT.BUSINESS_TYPE = BUSINESS_TYPEIN AND
 ADJUSTMENT.CATEGORY=CATEGORYIN AND

 VALUATION_DATE = (SELECT VALUATION_DATE FROM IBNR.SCENARIO WHERE SCENARIO = SCENARIOIN); Drop table ibnr.temp Received on Wed May 13 1998 - 00:00:00 CDT