Java Stored Procedure in Oracle, Database Interaction - How to execute the Java Stored Procedures
(Page 5 of 5 )
We have developed, loaded, and published our Java classes. The final step is to execute them. The following statements would make you see the Java based output.
SQL> SET SERVEROUTPUT ON
SQL> CALL dbms_java.set_output(2000);
SQL> EXECUTE add_emp(1,'Jag',40000.00,1);
Creating new employee...
PL/SQL procedure successfully completed.
The first two statements are necessary one time (after the first login), because it allows the redirection of tracing.
As you can see, from the caller's perspective, there is no difference between calls made to Java stored procedures and calls to a PL/SQL procedure or function.
VARIABLE x NUMBER;
CALL getEmpCountByDept(1) INTO :x;
Getting Number of Employees for Dept...
Call completed.
PRINT x
X
----------
1
The SQLException class has the getErrorCode() and getErrorMessage() methods to help report errors. Any uncaught exception in a Java Stored Procedure results in an 'ORA-29532 Java call terminated by uncaught Java exception' for the caller. How you choose to handle errors will vary by application. The addEmp method simply catches and displays the error. We receive an error message when we attempt to add an emp record with an invalid dept_id.
SQL> execute addEmp(2,'aaa', 45000.00,2);
Creating new employee...
ERROR! Adding Employee : ORA-02291: integrity constraint
(OPS$AK4353.FK_DEPT_ID) violated -
parent key not found
Because there is a need to call Java from PL/SQL, it is reasonable to assume that we will also need a way to call PL/SQL from Java code. This is very easy to achieve by using a CallableStatement object in our Java methods.
CallableStatement cstmt = conn.prepareCall("{your procedure}");
Thus, it is possible to create a seamless environment of PL/SQL procedures calling Java and vice versa.
Summary
Even though I examined only a stored procedure in the above scenario, you can even embed those Java stored procedures in PL/SQL Packages. Don't consider only Java stored procedures; there also exists the concept of .NET based stored procedures (primarily called .NET CLR stored procedures). .NET based stored procedures are supported by both SQL Server 2005 and Oracle 10g Version 10.2. Remember, it is possible to create a seamless environment of PL/SQL procedures calling Java and vice versa.
| DISCLAIMER: The content provided in this article is not warranted or guaranteed by Developer Shed, Inc. The content provided is intended for entertainment and/or educational purposes in order to introduce to the reader key ideas, concepts, and/or product reviews. As such it is incumbent upon the reader to employ real-world tactics for security and implementation of best practices. We are not liable for any negative consequences that may result from implementing any information covered in our articles or tutorials. If this is a hardware review, it is not recommended to open and/or modify your hardware. |
/ 21 
