Extract Procedure,Package,Package Body,Function in Oracle

This script will extract source code for the following source types:

  • 1. PACKAGE
  • 2. PACKAGE BODY
  • 3. FUNCTION
  • 4. PROCEDURE
  • The source code will be spooled into a file named after the source name.
    set scan on
    set pages 0
    set feedback off
    set heading off
    set verify off
    set lines 300
    set trims on
    
    set term on
    prompt +--------------------------------------------+
    prompt DESCRIPTION:
    prompt ============
    prompt This script will extract source code for the following source types:
    prompt 1. PACKAGE
    prompt 2. PACKAGE BODY
    prompt 3. FUNCTION
    prompt 4. PROCEDURE
    prompt The source code will be spooled into a file named after the source name
    prompt USAGE:
    prompt =====
    prompt follow the prompts ...
    prompt +---------------------------------------------+
    accept proc_name  prompt "Enter Procedure Or Package Name: "
    accept proc_owner prompt "Enter The Owner Name: "
    accept type prompt "Enter type Of the source [PACKAGE, PACKAGE BODY, PROCEDURE, FUNCTION]: "
    set term off
    
    SELECT DECODE(ROWNUM,1,'CREATE OR REPLACE '||text,text)
    FROM   dba_source
    WHERE  name  = UPPER('&&proc_name')
    AND    owner = UPPER('&proc_owner')
    AND    type  = UPPER('&type')
    ORDER BY line
    
    spool &&proc_name..sql
    /
    prompt /
    spool off
    
    ed &&proc_name
    
    undefine proc_name