Skip to Main Content

Use Java to call PL/SQL - OS level

A working example on CentOS 6, for calling a PL/SQL subprogram from a Java program.

I got most of this from http://everythingoracle.com/plsqlfrjv.htm. Have a look there for more detailed info. I fixed some typos and added some info on java paths that worked for my setup.

This example is for Oracle DB 12c, it should work for just about any version I think (including XE).


Set the Classpath

Note: I use /tmp as the place to put my Java files, and the /u01... path is where the Oracle jdbc is located - change if needed.
Note: The example on the linked page above uses a ; instead of : in the classpath- that doesn't work (for me anyway).
export CLASSPATH=$CLASSPATH:/tmp:/u01/app/oracle/product/12.1.0/db_1/jdbc/lib/ojdbc6.jar

Create the PL/SQL Subprogram


Sample Java Program

cd /tmp
vi PLSQLFromJava.java

Compile Java Program

Note: /opt/jdk1.8.0_66/bin/ is the place where the javac program is located on my server - change if needed.
/opt/jdk1.8.0_66/bin/javac PLSQLFromJava.java

Run Java Program

java PLSQLFromJava
Output:
This is the value set for parm2
The value received for parm1 is: <input value>

Use Java to call PL/SQL - Database level

This is a quite frustrating topic... All I needed was a small Java stored procedure to listen on UDP port 1813, after which I would do something useful with the data.
I did get that to work on Oracle 12c with Windows 8.1, but the exact same setup will not work on Oracle 12c with CentOS 6. And of course I needed CentOS, not Windows.
No idea why the OS makes any difference; it's not a firewall or SELinux issue, since disabling both doesn't make a difference. And any other kind of UDP traffic from and to the CentOS server (using e.g. SocketTest) works like a charm. But actually reaching the database? No way.

Most of the example below I found at http://www.java2s.com/Code/Java/Network-Protocol/ReceiveUDPpockets.htm.
I did change it a bit to suit my needs, and added some essential steps (like restarting listener and database, finding that one took a few hours, grrr...).
The database schema I use in this example is ESB_OWNER, and I use UDP port 1813.

Socket Permission

As sys:

Restart

Restart database and Listener.
Note: Not sure if you can get away with only restarting the listener; you might.
Note: You can see the permissions have been granted using select * from dba_java_policy where grantee = 'ESB_OWNER'; but you have to restart for it to actually work. That took a few hours to figure out... thanks for the tip Henk!

Table

As ESB_OWNER:

Java Source

As ESB_OWNER:

PL/SQL Package

As ESB_OWNER:

Test


And here you would expect to receive data, when sending some to the server using e.g. SocketTest. And on Windows you will. Not on CentOS.
You can see in DBMS Output that the procedure reaches step 5, and there it stays stuck on the receive call.

Finally I just abandoned this approach, and went for the OS Level solution at the top of the page. I spent way too much time on this, and still have no real clues as to what the issue is.
Some Google results suggest it may have to do with the way CentOS (and other RedHat Linux flavors) configure network adapters. Maybe you'd have to force the Java program and/or the OS to listen on the correct socket or IP (something vague anyway, which Windows does automatically).
If you have a fix for this please let me know, I'd love to hear it. And I'll give you all the credit :-)

Links

Some of the pages I visited during the quest to get this to work in the database itself. Like I said, this took way too long...:

The DayneO blog: Simplest way to debug your Oracle Java Stored Procedure
PATH and CLASSPATH (The Java™ Tutorials > Essential Classes > The Platform Environment)
Reading from and Writing to a Socket (The Java™ Tutorials > Custom Networking > All About Sockets)
sending and receiving UDP packets using Java? - Stack Overflow
java - How can I convert a stack trace to a string? - Stack Overflow
java - DatagramSocket Broadcast Behavior (Windows vs. Linux) - Stack Overflow
Enabling java security manager limits udp sockets count - Stack Overflow
Java DatagramSocket is able to receive packets on windows but not linux - Stack Overflow
networking - How can you find out which process is listening on a port on Windows? - Stack Overflow
difference between "address in use" with bind() in Windows and on Linux - errno=98 - Stack Overflow
java - javac : command not found - Stack Overflow
Udp socket programming in Java
Linux: Find Out Which Process Is Listening Upon a Port
ORA-29532: java call terminated by uncaught java exception: string tips
dbms_java Tips
Receive UDP pockets : UDP « Network Protocol « Java
Java Tutorial DatagramSocket
OraFAQ Forum: SQL & PL/SQL » Java network problem within 10g
UDP Sender and Receiver. | Oracle Community
Simple Socket Problem ( ? ) | Oracle Community
How to unbind DatagramSocket | Oracle Community
UDP Server hangs | Oracle Community
java.net.BindException: Address already in use | Oracle Community
Java stored procedure in oracle | Oracle Community
Calling Stored Procedures
SocketPermission (Java Platform SE 7 )
User Datagram Protocol - Wikipedia, the free encyclopedia
Packet Sender - Download
SocketTest - Test My Socket download | SourceForge.net
A Simple Java UDP Server and UDP Client | systemBash