Re: Java Procedure Doesn't Exist When Executed

From: Rauf Sarwar <rs_arwar_at_hotmail.com>
Date: 13 Jun 2006 07:19:11 -0700
Message-ID: <1150208351.067929.252770@f6g2000cwb.googlegroups.com>

Resant wrote:
> I've add the permission by script :
>
> dbms_java.grant_permission( 'SCOTT', 'SYS:java.io.FilePermission',
> '<<ALL FILES>>', 'execute' )
>
> and try to select again, but get error :
> ERROR at line 1:
> ORA-29532: Java call terminated by uncaught Java exception:
> java.io.IOException: The handle is invalid.
>
> Something wrong with the Java procedure?
>
> Thanks
>
> Resant

To add to Michael's corrections... you should make an effort to learn some basics about java, built in java security mechanism and the OS where your Oracle database is running. Without this, you will be shooting in the dark. Goto http://java.sun.com and download java docs.

1. When you deploy java code into Oracle, you have to consider the security mechanism of both Oracle and JVM. Unless your code is just a learning exercise, you are allowing user to run any OS command via the Runtime object. Do you really want to have this code in a Production system?
2. Shell commands run differently on Windows and *nix. If you want to run your code on *nix then you already have access to shell e.g. from your code it would be ('ls') for directory listings, however; on Windows, you have to run the shell command inside cmd.exe. Try ('cmd /c dir') to avoid the "invalid handle" error.
3. If you want to view the output of the command being executed then you have to get hold of the Process.getInputStream() and loop thru it to read it line by line. Before calling the java procedure, call dbms_java.set_output() to capture the System.out.println statements in sqlplus.

In the following simple example, I have three 0 byte files in c:\temp and I have granted execute on javasyspriv role to system.

C:\>dir /b c:\temp

file1.txt
file2.txt
file3.txt

C:\>sqlplus system

SQL*Plus: Release 9.2.0.7.0 - Production on Tue Jun 13 14:11:50 2006

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

Enter password:

Connected to:
Oracle9i Enterprise Edition Release 9.2.0.7.0 - Production With the Partitioning, OLAP and Oracle Data Mining options JServer Release 9.2.0.7.0 - Production

SQL> create or replace and compile java source   2 named "TestIO"
  3 as
  4 import java.io.*;
  5
  6 public class TestIO {
  7

  8     public static void testio (String cmd)
  9        throws Exception {
 10
 11        Runtime r = Runtime.getRuntime();
 12        Process p = r.exec(cmd);
 13
 14        BufferedReader br = new BufferedReader(new
InputStreamReader(p.getInputStream()));
 15        String s;
 16        while ((s = br.readLine()) != null) {
 17           System.out.println(s);
 18        }
 19        br.close();
 20        p.waitFor();
 21     }

 22 }
 23 /

Java created.

SQL> create or replace procedure testio_wrapper (   2 cmd_ in varchar2 )
  3 as
  4 language java name 'TestIO.testio(java.lang.String)';   5 /

Procedure created.

SQL> set serverout on
SQL> exec dbms_java.set_output(20000);

PL/SQL procedure successfully completed.

SQL> exec testio_wrapper('cmd /c dir /b c:\temp');

file1.txt
file2.txt
file3.txt

PL/SQL procedure successfully completed.

SQL> exec testio_wrapper('cmd /c del c:\temp\file1.txt');

PL/SQL procedure successfully completed.

SQL> exec testio_wrapper('cmd /c dir /b c:\temp'); file2.txt
file3.txt

PL/SQL procedure successfully completed.

Regards
/Rauf Received on Tue Jun 13 2006 - 09:19:11 CDT