Get Oracle View Definition with limited control

Question

My issue is I have a custom SQL runner hitting an Oracle, readonly db.

I want to get the definition of a view.

 select TEXT
 FROM all_VIEWS
 where VIEW_NAME  = '<view_name>';

This returns me limited text. A max of so many characters, maybe 100. All the views are longer than this.

The help file I found showed adding the 'set long 10000' before to capture the entire field I guess.

SQL> set long 10000

SQL> select TEXT
  2  FROM all_VIEWS
  3  where VIEW_NAME  = '<view_name>';

I don't have access to hit the set long 10000 since I'm running through another window. Is there another way to get the full definition with my limited ability?

Answer 1

Your problem is the LONG column containing the view definition.

You may use the DBMS_METADATA package to get the view text as a CLOB

select DBMS_METADATA.GET_DDL ('VIEW','view_name','owner') from dual;