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?

There seems to be a syntax error in the question. There is no WHERE clause, but AND keyword has been used. Correct query should be select TEXT FROM all_VIEWS WHERE VIEW_NAME = ''; — Mandar, Jan 30 '18 at 22:36

score 12 · Accepted Answer · answered Jun 28 '15 at 20:42

12

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;

answered Jun 28 '15 at 20:42

Marmite Bomber

19,886
4
26
53

@IstvanDevai Maybe, but I have ATM no idea how, sorry. – Marmite Bomber Dec 20 '19 at 16:04
1

Unfortunately, you need privileged access for this to work. https://stackoverflow.com/questions/26026068/dbms-metadata-get-ddl-not-working – Brad Rhoads Mar 18 '21 at 18:51

score 1 · Answer 2 · answered Jun 28 '15 at 21:30

1

Using @Marmite's suggestion of DBMS_METADATA.GET_DDL, and assuming the tool you're using only lets you retrieve 100 chars at a time, the following should retrieve your complete 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;

answered Jun 28 '15 at 21:30

Gerrat

28,863
9
73
101

ORA-22998: CLOB or NCLOB in multibyte character set not supported – vulkanino Dec 17 '18 at 16:24
@vulkanino: Really? That is the exact error message that the `DBMS_LOB.SUBSTR` should avoid. – Gerrat Dec 17 '18 at 18:36

Get Oracle View Definition with limited control

2 Answers2