Warning: View altered with compilation errors

[zigzagdna]

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>

[Mark D Powell]

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 --

[magicwand]

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

[zigzagdna]

> 0/0      ORA-00942: table or view does not exist- Hide quoted text -
>
> - Show quoted text -

That explains, learnng something new. Thanks so much.

[Mark D Powell]

On Mar 26, 5:53 pm, magicwand <magicw...@gmx.at> wrote:

> 0/0      ORA-00942: table or view does not exist- Hide quoted text -
>
> - Show quoted text -

Cool fact. I didn't think to check the syntax diagram..

-- Mark --