Re: Warning: View altered with compilation errors

From: zigzagdna <zigzagdna_at_yahoo.com>
Date: Fri, 26 Mar 2010 19:41:16 -0700 (PDT)
Message-ID: <a70defbd-8b7c-418f-b387-95322505f8c6_at_g10g2000yqh.googlegroups.com>

On Mar 26, 5:53 pm, magicwand <magicw..._at_gmx.at> wrote:
> 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- Hide quoted text -
>
> - Show quoted text -

That explains, learnng something new. Thanks so much. Received on Fri Mar 26 2010 - 21:41:16 CDT