oracle年份超出范围,oracle - ORA-01428：参数超出范围 - 堆栈内存溢出

我从PL SQL函数返回全局临时表。 但是出现错误，如ORA-01428：参数超出范围。

create or replace FUNCTION Country_TOP_500(ISO_COUNTRY_CODE IN VARCHAR, For_DATE DATE)

RETURN Country_TOP_500_TABLE_TYPE AS

--To create a seperate transcion for the function we have created.

PRAGMA AUTONOMOUS_TRANSACTION;

CNT NUMBER;

V_RET Country_TOP_500_TABLE_TYPE;

BEGIN

--TRUNCATING GTT TABLE USED IN A FUNCTION

EXECUTE IMMEDIATE 'TRUNCATE TABLE Country_TOP_500_GT_TABLE';

INSERT INTO FDS_DEMO.Country_TOP_500_GT_TABLE (RNUM,FID , Company_Name)

select

ROWNUM,c.FSYM_PRIMARY_LISTING_ID,a.ENTITY_PROPER_NAME

from

EDM_V1_EDM_STANDARD_ENTITY a, FF_V3_FF_SEC_ENTITY b, SYM_V1_SYM_COVERAGE c, REF_V2_COUNTRY_MAP d

where

a.FACTSET_ENTITY_ID=b.FACTSET_ENTITY_ID and c.FSYM_ID=b.FSYM_ID and

c.FSYM_ID=c.FSYM_PRIMARY_EQUITY_ID and a.ISO_COUNTRY_INCORP=d.ISO_COUNTRY and

c.FSYM_PRIMARY_LISTING_ID is not NULL and ISO_COUNTRY_INCORP=ISO_COUNTRY_CODE;

UPDATE FDS_DEMO.Country_TOP_500_GT_TABLE a

SET Latest_PRICE_DATE = (select MAX(P_DATE) from FP_V2_FP_BASIC_PRICES where FSYM_ID =a.FID and

P_DATE<=For_DATE and P_DATE>=ADD_MONTHS(For_DATE,0)-30 and P_VOLUME<>0 and P_PRICE IS NOT NULL);

UPDATE FDS_DEMO.Country_TOP_500_GT_TABLE a

SET Latest_DATE_PRICE = (select ROUND(P_PRICE,6) from table(FP_BASIC_BD_PRICES(a.FID)) where P_DATE=a.Latest_PRICE_DATE)

where a.Latest_PRICE_DATE is NOT NULL;

UPDATE FDS_DEMO.Country_TOP_500_GT_TABLE a

SET Shares_Outstanding = (select FF_COM_SHS_OUT from FF_V3_FF_BASIC_AF b where b.FSYM_ID=a.FID

and b."DATE"=(select MAX("DATE") from FF_V3_FF_BASIC_AF where FSYM_ID=b.FSYM_ID and "DATE"<=For_DATE))

where a.Latest_PRICE_DATE is NOT NULL;

UPDATE FDS_DEMO.Country_TOP_500_GT_TABLE a

SET MARKET_CAP = (select Round(Latest_DATE_PRICE * Shares_Outstanding,6) from FDS_DEMO.Country_TOP_500_GT_TABLE where FID=a.FID)

where a.Latest_DATE_PRICE is not NULL and a.Shares_Outstanding is not NULL;

--Get the GTT values into table derived from object type and return it.

SELECT

CAST(

MULTISET(

SELECT * FROM FDS_DEMO.Country_TOP_500_GT_TABLE where Latest_PRICE_DATE is NOT NULL and Shares_Outstanding is NOT NULL order by MARKET_CAP desc

)AS Country_TOP_500_TABLE_TYPE

) INTO V_RET FROM DUAL;

COMMIT;

RETURN V_RET;

END Country_TOP_500;

如果我尝试获得500行功能，则可以成功运行，但不能正常运行。

有关类型和全局临时表的其他信息。

create or replace TYPE Country_TOP_500_COLUMN AS OBJECT (

RNUM NUMBER,

FID VARCHAR2 (8 CHAR),

Company_Name VARCHAR2(200 CHAR),

Latest_PRICE_DATE DATE,

Latest_DATE_PRICE FLOAT(126),

Shares_Outstanding FLOAT(126),

MARKET_CAP FLOAT(126)

);

create or replace TYPE Country_TOP_500_TABLE_TYPE AS TABLE OF Country_TOP_500_COLUMN;

CREATE GLOBAL TEMPORARY TABLE FDS_DEMO.Country_TOP_500_GT_TABLE

(

RNUM NUMBER,

FID VARCHAR2(8 CHAR) NOT NULL ,

Company_Name VARCHAR2(200 CHAR),

Latest_PRICE_DATE DATE,

Latest_DATE_PRICE FLOAT(126),

Shares_Outstanding FLOAT(126),

MARKET_CAP FLOAT(126),

CONSTRAINT FID_pk PRIMARY KEY (FID)

) ON COMMIT PRESERVE ROWS ;

这是有关类型和使用的GTT的信息。