Oracle

錯誤 (2.17):PLS-00201:必須聲明標識符“DAYS_INTERVAL_TABLE”

  • October 14, 2019

我最近安裝了一個Oracle 12cin Oracle Linux 7.6,以前是 in Windows。在恢復的第一個數據庫中,某些函式存在編譯錯誤。

下面的查詢通知您存在語法錯誤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。現在數據庫沒有任何問題。

引用自:https://dba.stackexchange.com/questions/229607