Frist step in creating Oracle stored procedures in Java requires following steps
1. Create Java Classes
2. Establish oracle connection in the Java Classes
3. Add the Java class in Oracle using LoadJava
3. Create Oracle Stored procedures that use these classes
Basic Example :
Example 1
In the following example, the method
main accepts the name of a database table (such as 'emp') and an optional WHERE clause condition (such as 'sal > 1500'). If you omit the condition, the method deletes all rows from the table. Otherwise, the method deletes only those rows that meet the condition.import java.sql.*; import oracle.jdbc.*; public class Deleter { public static void main (String[] args) throws SQLException { Connection conn = DriverManager.getConnection("jdbc:default:connection:"); String sql = "DELETE FROM " + args[0]; if (args.length > 1) sql += " WHERE " + args[1]; try { Statement stmt = conn.createStatement(); stmt.executeUpdate(sql); stmt.close(); } catch (SQLException e) {System.err.println(e.getMessage());} } }
The method
main can take either one or two arguments. Normally, the DEFAULT clause is used to vary the number of arguments passed to a PL/SQL subprogram. However, that clause is not allowed in a call spec. So, you must overload two packaged procedures (you cannot overload top-level procedures), as follows:CREATE OR REPLACE PACKAGE pkg AS PROCEDURE delete_rows (table_name VARCHAR2); PROCEDURE delete_rows (table_name VARCHAR2, condition VARCHAR2); END; CREATE OR REPLACE PACKAGE BODY pkg AS PROCEDURE delete_rows (table_name VARCHAR2) AS LANGUAGE JAVA NAME 'Deleter.main(java.lang.String[])'; PROCEDURE delete_rows (table_name VARCHAR2, condition VARCHAR2) AS LANGUAGE JAVA NAME 'Deleter.main(java.lang.String[])'; END;
Now, you are ready to call the procedure
delete_rows:SQL> CALL pkg.delete_rows('emp', 'etype = ''Manager'''); Call completed. SQL> SELECT ename, sal FROM emp; EID ENAME DEPT ETYPE ---------- -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ------------------------- ------------------------------ 2 Vaishali Shetty Technology Tester 3 Shama Raman Design Designer 4 Asmita Gharpure Travel Tickiting Agent 5 Trupti S Operations Accountant 6 Genevive Dias Travel Ticketing Agent 7 rows selected.
---------------------------------------------------------
CREATE TABLE EMP (
EID NUMBER,
ENAME VARCHAR2(200),
DEPT VARCHAR2(25),
ETYPE VARCHAR2(30));
EID NUMBER,
ENAME VARCHAR2(200),
DEPT VARCHAR2(25),
ETYPE VARCHAR2(30));
INSERT INTO EMP VALUES (1, 'Varsha Shenoy', 'Travel', 'Manager');
INSERT INTO EMP VALUES (2, 'Vaishali Shetty', 'Technology', 'Tester');
INSERT INTO EMP VALUES (3, 'Shama Raman', 'Design', 'Designer');
INSERT INTO EMP VALUES (4, 'Asmita Gharpure', 'Travel', 'Tickiting Agent');
INSERT INTO EMP VALUES (5, 'Trupti S', 'Operations', 'Accountant');
INSERT INTO EMP VALUES (6, 'Genevive Dias', 'Travel', 'Ticketing Agent');
INSERT INTO EMP VALUES (7, 'Poornima Redla', 'Marketing', 'Manager');
---------------------------------------A prollogue about establishing connection in the Database
Select your driver type : thin, oci, kprb...
Oralce provides four types of JDBC driver.- Thin Driver, a 100% Java driver for client-side use without an Oracle installation, particularly with applets. The Thin driver type is
thin. To connect userscottwith passwordtigerto a database withSID(system identifier)orclthrough port 1521 of hostmyhost, using the Thin driver, you would write :Connection conn = DriverManager.getConnection ("jdbc:oracle:thin:@myhost:1521:orcl", "scott", "tiger"); - OCI Driver for client-side use with an Oracle client installation. The OCI driver type is
oci. To connect userscottwith passwordtigerto a database withSID(system identifier)orclthrough port 1521 of hostmyhost, using the OCI driver, you would write :
Note that you can also specify the database by aConnection conn = DriverManager.getConnection ("jdbc:oracle:oci:@myhost:1521:orcl", "scott", "tiger");TNSNAMESentry. You can find the availableTNSNAMESentries listed in the filetnsnames.oraon the client computer from which you are connecting. For example, if you want to connect to the database on hostmyhostas userscottwith passwordtigerthat has aTNSNAMESentry ofMyHostString, enter:
If your JDBC client and Oracle server are running on the same machine, the OCI driver can use IPC (InterProcess Communication) to connect to the database instead of a network connection. An IPC connection is much faster than a network connection.Connection conn = DriverManager.getConnection ("jdbc:oracle:oci8:@MyHostString","scott","tiger");Connection conn = DriverManager.getConnection ("jdbc:oracle:oci8:@","scott","tiger"); - Server-Side Thin Driver, which is functionally the same as the client-side Thin driver, but is for code that runs inside an Oracle server and needs to access a remote server, including middle-tier scenarios. The Server-Side Thin driver type is
thinand there is no difference in your code between using the Thin driver from a client application or from inside a server.
- Server-Side Internal Driver for code that runs inside the target server, that is, inside the Oracle server that it must access. The Server-Side Internal driver type is
kprband it actually runs within a default session. You are already "connected". Therefore the connection should never be closed.
To access the default connection, write:
You can also use the Oracle-specific defaultConnection() method of the OracleDriver class which is generally recommended:DriverManager.getConnection("jdbc:oracle:kprb:");or:DriverManager.getConnection("jdbc:default:connection:");OracleDriver ora = new OracleDriver(); Connection conn = ora.defaultConnection();
http://docs.oracle.com/cd/E11882_01/appdev.112/e13995/oracle/jdbc/OracleDriver.html
https://docs.oracle.com/cd/B10501_01/java.920/a96659/04_call.htm#12072
