Thursday, September 15, 2011

Oracle Datapump Export and Import to New Schema using schema_remap and the Affects on Java Stored Procedures

The situation:

Datapump export on Schema Named A. Datapump import to Schema Named B.  Schema A contains Java Stored Procedures. After importing to Schema B you get errors stating that the Java Stored Procedures cannot be resolved within Schema B.

A little PL/SQL script I wrote to run against Schema B after import to correct:

DECLARE
   CURSOR java_class_names
   IS
      SELECT name FROM user_java_classes;

   v_name   user_java_classes.name%TYPE;
BEGIN
   OPEN java_class_names;

   LOOP
      FETCH java_class_names INTO v_name;

      EXIT WHEN java_class_names%NOTFOUND;

      BEGIN
         EXECUTE IMMEDIATE 'alter java class "' || v_name 
           || '" resolver ((* B) (* public)) resolve' ;
      EXCEPTION
         WHEN OTHERS
         THEN
            DBMS_OUTPUT.put_line 
               ('catch comp errors due to dependencies');
      END;
   END LOOP;

   CLOSE java_class_names;
EXCEPTION
   WHEN OTHERS
   THEN
      IF java_class_names%ISOPEN
      THEN
         CLOSE java_class_names;
      END IF;

      RAISE;
END;

This updates the Java Class Resolver to Schema B. This should be handled by the schema_remap parameter on impdp, but unfortunately is not.

0 comments: