-- The following script must be modified for your use.
-- It creates one SQL script on the OS for each object name
-- you return from a query (a package & its associated package body are 
-- grouped together into one script). 
--
-- Because ownership is important to this script, it is a good
-- idea to double check the database for objects that have mistakenly 
-- been compiled by the wrong owner, and thus duplicated into the wrong
-- schema. The following query will help to find those objects:

SELECT  a.owner AS ownerA, b.owner AS ownerB, b.object_name, b.object_type
  FROM DBA_OBJECTS a, DBA_OBJECTS b
 WHERE a.object_name = b.object_name
   AND a.object_type = b.object_type
   AND a.object_type IN ('PACKAGE BODY','PACKAGE','TRIGGER','PROCEDURE','FUNCTION')
   AND b.object_type IN ('PACKAGE BODY','PACKAGE','TRIGGER','PROCEDURE','FUNCTION')
   AND a.owner <> b.owner
   AND a.owner NOT IN ('SYS',  'PERFSTAT')
   AND b.owner NOT IN ('SYS',  'PERFSTAT');

-- Some important points:
--    The path1 constant should refer to a database directory object.
--    The list of objects is defined by the cursor c1.
--
-- The heart of this code (which is cursor c_source) was written by 
-- adding to a script created by Tom Kyte at asktom.oracle.com. 
-- I added in the logic necessary to include the object owner, which was important
-- for the task at hand.
--
-- ©2005 Edward Stoever 

DECLARE
   CURSOR c1
   IS
      SELECT DISTINCT NAME
                 FROM FTS_OBJECTS;

   var1   FTS_OBJECTS.NAME%TYPE;

--
-- BEGIN SUBPROGRAM THAT WRITES EACH FILE
--
   PROCEDURE write_file (source_name FTS_OBJECTS.NAME%TYPE)
   IS
      CURSOR c_source
      IS
         SELECT      DECODE (TYPE || '-' || TO_CHAR (line, 'fm99999'),
                             'PACKAGE BODY-1', '/' || CHR (10),
                             NULL
                            )
                  || DECODE (line,
                             1,  '-- OWNER: '
                              || owner
                              || CHR (10)
                              || 'create or replace ',
                             ''
                            )
                  || TRIM (CHR (10) FROM (DECODE (line,
                                                  1, REPLACE (UPPER (text),
                                                              UPPER (NAME),
                                                                 UPPER (owner)
                                                              || '.'
                                                              || UPPER (NAME)
                                                             ),
                                                  text
                                                 )
                                         )
                          )
             FROM DBA_SOURCE
            WHERE NAME = (source_name)
         ORDER BY TYPE, line;

      var_source       VARCHAR2 (4100);
      path1   CONSTANT VARCHAR2 (15)           := 'FTS$DBPROCS';
      -- uses directory called EDWARD
      output_file      UTL_FILE.file_type;
      filename         FTS_OBJECTS.NAME%TYPE;
   BEGIN
      filename := SUBSTR (source_name, 1, 25) || '.SQL';
      output_file := UTL_FILE.fopen (path1, filename, 'W');

      OPEN c_source;

      LOOP
         FETCH c_source
          INTO var_source;

         EXIT WHEN c_source%NOTFOUND;
         UTL_FILE.put_line (output_file, var_source);
      END LOOP;

      CLOSE c_source;

      UTL_FILE.put_line (output_file, '/');
      UTL_FILE.fclose (output_file);
   END;
--
-- END SUBPROGRAM THAT WRITES EACH FILE
--
BEGIN
   OPEN c1;

   LOOP
      FETCH c1
       INTO var1;

      EXIT WHEN c1%NOTFOUND;
      write_file (var1);
   END LOOP;

   CLOSE c1;
EXCEPTION
   WHEN OTHERS
   THEN
      RAISE_APPLICATION_ERROR (-20001, 'error on object: ' || var1);
END;
/