Error (2.17): PLS-00201: The identifier 'DAYS_INTERVAL_TABLE' must be declared

Question

I recently installed an Oracle 12c in Oracle Linux 7.6, it was previously in Windows. In the first database restored, some functions are with compilation errors.

The query below informs you that there is a syntax error on line 2, but I can not figure out where the problem is.

Error: Error (2.17): PLS-00201: The identifier 'DAYS_INTERVAL_TABLE' must be declared

Query:

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;

score 1 · Accepted Answer · answered Feb 13 '19 at 16:18

I checked the impdb log and saw that this object was not imported due to an OID conflict.

At the beginning of the log I found this message.

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;

So I dropped the schema and rerun the impdp again with the parameter TRANSFORM=oid:n. Now the database went up without any problems.

Error (2.17): PLS-00201: The identifier 'DAYS_INTERVAL_TABLE' must be declared

1 Answers1