Standardizing on a common set of technologies and programming languages can help companies reduce variability, decrease costs, and increase reuse. However, with the abundance of content and data management tools available, agile organizations will look to augment their standardized systems with supporting technologies and programming languages to decrease development time and optimize efficiencies. PL/SQL (Procedural Language/Structured Query Language) is Oracle Corporation’s procedural extension language for SQL and the Oracle relational database. PL/SQL can be used when building and managing complex database procedures, but it cannot handle more difficult operations such as connecting to other systems like Oracle WebCenter UCM services to pull or push data. However, making those connection available can be done with Java. While creating a connection in Java is well documented, loading and running Java in PL/SQL is not. Here is an example of loading and running Java in PL/SQL:
To get started create a java file with the following code.
public class SayStuff {
public static String sayThis(String arg) {
return “Parrot says:” + arg;
}
}
In order run the above method with PLSQL you’ll need to load it’s dependencies into the database. This is done by running the loadjava method. Make sure that you load your dependencies in the correct order as some may be dependent on others. Alternatively, you can also load your java class files after they have been compiled. Be aware that this will cause assertion errors if the java versions are incompatible. Here is an example:
loadjava -user <username>/<password> SayStuff.java
(You can find more information on loadjava here: http://www.oracleutilities.com/OSUtil/loadjava.html)
Once all the java files are loaded they will be visible from the dba_objects view with an object type of JAVA CLASS. Finally you’ll need to create a PLSQL function to be able to run your java method. This is done by running the following PLSQL code:
CREATE OR REPLACE FUNCTION sayThis
(id VARCHAR2)
RETURN VARCHAR2
AS LANGUAGE JAVA
NAME ‘com.fishbowl.sayThis(java.lang.String)
return java.lang.String’;
Now you’re ready to run the function, an example is shown below:
select sayThis(‘Hello World!’) from dual;
Parrot says: Hello World!
This simple bit of script will enable you to run any java function from PLSQL and a few minor changes, you will be able to create network connections that will help your company keep up with quick changing front-end user interfaces that need more from PLSQL.
