Home » Developer & Programmer » JDeveloper, Java & XML » How to get String and Integer values out from an Oracle OUT Parameter?
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];
}
Previous Topic: Quering in join tables with contains (too slow!)
Next Topic: New to Jdeveloper: i want to develop a web page using ASP.
Goto Forum:
  


Current Time: Sat Dec 03 20:16:16 CST 2016

Total time taken to generate the page: 0.08144 seconds