| How to get String and Integer values out from an Oracle OUT Parameter? [message #228413] |
Mon, 02 April 2007 23:53 |
Dalomi
Messages: 4
Registered: February 2007
|
Junior Member |
|
|
Hi Everyone,
May someone please help with getting String and Integer values out from an Oracle OUT parameter? I don't know how to map the values so that my java class understands. Below is my code. Your help is really appreciated!
Thanks,
Dalomi
-------------------------------------------------
-- TYPES
-------------------------------------------------
CREATE OR REPLACE TYPE employee_name AS OBJECT (
employee_id number(5),
first_name varchar2(30),
last_name varchar2(40),
age number(3)
);
CREATE OR REPLACE TYPE employee_names IS TABLE OF employee_name;
-------------------------------------------------
-- PACKAGE AND PROCEDURE
-------------------------------------------------
CREATE OR REPLACE PACKAGE BODY employees
AS
PROCEDURE get_employees
(
o_employees OUT employee_names
)
IS
v_employees employee_names := employee_names();
BEGIN
employee_names.extend();
employee_names(1) := employee_name(55555, 'John', 'Doe', 42);
employee_names.extend();
employee_names(2) := employee_name(66666, 'Mary', 'Jones', 38);
o_employees := v_employees;
END;
END employees;
-------------------------------------------------
-- JAVA
-------------------------------------------------
DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver());
Connection conn = DriverManager.getConnection ("jdbc:oracle:thin:@ComputerDev01:Staff", "tiger", "scott");
conn.setAutoCommit (false);
OracleCallableStatement ocs = (OracleCallableStatement)conn.prepareCall("{ call employees.get_employees(?) }");
ocs.registerOutParameter(1, OracleTypes.ARRAY, "EMPLOYEE_NAMES");
ocs.execute();
Object[] objEmployees = (Object[]) ocs.getARRAY(1).getArray();
for (int i=0; i<objEmployees.length; i++)
{
Struct objEmployee = (Struct) partialMatches[i];
Object v_employee_id = objEmployee.getAttributes()[0];
String v_first_name = (String)objEmployee.getAttributes()[1];
String v_last_name = (String)objEmployee.getAttributes()[2];
Object r_middle_name = objEmployee.getAttributes()[3];
}
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
