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

Query all_source

   <source lang="sql">

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: --</source>

Query all_source for all packages

   <source lang="sql">

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.</source>

Query all_source for certain string

   <source lang="sql">

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></source>

Query all_source table for all package bodies

   <source lang="sql">

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></source>