在一些情况下，我们要获取某个对象的SQL语句，以方便分析问题。
如：存储过程编译失败，我们可以通过详细错误信息知道代码哪行出现错误，而源SQL语句不是自己开发，这时就需要知道ORACLE数据库对象SQL语句。
ALL_SOURCE describes the text source of the stored objects accessible to the current user.
Related Views
-
DBA_SOURCE describes the text source of all stored objects in the database.
USER_SOURCE describes the text source of the stored objects owned by the current user. This view does not display the OWNER column.
SQL> select distinct type from all_source ;
TYPE
--------------------
PROCEDURE
PACKAGE
PACKAGE BODY
TYPE BODY
TRIGGER
FUNCTION
TYPE
7 rows selected.
SQL> create or replace function substring
(vsS IN char , viS IN integer , viL IN integer )
RETURN char
IS
BEGIN
RETURN(substr(vsS,viS,viL)) ;
END;
/
Function created.
SQL> select OWNER,NAME,TYPE,LINE,TEXT from all_source where NAME ='SUBSTRING';
OWNER NAME TYPE LINE TEXT
---------- -------------------- -------------------- ---------- --------------------------------------------------
SYS SUBSTRING FUNCTION 1 function substring
SYS SUBSTRING FUNCTION 2 (vsS IN char , viS IN integer , viL IN integer )
SYS SUBSTRING FUNCTION 3 RETURN char
SYS SUBSTRING FUNCTION 4 IS
SYS SUBSTRING FUNCTION 5 BEGIN
SYS SUBSTRING FUNCTION 6 RETURN(substr(vsS,viS,viL)) ;
SYS SUBSTRING FUNCTION 7 END;
7 rows selected.
