I have an oracle database with some java stored procs. I am creating a dockerized version of the database for our developers to use. I can use oracle sql developer to copy all normal pl/sql procs to my local database but it doesnt export compiled java objects. The problem being the schema with the java procs is so old no one at my company seems to know where the original code is (or I would use loadjava to compile / load it). Is there a way to copy the java code from an existing database without accessing the original java source code?

Our DBA is back from vacation but still having trouble figuring out what permission is needed to read the classes via DBMS_JAVA. There is a ton of unclear documentation on the subject. Anyone know what dbms_java.grant_permission(...) calls are needed?

  • Can you back up the Java stored procedures using RMAN and then restore it on the other database?
    – MT0
    Sep 30 at 22:40
  • 1
    Stackoverflow is for programming questions. Admin questions can be better answered at dba.stachexchange.com. Sep 30 at 23:52

In the past I've used the following to extract the java class files from a database:

DROP TABLE java_class_blob ;

CREATE TABLE java_class_blob (
    longname varchar2 ( 2000 ),
    blob_object blob ) ;

CREATE FUNCTION temp_get_java_class (
    a_object_name varchar2,
    a_object_schema varchar2 )

    b blob ;

    dbms_lob.createtemporary ( b, false ) ;
    dbms_java.export_class ( a_object_name, a_object_schema, b ) ;
    RETURN b ;

INSERT INTO java_class_blob (
        blob_object )
    SELECT dbms_java.longname ( object_name ) AS longname,
            temp_get_java_class ( object_name, owner )
        FROM dba_objects
        WHERE object_type = 'JAVA CLASS'
            AND owner = 'TEMPO' ;


DROP FUNCTION temp_get_java_class ;

Followed by the extracting the blobs using:

#!/usr/bin/env perl

use strict;
use warnings;
use DBD::Oracle qw(SQLT_BIN ORA_BLOB);
use utf8;

my ( $database, $user ) = @ARGV;

my $dbh = get_db_connection($database, $user);
$dbh->{LongTruncOk} = 0;

my $obj_query = q{
SELECT longname,
        dbms_lob.getlength (blob_object)
    FROM java_class_blob

my $obj_list = $dbh->selectall_arrayref($obj_query);

my $blob_query = q{
    SELECT blob_object
        FROM java_class_blob
        WHERE longname = ?

foreach my $obj (@{$obj_list}) {
    my ($longname, $length) = @{$obj};
    $dbh->{LongReadLen} = $length;

    my $sth_obj = $dbh->prepare($blob_query);
    my ($blob) = $sth_obj->fetchrow();
    next unless ($blob) ;

    my $classfile = 'JAVA_CLASS/' . $longname . '.class';
    my $dirname = 'JAVA_CLASS/' . $longname;
    $dirname =~ s|/[^/]+$||;
    ( -d $dirname) || `mkdir -p $dirname` ;

    open my $fh, '>:raw', $classfile ;
    print $fh $blob;
    close $fh;

  • Thanks for the reply! Unfortunately Im not a DBA and dont have the permissions to run it in our test database. Our admin is on vacation forever and a day and dont want to wait for his return. using the above requires me to have some additional grants (dbms_java.grant_permission). I see there is a table (sys.idl_ub1$) that has the compiled byte code in hex but not sure how to get that into my local database. Is there some way to use loadjava + sys.idl_ub1$ to export/import the classes? I dont really need the source code - just need the procs to compile correctly in local DB. Thanks again!
    – George
    Oct 1 at 17:02
  • the contents of that table idl_ub1$ do seem to by a hex version of the bytecode. If I convert it to ascii it looks like an actual compiled class and I can see human readable strings like "java/lang/String;Ljava/sql/Connection;" mixed with a bunch of non readable characters. Not sure how to decompile it as the java decompiler I normally use errors out
    – George
    Oct 1 at 19:57

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.