asked on

Using Java class from Oracle 9i PL/SQL

Hi everyone,

I am trying to find the way to execute a DOS (XP) batch file from pl/sql store procedure. I am trying to use Java.

I have found the following sample over the internet:

1.
public class SysUtil {
public static void runBatch(String path) {
Runtime runtime = Runtime.getRuntime();
try {
runtime.exec(path);
} catch (Exception e) {
System.err.println(e.getMessage());
}
}
}

2.
-- Loading and calling Java class from Oracle

2.1.
C:\oracle9i\bin>javac SysUtil.java

2.2
C:\oracle9i\bin>loadjava -user scott/tiger SysUtil.class

3.
CREATE OR REPLACE PROCEDURE runbatch (path IN VARCHAR2)
AS LANGUAGE JAVA NAME 'SysUtil.runBatch (java.lang.String)';
/
CALL DBMS_JAVA.SET_OUTPUT (50);

BEGIN runbatch ('c:\any.bat'); END;
/

---------
I followed all steps specified in this sample. Java class has been compiled and successfully loaded into Oracle.
I created a procedure as instructed successfully as well.

But when I have tried to execute this procedure I got the following error:

ORA-29541: class POSINT.SysUtil could not be resolved
ORA-06512: at "sim.RUN_BATCH", line 0
ORA-06512: at line 2

Please advice how I can correct the problem.

Thanks and regards.

ASKER CERTIFIED SOLUTION

Sean Stuber

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Sean Stuber

could also be missing grants and synonyms.

If your sim pl/sql is trying to run posint java but can't find it because of a missing synonym you can either add a synonym, or move the java to the sim schema, or explicity refrence the posint schema.

If the pl/sql "can" find it but can't execute it, then grant execute on the posint java to sim.

GoodName

ASKER

Hello Sdstuber,

Thanks for your comments. Please ignore the name of the schemes in my sample (sim and posint).

I have tried to implement a java function on both.

In regards to privileges I used only

grant execute on runBatch to public;

I did not used any synonyms. What I am missing here?

GoodName

ASKER

Maybe there is something wrong with this java class.

Because when I tried from command line

loadjava -resolve -user scott/tiger@sim SysUtil.class

I got an error:

errors : class SysUtil
ORA-29545 : badly formed class...

Sean Stuber

the create and resolve code I sent above ran fine for me. try doing that instead of using loadjava.

change the schema to whatever you need.

If you are loading the java and the pl/sql into the same schema you don't need grants or synonyms

GoodName

ASKER

When I tried the way you described above it really worked without any errors. It was compiled successfully.

But when I tried to execute a batch file calling this class method it ran but did not do anything and did not generate any errors as well.

Sean Stuber

did you grant the java privileges?

GoodName

ASKER

I created a class and proc in the same schema.

What privileges should I add?

Sean Stuber

java doesn't allow you to do much by default.
You have to be given permission to execute each file.

DECLARE
KEYNUM NUMBER;
BEGIN
SYS.DBMS_JAVA.GRANT_PERMISSION(
grantee => 'SIM'
,permission_type => 'SYS:java.io.FilePermission'
,permission_name => 'c:\any.bat'
,permission_action => 'execute'
,key => KEYNUM
);
END;
/

other permissions you might need to grant...

SYS.DBMS_JAVA.GRANT_PERMISSION(
grantee => 'SIM'
,permission_type => 'SYS:java.lang.RuntimePermission'
,permission_name => '*'
,permission_action => 'writeFileDescriptor'
,key => KEYNUM
);

SYS.DBMS_JAVA.GRANT_PERMISSION(
grantee => SIM
,permission_type => 'SYS:java.io.FilePermission'
,permission_name => 'c:\some-file'
,permission_action => 'read'
,key => KEYNUM
);

<<ALL FILES>> is a key word that wildcards you to all files

GoodName

ASKER

I have done all as you said but with the same results. Maybe I am still missing something...

Sean Stuber

Let's see if you're having problems running the batch file or if it's something else....

create c:\any.bat with just one line....

echo "Hi there!" 1>> c:\anybat.log

Then assuming you've created the java and the pl/sql as sim, then log
in as sim and run

BEGIN runbatch ('c:\any.bat'); END;

GoodName

ASKER

Yes, I did this and after execution procedure nothing happened. Also no errors occurred.