通过有限的控制获取 Oracle View Definition
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/31104012/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Get Oracle View Definition with limited control
提问by markokstate
My issue is I have a custom SQL runner hitting an Oracle, readonly db.
我的问题是我有一个自定义 SQL 运行程序访问 Oracle，只读数据库。
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.
这将返回有限的文本。最多有这么多字符，可能有 100 个。所有的视图都比这个长。
The help file I found showed adding the 'set long 10000' before to capture the entire field I guess.
我发现的帮助文件显示之前添加了“set long 10000”以捕获我猜的整个字段。
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 10000since I'm running through another window. Is there another way to get the full definition with my limited ability?
set long 10000由于我正在通过另一个窗口运行，因此我无法访问。有没有另一种方法可以以我有限的能力获得完整的定义？
回答by Marmite Bomber
Your problem is the LONG column containing the view definition.
您的问题是包含视图定义的 LONG 列。
You may use the DBMS_METADATA package to get the view text as a CLOB
您可以使用 DBMS_METADATA 包来获取视图文本作为 CLOB
select DBMS_METADATA.GET_DDL ('VIEW','view_name','owner') from dual;
回答by Gerrat
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:
使用@Marmite 的建议DBMS_METADATA.GET_DDL，并假设您使用的工具一次只允许您检索 100 个字符，以下内容应检索您的完整视图：
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;
