Menu
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?
335
A view is a virtual table because you can use it like a table in your SQL queries. Every view has columns with data types so you can execute a query against views or manage their contents (with some restrictions) using the INSERT , UPDATE , DELETE , and MERGE statements. Unlike a table, a view does not store any data.
You simply execute dbms_metadata. get_ddl, specify the object names, and Oracle will extract ready-to-use DDL.
You can use the following query:
SELECT VIEW_NAME, TEXT FROM USER_VIEWS; or you can use ALL_VIEWS, as in
SELECT VIEW_NAME, TEXT FROM ALL_VIEWS; References:
ALL_VIEWS on Oracle® Database Reference
163
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
