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 ...
Get Oracle and PL/SQL Recipes: A Problem-Solution Approach now with the O’Reilly learning platform.
O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.