sql - w3schools - update view in oracle




How to retrieve the SQL used to create a view in Oracle? (4)

In Oracle, to retrieve the SQL used to create a Function, Package, etc, the user_source view can be queried. However, views are not included in this view - nor do they exist in the underlying sys.source$. To access the text of views, the user_views.text column can be used, but this is not exact because Oracle will re-write some parts of the query, for example it will do glob expansion.

How can I retrieve the SQL used to create a view, exactly as it was entered, without glob expansion?


I think as developer the interest of view is the "select" which make it valid and runnable, and the easiest way to edit a view is by using another application like pl/sql Developer or by TOAD.

editing any object in oracle database by text editor only like sqlplus.exe takes a long time for simple tasks, and it is a headache way to do so.


I think the original text is lost:

create table t1(id number)
/
create view t1_vw as select * from t1
/
alter table t1 add val varchar2(20)
/
alter view t1_vw compile
/
select * from t1_vw
/

will return only id column. Interesting, but for materialized views original text is preserved.


You can use data dictionary views (V$SQL, V$SQLTEXT) to see the actual SQL entered.

Please see this related question.






oracle