Solved

Using Java class from Oracle 9i PL/SQL

Posted on 2007-11-25
14
774 Views
Last Modified: 2013-12-19
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.
0
Comment
Question by:GoodName
  • 6
  • 6
14 Comments
 
LVL 73

Accepted Solution

by:
sdstuber earned 500 total points
ID: 20348438
not sure where "POSINT" came from

based on your pl/sql declaration, I'd say

SysUtil should be loaded into the "sim" schema.

You can do that with this...

CREATE OR REPLACE AND RESOLVE JAVA SOURCE NAMED sim."SysUtil" as
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());
        }
        }
 }


or you can loadjava into the sim schema
0
 
LVL 73

Expert Comment

by:sdstuber
ID: 20348449
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.
0
 

Author Comment

by:GoodName
ID: 20351019
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?
0
 

Author Comment

by:GoodName
ID: 20351142
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...
0
 
LVL 73

Expert Comment

by:sdstuber
ID: 20351204
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
0
 

Author Comment

by:GoodName
ID: 20353291
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.
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 73

Expert Comment

by:sdstuber
ID: 20353295
did you grant the java privileges?
0
 

Author Comment

by:GoodName
ID: 20353652
I created a class and proc in the same schema.

What privileges should I add?
0
 
LVL 73

Expert Comment

by:sdstuber
ID: 20353814
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

0
 

Author Comment

by:GoodName
ID: 20354116
I have done all as you said but with the same results. Maybe I am still missing something...
0
 
LVL 73

Expert Comment

by:sdstuber
ID: 20357798
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;
0
 

Author Comment

by:GoodName
ID: 20370255
Yes, I did this  and after execution procedure nothing happened. Also no errors occurred.
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Convert Oracle data into XML document 2 55
How to return an OUT parameter from and ORACLE 3 56
report returning null 21 79
Oracle Pivot Question 8 44
Subquery in Oracle: Sub queries are one of advance queries in oracle. Types of advance queries: •      Sub Queries •      Hierarchical Queries •      Set Operators Sub queries are know as the query called from another query or another subquery. It can …
This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.

911 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now