Oracle and PL/SQL Recipes: A Problem-Solution Approach

4-20. Viewing Source Code for Stored Programs

Problem

You want to retrieve the code for your stored functions, procedures, triggers, and packages.

Solution

Use the DBMS_METADATA package to assist you in fetching the information. In this case, you will use the DBMS_METADATA.GET_DDL procedure to obtain the code for a stored function. In the following code, the DBMS_METADATA package is used to return the DDL for the CALC_QUARTER_HOUR function:

SELECT DBMS_METADATA.GET_DDL('FUNCTION','CALC_QUARTER_HOUR') FROM DUAL;

The query illustrated previously should produce results that are similar to the following as long as you have the CALC_QUARTER_HOUR function loaded in your database:

CREATE OR REPLACE FUNCTION "MY_SCHEMA"."CALC_QUARTER_HOUR" (HOURS ...