invisible columns in oracle 12c view

Question

One of the topics on the Oracle 1Z0-047 sql expert exam is "Create simple and complex views with visible/invisible columns". I can create tables with invisible columns, I can create a view containing those invisible columns, but I cannot find the syntax to make those columns invisible in the resulting view. Oracle documentation mentions visible/invisible in the Database SQL language reference "Create View" page but gives no specific example and my attempt at following their squiggly diagram failed:

create or replace view jl_book_author as  
    select title, b.isbn isbn invisible, lname, fname  
        from jl_books b  
            join jl_bookauthor a on b.isbn = a.isbn  
            join jl_author r on a.authorid = r.authorid  
         order by title, lname, fname;  
ERROR at line 3:
ORA-00923: FROM keyword not found where expected

Any idea how to do make a column of a view invisible and why I would want to do this other than to pass the exam?

Answer 1

This works. The only way to specify "INVISIBLE" is if you're using column aliases in the view. The aliases, constraints and INVISIBLE/VISIBLE modifiers need to be in parenthesis between the view name and keyword "AS".

create or replace view jl_book_author (tl, bn INVISIBLE, al, af) as  
    select title, b.isbn, lname, fname  
        from jl_books b  
            join jl_bookauthor a on b.isbn = a.isbn  
            join jl_author r on a.authorid = r.authorid  
         order by title, lname, fname;  

Answer 2

"why I would want to do this other than to pass the exam?"

You use the INVISIBLE clause, as stated in the Oracle SQL Reference CREATE TABLE, to add columns to any table, without having code that includes wildcard SELECT * statements add to it haphazardly.

Therefore very useful for adding columns to tables that you wouldnt want legacy code to casually manipulate.

For a fuller explanation and examples see the article ASK TOM: technology from the MAY/JUNE 2014 edition of Oracle Magazine.