Oracle PL/SQL Tutorial/System Tables Data Dictionary/all source

Query all_source

SQL>
SQL>
SQL> COLUMN    text    FORMAT    a78
SQL> SELECT    text
  2  FROM      all_source
  3  WHERE     name = UPPER("&name")
  4  AND       type = UPPER("&type")
  5  and       rownum < 2
  6  AND       line BETWEEN &starting_line AND &ending_line;
Enter value for name:
old   3: WHERE     name = UPPER("&name")
new   3: WHERE     name = UPPER("")
Enter value for type: --

Query all_source for all packages

SQL>
SQL>
SQL> select text
  2    from all_source
  3   where name = "DBMS_OUTPUT"
  4     and type = "PACKAGE"
  5     and line < 26
  6   order by line
  7  /
TEXT
----------------------------------------------------------------------------------------------------
package dbms_output as
-- DE-HEAD     <- tell SED where to cut when generating fixed package
  ------------
  --  OVERVIEW
  --
  --  These procedures accumulate information in a buffer (via "put" and
  --  "put_line") so that it can be retrieved out later (via "get_line" or
  --  "get_lines").  If this package is disabled then all
  --  calls to this package are simply ignored.  This way, these routines
TEXT
----------------------------------------------------------------------------------------------------
  --  are only active when the client is one that is able to deal with the
  --  information.  This is good for debugging, or SP"s that want to want
  --  to display messages or reports to sql*dba or plus (like "describing
  --  procedures", etc.).  The default buffer size is 20000 bytes.  The
  --  minimum is 2000 and the maximum is 1,000,000.
  -----------
  --  EXAMPLE
  --
  --  A trigger might want to print out some debugging information.  To do
  --  do this the trigger would do
TEXT
----------------------------------------------------------------------------------------------------
  --    dbms_output.put_line("I got here:"||:new.col||" is the new value");
  --  If the client had enabled the dbms_output package then this put_line
  --  would be buffered and the client could, after executing the statement
25 rows selected.

Query all_source for certain string

SQL>
SQL>
SQL> COLUMN    text    FORMAT    a30 word_wrapped
SQL> SELECT    name, type, text
  2  FROM      all_source
  3  WHERE     UPPER(text) LIKE "%FREE BLOCKS%"
  4  and       rownum < 2;
NAME                   TYPE         TEXT
---------------------- ------------ ------------------------------
DBMS_SPACE             PACKAGE      --  Returns information about
                                    free blocks in an object
                                    (table, index,

SQL>

Query all_source table for all package bodies

SQL>
SQL> select text
  2  from all_source
  3  where name = "DBMS_OUTPUT"
  4  and type = "PACKAGE BODY"
  5  and line < 10
  6  order by line
  7  /
TEXT
----------------------------------------------------------------------------------------------------
package body dbms_output wrapped
a000000
1
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
TEXT
----------------------------------------------------------------------------------------------------
abcd
abcd
abcd
abcd
abcd
abcd
abcd
b
10b5 5cc
yNcEMLzwiOfe0821gGMhhUJI5Q0wg0PquiAF344Zxz+VQgNGtAIe+9kvmPsRHoXz2Gk5oQ0I
h3SOuIOm3l71MUD7cwjaNBjxbLBig7DzNcr3wZsQwRsj2Sv9YWBUKzHLwkPwaOoNgzyedXqM
TEXT
----------------------------------------------------------------------------------------------------
RKluYgxH3WQBXI2FU6rtz0St1Wz/ofWTwRKT5Dc1oI9ZijNE6lNga2yXrO+hjYX4FKAtKXZa
SqaOMWJnnWxi/gkTuobH4IJJYBL4O0084WN0jn1x9INn6phi5qArsVmKWC9pWml7pmlDpey9
7SQMP5BM5J/35q+CNAJD4gcW90YiErnY23GQZwCon5/zi5TJDWe0fkjMfFmFpqWS6IzpmIb9
vssvgfg5Um3mw+bF8sBwkDj//AkxoGPHwK88eHSgqPcDlObCqxIg9XY1L9jIUxcFeZPYdcXF
vHiLqiEaA+ZBYlJMuCWl2PrkBIWrqPZYyqmFA6iRx0zdkJ1ASjt+WhYsrZ66vkRbT1soJ99j
6fqn7BuJXSs901Fx68kNg453ZlL7V+EuHek9YPGt20gf1JlN9qB7zJYpZuiCCkBjihGHr9km
k4SXuTBaT/wTsQGtGi6N5xt+YB8SFGYL/1tDjIXNA5Yy1R9GWtsa6PrpK1q3dLZDGbS+PobX
vIVLogJOUOmLykH9P1oECNtBGiO/IcIFVS0vbH0Eyim3uGtF5+8Wf6wXkIC3UFntXu78Aqvo
SJY8RgK8mWPVjXzWxBejRHvDpCKLzOlQSB819Z6oV8fEhgzdPzVmmkYTndG2I3EKHgEVJX9Z
60KGbKFuj0KODokYaC5op7FI5wfmO7FQfex1uRxqTdOBRqMBM/hsemiqet1q7oaYsP6lzv7u
TlVqhiQU+w+8biodENK4te75vRy7ViEw+Lvr7q7O9c5+d4ApzBarSmsSb+p3D6eJlnCWhhW4
TEXT
----------------------------------------------------------------------------------------------------
QwxRhHMTFkxxFQWdubnng8oT3+8Q5+WDHpu9EV7trcJ6/SPNnyTiOE5XUiks46FJeQjVvpGH
CpC5fc16dbtHT/nRPOzGzQhxmXVBnqCtvH+fi3Ij0vEPwUMS/TudqKt9SBS+sMckEzmw6z5g
qw70a2vpxJUvl39T0IwZ9YZjljQ0CRBdkMCCeybUzD2sxH+VK9w21SdTFtHqISCp92zGq4WT
sMQjcKA39Wo/BtrT+1K/PdR+bXITHlGilgOl9MUKJ/1DPL8HlAzBgKdVXomaQEf1FuiD9BDa
4Bnjy5w3Ie4NrbFcZ5xoLbyyg0xFrqQf1gEDBjgkwD5zL3bOe7J33LgwGCauWLJhxkswPRGA
+yCNvmyhJdKv8QhbDPzhm7bkMAKS9AhCxrdkYnxkLyn8jr9zoLdCJYbEGyu1fKCdmc3zbFjN
QOtZM7xJszaSSRckcGVR7djxoL4O+rgNmaY0sAK+Ojvl7IWVWkeUxr0C2njUsDUg54OZNFyg
E6X1TXG1xDIntbWbLC4e22s=

SQL>