blocks|key|124479|text|我认为作为开发人员，视图的兴趣在于使其有效和可运行的"select“，而编辑视图的最简单方法是使用另一个应用程序，如pl/sql+developer或TOAD。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|124480|对于简单的任务，仅使用文本编辑器(如sqlplus.exe+)来编辑oracle数据库中的任何对象都需要很长时间，而且这是一种令人头疼的方法。|124481|​|124482|📷|atomic|offset|length|124483|124484|entityMap|0|IMAGE|mutability|IMMUTABLE|imageUrl|https://ask.qcloudimg.com/http-save/yehe-900000/eeba98cb721a361e7899225930001c99.png|imageAlt^0|0|0|0|0|1|0|0|0^^$0|@$1|2|3|4|5|6|7|U|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|V|8|@]|9|@]|A|$]]|$1|D|3|E|5|6|7|W|8|@]|9|@]|A|$]]|$1|F|3|G|5|H|7|X|8|@]|9|@$I|Y|J|Z|1|10]]|A|$]]|$1|K|3|E|5|6|7|11|8|@]|9|@]|A|$]]|$1|L|3|-4|5|6|7|12|8|@]|9|@]|A|$]]]|M|$N|$5|O|P|Q|A|$R|S|T|-4]]]]

I think as developer the interest of view is the "select" which make it valid and runnable, and the easiest way to edit a view is by using another application like pl/sql Developer or by TOAD.

editing any object in oracle database by text editor only like sqlplus.exe takes a long time for simple tasks, and it is a headache way to do so.

<img src="https://i.stack.imgur.com/pGmIS.png" alt="enter image description here">

blocks|key|4630561|text|我认为原文丢失了：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|4630562|create+table+t1(id+number)
/
create+view+t1_vw+as+select+*+from+t1
/
alter+table+t1+add+val+varchar2(20)
/
alter+view+t1_vw+compile
/
select+*+from+t1_vw
/|code-block|syntax|javascript|4630563|将仅返回id列。这很有趣，但对于物化视图，原始文本将被保留。|4630564|entityMap^0|0|0|0^^$0|@$1|2|3|4|5|6|7|K|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|L|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|M|8|@]|9|@]|A|$]]|$1|I|3|-4|5|6|7|N|8|@]|9|@]|A|$]]]|J|$]]

I think the original text is lost:

<pre><code>create table t1(id number)
/
create view t1_vw as select * from t1
/
alter table t1 add val varchar2(20)
/
alter view t1_vw compile
/
select * from t1_vw
/
</code></pre>

will return only id column.
Interesting, but for materialized views original text is preserved.

blocks|key|4630588|text|您可以使用数据字典视图(V$+SQL，V$SQLTEXT)来查看实际输入的SQL。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|4630589|请参阅this+related+question。|offset|length|4630590|entityMap|0|LINK|mutability|MUTABLE|url|https://stackoverflow.com/questions/55899/how-to-see-the-actual-oracle-sql-statement-that-is-being-executed^0|0|3|L|0|0^^$0|@$1|2|3|4|5|6|7|N|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|O|8|@]|9|@$D|P|E|Q|1|R]]|A|$]]|$1|F|3|-4|5|6|7|S|8|@]|9|@]|A|$]]]|G|$H|$5|I|J|K|A|$L|M]]]]

You can use data dictionary views (V$SQL, V$SQLTEXT) to see the actual SQL entered.

Please see <a href="https://stackoverflow.com/questions/55899/how-to-see-the-actual-oracle-sql-statement-that-is-being-executed">this related question</a>.

blocks|key|124560|text|我使用dbms_metadata.get_ddl的方法如下：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|124561|select
dbms_metadata.get_ddl('VIEW',+'VIEW_NAME',+'VIEW_OWNER')+
from
dual;|code-block|syntax|javascript|124562|参数如下：|124563|DBMS_METADATA.GET_DDL+(
object_type+++++IN+VARCHAR2,
name++++++++++++IN+VARCHAR2,
schema++++++++++IN+VARCHAR2+DEFAULT+NULL,
version+++++++++IN+VARCHAR2+DEFAULT+'COMPATIBLE',
model+++++++++++IN+VARCHAR2+DEFAULT+'ORACLE',
transform+++++++IN+VARCHAR2+DEFAULT+'DDL')
RETURN+CLOB;|124564|它有一个公共同义词。对于不属于用户的对象，用户可以获得系统权限SELECT_CATALOG_ROLE，并且可以看到所有对象的所有DDL。|124565|在Oracle+SQL+Developer中，可以在查询结果窗口中看到clob+(许多人还创建了实用程序来转换clob)。|124566|大多数SQL+IDE工具都有一些DDL查看机制，尽管DBMS_METADATA是Oracle数据库的种子功能(不需要一些花哨、昂贵的工具)。|124567|entityMap^0|0|0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|S|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|T|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|U|8|@]|9|@]|A|$]]|$1|I|3|J|5|D|7|V|8|@]|9|@]|A|$E|F]]|$1|K|3|L|5|6|7|W|8|@]|9|@]|A|$]]|$1|M|3|N|5|6|7|X|8|@]|9|@]|A|$]]|$1|O|3|P|5|6|7|Y|8|@]|9|@]|A|$]]|$1|Q|3|-4|5|6|7|Z|8|@]|9|@]|A|$]]]|R|$]]

I use dbms_metadata.get_ddl as follows: 

<pre><code>select
dbms_metadata.get_ddl('VIEW', 'VIEW_NAME', 'VIEW_OWNER') 
from
dual;
</code></pre>

Here are the parameter:

<pre><code>DBMS_METADATA.GET_DDL (
object_type IN VARCHAR2,
name IN VARCHAR2,
schema IN VARCHAR2 DEFAULT NULL,
version IN VARCHAR2 DEFAULT 'COMPATIBLE',
model IN VARCHAR2 DEFAULT 'ORACLE',
transform IN VARCHAR2 DEFAULT 'DDL')
RETURN CLOB;
</code></pre>

It has a public synonym. For objects not owned by user, one can obtain the system privilege, SELECT_CATALOG_ROLE, and one can see all DDL for all objects.

In Oracle SQL Developer, one can see the clob in the query results window (many people have created utilities to transform clobs as well).

Most SQL IDE tools have some DDL viewing mechanism, though DBMS_METADATA is seeded Oracle database functionality (one does not need some fancy, expensive tool).

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 <code>sys.source$</code>. To access the text of views, the <code>user_views.text</code> 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?

How to retrieve the SQL used to create a view in Oracle?

Oracle数据库

数据库

在Oracle中，要检索用于创建函数、包等的SQL，可以查询user_source视图。但是，视图不包括在此视图中-它们也不存在于基础sys.source$中。要访问视图的文本，可以使用Oracle列，但这并不准确，因为user_views.text将重写查询的某些部分，例如，它将执行全局扩展。如何检索用于创建视图的S...

问如何在Oracle中检索用于创建视图的SQL？
EN

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问如何在Oracle中检索用于创建视图的SQL？EN

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问如何在Oracle中检索用于创建视图的SQL？
EN