CODEWITHSUNDEEP
sqlvieworacle12c
Leonardo Repolust
Leonardo Repolust

Reputation: 161

Invalid View cannot be compiled

I have an invaild view on my PROD system:

PROD.CLIENT

I found out that is invalid with following statement:

select
    owner c1,
    object_type c3,
    object_name c2
from
    dba_objects
where
    status != 'VALID'
order by
    owner,
    object_type;

I tried recompiling it but when I try the first statement it still shows up:

ALTER VIEW PROD.CLIENT COMPILE;

I also tried searching for user errors, but there are none:

SELECT * FROM user_errors;

How can I find the problem or error with my invalid view?

Upvotes: 0

Views: 571

Answers (2)

MT0
MT0

Reputation: 167962

SELECT * FROM user_errors;

Will only find errors belonging to your current user.

You are re-compiling a view belonging to the PROD user and if that is not the user you are connected as then you will not see the errors.

Instead, you can use:

SELECT * FROM all_errors WHERE owner='PROD' and name = 'CLIENT';

Upvotes: 1

lavantho0508
lavantho0508

Reputation: 155

You should CREATE VIEW :

 CREATE VIEW example AS
 select
    owner c1,
    object_type c3,
    object_name c2
from
    dba_objects
where
    status != 'VALID'
order by
    owner,
    object_type;
--- and then 
SELECT * FROM example; -- call view

Upvotes: 0

Related Questions