Re: Warning: View altered with compilation errors

From: magicwand <magicwand_at_gmx.at>
Date: Fri, 26 Mar 2010 14:53:15 -0700 (PDT)
Message-ID: <b5a24919-ddfb-4c19-9ed6-61ad817c64d2_at_15g2000yqi.googlegroups.com>

On 26 Mrz., 21:19, Mark D Powell <Mark.Powe..._at_hp.com> wrote:
> On Mar 26, 3:14 pm, zigzagdna <zigzag..._at_yahoo.com> wrote:
>
> > I am using 10.2.0.3 on HP UNIX 11i. I got some warning when I  did
> > import data pump. But when I tried to start compiling objects, I do
> > not see any error messages in show errors, why?
>
> > SQL> alter view TW_RPT_4232 compile;
>
> > Warning: View altered with compilation errors.
>
> > SQL> show errors;
> > No errors.
> > SQL>
>
> I believe this is expected behavior.  Using 9.2.0.6 to test and I can
> duplicate the results on 10.2.0.3
>
> SQL> create view bad as select * from marktest2;
>
> View created.
>
> SQL> drop table marktest2;
>
> Table dropped.
>
> SQL> alter view bad compile;
>
> Warning: View altered with compilation errors.
>
> SQL> show errors;
> No errors.
> SQL> select * from dba_errors where name = 'BAD';
> OWNER                            BAD                            VIEW
>          1          0          0
> ORA-00942: table or view does not exist
>
> On the other hand if I do this with stored code: Procedure, Package,
> or Function I get results: (tested on 10.2.0.3 only)
>
> SQL> create table marktest (fld1 number, fld2 varchar2(10), fld3
> date);
>
> Table created.
>
> SQL> create procedure p1 as
>   2  v_ctr number;
>   3  begin
>   4  select count(*) into v_ctr from marktest;
>   5  end;
>   6  /
>
> Procedure created.
>
> SQL> drop table marktest;
>
> Table dropped.
>
> SQL> alter procedure p1 compile;
>
> Warning: Procedure altered with compilation errors.
>
> SQL> show errors
> Errors for PROCEDURE P1:
>
> LINE/COL ERROR
> --------
> -----------------------------------------------------------------
> 4/1      PL/SQL: SQL Statement ignored
> 4/33     PL/SQL: ORA-00942: table or view does not exist
> SQL>
>
> HTH -- Mark D Powell --

The reason incomplete syntax

The full syntax according to the "SQL*Plus User's Guide and Reference":

ERR[ORS] [{FUNCTION | PROCEDURE | PACKAGE | PACKAGE BODY | TRIGGER | VIEW | TYPE | TYPE BODY | DIMENSION | JAVA CLASS} [schema.]name]

and - for whatever reason - it defaults to the last compiled package, procedure or function.

SQL> create table test(a number, b varchar2(10));

Table created.

SQL> create view v_test as select * from test;

View created.

SQL> drop table test;

Table dropped.

SQL> select * from v_test;
select * from v_test

              *
ERROR at line 1:
ORA-04063: view "TST.V_TEST" has errors

SQL> show error view v_test
Errors for VIEW V_TEST:

LINE/COL ERROR

---

---

0/0 ORA-00942: table or view does not exist Received on Fri Mar 26 2010 - 16:53:15 CDT