Get Oracle View Definition with limited control

Question

My problem is that I have a custom SQL runner that got into Oracle, read-only.

I want to get a view definition.

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

This returns me limited text. The maximum number of characters is possibly 100. All representations are longer than this.

The help file I found showed the addition of 'set long 10000' before capturing the entire field that I assume.

 SQL> set long 10000 SQL> select TEXT 2 FROM all_VIEWS 3 and VIEW_NAME = '<view_name>'; 

I do not have access to get into set long 10000 , since I am launching another window. Is there any other way to get a complete definition with my disabilities?

Answer 1

Your problem is the LONG column containing the view definition.

You can use DBMS_METADATA package to get view text as CLOB

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

Answer 2

Using the @Marmite clause DBMS_METADATA.GET_DDL , and suppose the tool you are using allows you to extract 100 characters at a time, the following should get your full view:

 SELECT view_name, LEVEL "Line No", DBMS_LOB.SUBSTR(view_clob, 100 ,1 + (LEVEL-1)*100) line_text FROM ( SELECT view_name, owner, DBMS_METADATA.GET_DDL('VIEW', view_name, owner) view_clob FROM all_views WHERE view_name = '<view_name>' ) CONNECT BY LEVEL <= CEIL(LENGTHB(view_clob)/100) ORDER BY LEVEL;