A very interesting topic came to me out of this experience.
Have you ever tried to get a list of all stored procedures and functions in Oracle schema? Preaty easy,
SELECT * FROM USER_PROCEDURES;
You figured out that it is quite different comparing to SQL Server and MySQL where with the single query
SELECT * FROM INFORMATION_SCHEMA.ROUTINES;
you get a body definition as well.
To achieve this on Oracle is not so simply. First, to get user sources you should query USER_SOURCE view where each function or stored procedure line is stored in one row.
To get a full body, you have to use a powerful hierarchical query clause
SYS_CONNECT_BY_PATH.
Here is a query which returns you list of all stored procedures and functions in Oracle schema together with their bodies defined in one column:
SELECT routine_name, y.TYPE, TRANSLATE(LTRIM(x.text1, '/'), '/', ' ') routine_definition
FROM (SELECT name routine_name, LEVEL lvl, sys_connect_by_path(text, '/') text1
FROM USER_SOURCE
CONNECT BY LINE - 1 = PRIOR LINE AND name = PRIOR name) x,
(SELECT name, TYPE, MAX(line) AS maxline
FROM USER_SOURCE
GROUP BY name, TYPE) y
WHERE x.routine_name = y.name AND x.lvl = y.maxline
ORDER BY TYPE, routine_name
Compare database schema and data fast and easy with DBTYP.NET Studio
