2016-12-20

Copying java classes to another database

1 Export java classes using dbms_metadata.
It doesn’t matter are you set SQLTERMINATOR=TRUE or FALSE:

dbms_metadata.set_transform_param(dbms_metadata.session_transform, 'SQLTERMINATOR', true);

results will be incorrect in any cases and you should to transform received files using your favorite text editor or other tool.

To export java classes you should execute dbms_metadata.get_ddl with parameters (please notice the underscore in JAVA_CLASS)

SELECT dbms_metadata.get_ddl('JAVA_CLASS',dbms_java.longname(object_name), USER) 
FROM user_objects o 
WHERE object_type = 'JAVA CLASS'

2 Transform sql files from previous step. I have the following problems:
- lack of slashes. If you use SQLTERMINATOR=true on previous step, you get only one / at the end of the script, but unfortunately totally unusable because of leading spaces. So, I use SQLTERMINATOR=false and add slashes by regexp in Notepad++

[ ]+END;(\n|\z) --> END;\n/\n
With Regular expression radio button
  • long lines: sql*plus I got an error SP2-0027: Input is too long (> 2499 characters) in lines with DBMS_JAVA.IMPORT_RAW_CHUNK(HEXTORAW(…
    I split these lines with regexp
('[^']{2400})(.*')  --> \1'\n|| '\2
With Regular expression radio button

Classes will be created in connected user schema. If this schema is different, that in source database, you should to change these strings

STATUS := DBMS_JAVA.IMPORT_TEXT_CHUNK('(("*" "__NEW_OWNER_HERE__")("*" "PUBLIC"))', 34);

and change the second parameter 34 to length in chars of the first parameter

length('(("*" "__NEW_OWNER_HERE__")("*" "PUBLIC"))')

else you’ll get a lot of errors like this

SQL> alter java class "MDSYS"."/4e9308f3_BMPWriter" compile;

Warning: Java altered with compilation errors.

SQL> show errors java class "MDSYS"."/4e9308f3_BMPWriter"
Errors for JAVA CLASS "MDSYS"."/4e9308f3_BMPWriter":

LINE/COL ERROR
-------- -----------------------------------------------------------------
0/0 ORA-29521: referenced name com/sun/media/jai/codec/BMPEncodeParam could not be found
0/0 ORA-29521: referenced name com/sun/media/jai/codec/ImageEncodeParam could not be found
0/0 ORA-29521: referenced name com/sun/media/jai/codec/ImageEncoder could not be found
0/0 ORA-29521: referenced name com/sun/media/jai/codec/ImageCodec could not be found

It’s interesting, that show errors for java classes works in Sql*Plus only.

3 After running the scripts you should to recompile or re-resolve imported classes. I did it with script:

-- resolve classes to new schema
SET SERVEROUTPUT ON SIZE 1000000
DECLARE 
  l_invalid_cnt NUMBER := 0;
  l_prev_invalid_cnt NUMBER := 0;
  FUNCTION get_invalid_cnt RETURN NUMBER IS 
    RESULT NUMBER;
  BEGIN
    SELECT COUNT(*) INTO RESULT FROM user_objects  WHERE object_type = 'JAVA CLASS' AND status = 'INVALID';
    RETURN RESULT;
  END;
BEGIN
  l_invalid_cnt := get_invalid_cnt();
  WHILE l_invalid_cnt > 0 AND l_invalid_cnt <> l_prev_invalid_cnt LOOP
    FOR rec IN (SELECT dbms_java.longname(object_name) NAME FROM user_objects  WHERE object_type = 'JAVA CLASS' AND status = 'INVALID') LOOP
      BEGIN
        EXECUTE IMMEDIATE 'alter java class "' || rec.name || '" resolver ((* ' || USER || ')(* PUBLIC)) resolve';
      EXCEPTION WHEN OTHERS THEN NULL;
      END;
    END LOOP;
    l_prev_invalid_cnt := l_invalid_cnt;
    l_invalid_cnt := get_invalid_cnt();    
  END LOOP;
  dbms_output.put_line('Finish Invalid count = ' ||   l_invalid_cnt); 
END;  
/

PROMPT ================================
PROMPT Invalid classes
PROMPT ================================
SELECT dbms_java.longname(object_name) NAME FROM user_objects  WHERE object_type = 'JAVA CLASS' AND status = 'INVALID';

I never use WHEN others THEN NULL; but in this case I’ve got strange errors without error code in PL/SQL Developer. So I have to wrap executions.
I think you can use more simple

alter java class ... compile; -- or
alter java class ... resolve;

without specifying resolver ((* ' || USER || ')(* PUBLIC))
But code above shows, how you can solve ORA-29521 errors. You can resolve reference to any schema you want (by default it’ll be resolved to current schema + public).
To check resolving schema name you can use the following query

select * from user_java_resolvers;

Комментариев нет: