Oracle
錯誤 (2.17):PLS-00201:必須聲明標識符“DAYS_INTERVAL_TABLE”
我最近安裝了一個
Oracle 12c
inOracle Linux 7.6
,以前是 inWindows
。在恢復的第一個數據庫中,某些函式存在編譯錯誤。下面的查詢通知您存在語法錯誤
line 2
,但我無法弄清楚問題出在哪裡。錯誤:
Error (2.17): PLS-00201: The identifier 'DAYS_INTERVAL_TABLE' must be declared
詢問:
create or replace FUNCTION funRetornaDias (p_inicio date, p_termino date) RETURN DAYS_INTERVAL_TABLE PIPELINED IS v_date date; out_rec DAYS_INTERVAL := DAYS_INTERVAL(NULL,NULL); v_ref date; BEGIN if p_inicio > p_termino then v_ref := p_inicio; v_date := p_termino; else v_ref := p_termino; v_date := p_inicio; end if; while cast(to_char(v_date, 'YYYYMMDD') as number) <= cast(to_char(v_ref, 'YYYYMMDD') as number) loop begin out_rec.date_ := v_date; out_rec.day_of_week := datepart('DW', v_date); PIPE ROW(out_rec); v_date := v_date + interval '1' day; end; end loop; RETURN; END;
我檢查了
impdb
日誌,發現這個對象由於OID
衝突沒有被導入。在日誌的開頭,我發現了這條消息。
ORA-39083: Object type TYPE:"VERZANI"."DAYS_INTERVAL" failed to create with error: ORA-02304: invalid object identifier literal Failing sql is: CREATE EDITIONABLE TYPE "VERZANI"."DAYS_INTERVAL" OID 'C01480BB3D223837E0430100007FED58' AS OBJECT (DATE_ DATE, DAY_OF_WEEK number(1,0)); ORA-39083: Object type TYPE:"VERZANI"."DAYS_INTERVAL_TABLE" failed to create with error: ORA-02304: invalid object identifier literal Failing sql is: CREATE EDITIONABLE TYPE "VERZANI"."DAYS_INTERVAL_TABLE" OID 'C01480BB3D273837E0430100007FED58' AS TABLE OF DAYS_INTERVAL;
所以我刪除了模式並
impdp
使用參數重新執行TRANSFORM=oid:n
。現在數據庫沒有任何問題。