JDBC access to Oracle9i Stored Procedure

P2 in number,
P3 out number );
 
Function myf_mult
(P1 in number,
P2 in number)
Return number;

Function myf_rtnrcd
(P_deptno in number)
Return t_rec; -- the return type of this function is cursor.

End; -- package spec

Package body mytestpkg1
Is
Procedure my_add
(P1 in number,
P2 in number,
P3 out number)
Is
Begin
P3: = p1 + P2;
End;

Function myf_mult
(P1 in number,
P2 in number)
Return number
Is
P3 number (8 );
Begin
P3: = p1 * P2;
Return P3;
End;

Function myf_rtnrcd
(P_deptno in number)
Return t_rec
Is
Myrec t_rec; -- Define a variable of the cursor type
Begin
Open myrec for select * from Dept where deptno = p_deptno;
Return myrec; -- return a cursor variable
End;

End;

Ii. JDBC program instance:

1. oracleconfig. Java:

Package com. jerryzhang. DBMS;

Public class oracleconfig
{
Private string drivername = "oracle. JDBC. Driver. oracledriver ";
Private string dburl = "JDBC: oracle: thin: @ localhost: 1521: ora901 ";
Private string user = "Scott ";
Private string Password = "tiger ";


Public oracleconfig ()
{
}

Public String getdrivername (){
Return drivername;
}
Public String getdburl (){
Return dburl;
}
Public String getuser (){
Return user;
}
Public String GetPassword (){
Return password;
}
}

 

2. class1.java:

Package com. jerryzhang. DBMS;
Import java. SQL .*;
Import oracle. JDBC. oracletypes; // you need to add Oracle Lib: class12.jar, nls_charset12.jar to the class path.
Public class class1
{
Public class1 ()
{
}
Public static void main (string ARGs []) throws exception {
Oracleconfig = new oracleconfig ();
Connection conn;
Statement stmt;
Resultset RS;
Class. forname ("oracle. JDBC. Driver. oracledriver ");
Conn = drivermanager. getconnection (oracleconfig. getdburl (), oracleconfig. getuser (), oracleconfig. GetPassword ());

// Statement
Stmt = conn. createstatement ();
Rs1_stmt.exe cutequery ("select * from Dept ");
While (Rs. Next ()){
System. Out. println (Rs. getstring (2 ));
}

// Callablestatement 1-call a procedure containing the out Parameter
Callablestatement cs = conn. preparecall ("{call mytestprc10 (?,?,?)} ");
CS. setstring (1, "10 ");
CS. setstring (2, "20 ");
CS. registeroutparameter (3, types. integer); // note the setting method of the returned parameters.

Cs.exe cuteupdate ();
Int T = cs. getint (3 );
System. Out. println ("callablestatement 1:" + t );
CS. Close ();

// Callablestatement 2-call a function that returns a numeric Parameter
Callablestatement CS2 = conn. preparecall ("{? = Call mytestpkg1.myf _ mult (?,?)} ");
Cs2.registeroutparameter (1, types. integer); // note the setting method of the returned parameters.
Cs2.setint (2, 2 );
Cs2.setint (3, 3 );
Cs2.executeupdate ();
Int t2 = cs2.getint (1 );
System. Out. println ("callablestatement 2:" + T2 );
Cs2.close ();

// Callablestatement 3-call a function that returns the cursor type parameter
Callablestatement CS3 = conn. preparecall ("{? = Call mytestpkg1.myf _ rtnrcd (?)} ");
Cs3.registeroutparameter (1, oracletypes. cursor); // note the setting method of the returned parameters, which is different from the preceding method.
Cs3.setint (2, 20 );
Cs3.executeupdate ();
Resultset rs3 = (resultset) cs3.getobject (1 );
Rs3.next ();
System. Out. println ("callablestatement 3:" + rs3.getstring (2 ));
Rs3.close ();
Cs3.close ();

Rs. Close ();
Stmt. Close ();
Conn. Close ();
}
}