Compiling PL/SQL Containing INTERVAL MONTH Fails With PLS-00201, Work OK in SQL (Doc ID 344276.1)

Last updated on JULY 05, 2017

Applies to:

PL/SQL - Version: 8.0.6.0 to 8.1.7.4 - Release: 8.0.6 to 8.1.7
Information in this document applies to any platform.
Checked for relevance on 14-May-2010

Symptoms

PROCEDURE Containing INTERVAL MONTH Fails to Compile With PL/SQL:

PLS-00201: identifier 'NUMTOYMINTERVAL' must be declared

Fails in Oracle8i .

Work OK in Oracle9i.


Verified issue with code:
SQL> CREATE OR REPLACE
  2  PROCEDURE p_NUMTOYMINTERVAL (pDebut in out date,
  3  pFinal in out date)
  4  is
  5  begin
  6  select to_date ('01/'||to_char(sysdate, 'MM/YYYY'), 'DD/MM/YYYY') -
  7  interval '3' MONTH
  8  into pDebut
  9  from dual;
 10  select last_day (pDebut) into pFinal from dual;
 11  end;
 12  /

Warning: Procedure created with compilation errors.

SQL> show error
Errors for PROCEDURE P_NUMTOYMINTERVAL:

LINE/COL ERROR
-------- -----------------------------------------------------------------
5/1      PL/SQL: SQL Statement ignored
6/1      PLS-00201: identifier 'NUMTOYMINTERVAL' must be declared





Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms