Re: Stored Proc Compilation Error

On 21 Oct 2004 09:10:24 -0700, dc wrote:

> Hey Brian,
>
> So you were able to take that code, place it into a stored procedure
> and compile it?
>
> I double checked and that DATE is the name of the column. Here is the
> source table name definition.
>
> CREATE TABLE HISTORY
> (
> RECNUM NUMBER(28) NOT NULL,
> AP# VARCHAR2(14 BYTE) DEFAULT ' ' NOT NULL,
> CHANGED_FIELD VARCHAR2(16 BYTE) DEFAULT ' ' NOT NULL,
> PROGRAM VARCHAR2(8 BYTE),
> DATA_BEFORE VARCHAR2(30 BYTE), DATA_AFTER VARCHAR2(30
> BYTE) DEFAULT ' ' NOT NULL,
> "DATE" DATE DEFAULT
> TO_DATE('01/01/0001','MM/DD/YYYY') NOT NULL,
> HR NUMBER(2) DEFAULT 0 NOT NULL,
> MIN NUMBER(2) DEFAULT 0 NOT NULL,
> "BY" VARCHAR2(25 BYTE),
> "COMMENT" VARCHAR2(70 BYTE)
> )
>
> - Brent

I just ran it as SQL. When I put it in an anonymous PL/SQL block I get the following error.

--

PL/SQL: ORA-06552: PL/SQL: Compilation unit analysis terminated
ORA-06553: PLS-320: the declaration of the type of this expression is
incomplete or malformed

--

If I rename the "DATE" column to "date" the PL/SQL works too. The
explanation for the PLS-320 is as follows.

--

PLS-00320 the declaration of the type of this expression is incomplete or
malformed

Cause: In a declaration, the name of a variable or cursor is misspelled or
the declaration makes a forward reference. Forward references are not
allowed in PL/SQL. A variable or cursor must be declared before it is
referenced it in other statements, including other declarative statements.
For example, the following declaration of dept_rec raises this exception
because it refers to a cursor not yet declared:

DECLARE
dept_rec  dept_cur%ROWTYPE; 
CURSOR dept_cur IS SELECT ...  
...

Action: Check the spelling of all identifiers in the declaration. If
necessary, move the declaration so that it makes no forward references.

--

Mmmm... Since you don't have any declarations at all, let alone forward
referencing ones, the error message is confusing. Without more digging, I
would say you found a PL/SQL bug. As a work-around you should reconsider
renaming your columns.

--
Man goes to doctor, raises arm and says "Doctor, it hurts when I do this".
Doctor replies "Don't do that".
--