SELECT DBMS_METADATA.get_ddl(object_type, object_name, user) AS object_ddl

FROM user_objects

WHERE object_type IN ('PROCEDURE', 'FUNCTION', 'PACKAGE', 'PACKAGE BODY')

AND status = 'VALID';

-- Example script for extracting stored procedure source code

SELECT name, type, line, text

FROM all_source

WHERE owner = 'YOUR_SCHEMA_NAME'

AND type IN ('PROCEDURE', 'FUNCTION', 'PACKAGE', 'PACKAGE BODY')

ORDER BY name, type, line;