Stored Procedure Question
Date: 1998/05/13
Message-ID: <6jckgg$q20_at_jupiter.planet.net>#1/1
[Quoted] 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 CEST