Home » Developer & Programmer » JDeveloper, Java & XML » Is it possible to retrieve an array of pl/sql record which contain DATE elements in Java (11.2.0.3)
| Is it possible to retrieve an array of pl/sql record which contain DATE elements in Java [message #636975] |
Thu, 07 May 2015 00:55  |
kytemanaic
Messages: 55
Registered: February 2009
|
Member |
|
|
Dear all,
my ultimate objective is to retrieve an array of pl/sql records which contains Date element by calling a pl/sql procedure or pl/sql function in Java.
http://info.michael-simons.eu/2012/07/24/how-to-retrieve-tables-of-custom-object-types-with-jdbc/
CREATE TABLE some_table
(
some_number NUMBER,
some_string varchar2(32)
)
/
INSERT INTO some_table (some_number, some_string) VALUES (1 ,'One');
INSERT INTO some_table (some_number, some_string) VALUES (2 ,'Two');
INSERT INTO some_table (some_number, some_string) VALUES (3 ,'Three');
INSERT INTO some_table (some_number, some_string) VALUES (4 ,'Four');
CREATE TYPE t_demo_object AS OBJECT (
some_number NUMBER,
some_string varchar2(32)
)
/
CREATE TYPE t_demo_objects IS TABLE OF t_demo_object
CREATE OR REPLACE PROCEDURE p_retrieve_demo_objects(p_num IN NUMBER, p_data OUT t_demo_objects) AS
BEGIN
SELECT t_demo_object(some_number, some_string) bulk collect INTO p_data FROM some_table;
END p_retrieve_demo_objects;
/
Java Code is as follow:
import java.sql.Array;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSetMetaData;
import java.sql.Struct;
import java.sql.Types;
import oracle.sql.StructDescriptor;
public class OracleTableOfResult {
public static void main(String...a) throws Exception {
Class.forName("oracle.jdbc.OracleDriver");
Connection connection = DriverManager.getConnection("jdbc:oracle:thin:simple/simple@abc-scan:1521/abc_taf_preconnect");
final String typeName = "T_DEMO_OBJECt";
final String typeTableName = "T_DEMO_OBJECTS";
final StructDescriptor structDescriptor = StructDescriptor.createDescriptor(typeName.toUpperCase(), connection);
final ResultSetMetaData metaData = structDescriptor.getMetaData();
CallableStatement cs = connection.prepareCall("{call p_retrieve_demo_objects(?, ?)}");
cs.setInt(1, 5);
cs.registerOutParameter(2, Types.ARRAY, typeTableName);
cs.execute();
Object[] data = (Object[]) ((Array) cs.getObject(2)).getArray();
for(Object tmp : data) {
Struct row = (Struct) tmp;
// Attributes are index 1 based...
int idx = 1;
for(Object attribute : row.getAttributes()) {
System.out.println(metaData.getColumnName(idx) + " = " + attribute);
++idx;
}
System.out.println("---");
}
cs.close();
connection.close();
}
}
all the above codes work well. However from http://info.michael-simons.eu/2012/07/24/how-to-retrieve-tables-of-custom-object-types-with-jdbc/
Oracle JDBC does not support RAW, DATE, and PL/SQL RECORD as element types.=>I'm not really convince on this limitations.
My amended code is as follow:
CREATE TABLE some_table_date
(
some_number NUMBER,
some_string varchar2(32),
some_date DATE
)
/
INSERT INTO some_table_date (some_number, some_string, some_date) VALUES (1 ,'One', To_DATE('2015-05-01 00:00:00','YYYY-MM-DD HH24:MI:SS'));
INSERT INTO some_table_date (some_number, some_string, some_date) VALUES (2 ,'Two', To_DATE('2015-05-02 01:00:00','YYYY-MM-DD HH24:MI:SS'));
INSERT INTO some_table_Date (some_number, some_string, some_date) VALUES (3 ,'Three', To_DATE('2015-05-03 02:00:00','YYYY-MM-DD HH24:MI:SS'));
INSERT INTO some_table_date (some_number, some_string, some_date) VALUES (4 ,'Four', To_DATE('2015-05-04 03:00:00','YYYY-MM-DD HH24:MI:SS'));
CREATE OR REPLACE TYPE t_demo_object_with_date AS OBJECT (
some_number NUMBER,
some_string varchar2(32),
some_date DATE
);
/
CREATE OR REPLACE TYPE t_demo_objects_with_date IS TABLE OF t_demo_object_with_date
/
CREATE OR REPLACE PROCEDURE p_retrieve_demo_objects_date(p_num IN NUMBER, p_data OUT t_demo_objects_with_date) AS
BEGIN
SELECT t_demo_object_with_date(some_number, some_string, some_date) bulk collect INTO p_data FROM some_table_date;
END p_retrieve_demo_objects_date;
/
import java.sql.Array;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSetMetaData;
import java.sql.Struct;
import java.sql.Types;
import oracle.sql.StructDescriptor;
public class OracleTableOfResult2 {
public static void main(String...a) throws Exception {
Class.forName("oracle.jdbc.OracleDriver");
Connection connection = DriverManager.getConnection("jdbc:oracle:thin:stock/stock@abc-scan1521/abc_taf_preconnect");
final String typeName = "t_demo_object_with_date";
final String typeTableName = "t_demo_objects_with_date";
final StructDescriptor structDescriptor = StructDescriptor.createDescriptor(typeName.toUpperCase(), connection);
final ResultSetMetaData metaData = structDescriptor.getMetaData();
CallableStatement cs = connection.prepareCall("{call p_retrieve_demo_objects_date(?, ?)}");
cs.setInt(1, 5);
cs.registerOutParameter(2, Types.ARRAY, typeTableName);
cs.execute();
Object[] data = (Object[]) ((Array) cs.getObject(2)).getArray();
for(Object tmp : data) {
Struct row = (Struct) tmp;
// Attributes are index 1 based...
int idx = 1;
for(Object attribute : row.getAttributes()) {
System.out.println(metaData.getColumnName(idx) + " = " + attribute);
++idx;
}
System.out.println("---");
}
cs.close();
connection.close();
}
}
So Is it possible to retrieve an array of pl/sql record which contain DATE elements in Java, so how can it be done with my examples that include date fields.
many thanks in advance.
|
|
|
|
|
|
| Re: Is it possible to retrieve an array of pl/sql record which contain DATE elements in Java [message #637332 is a reply to message #636978] |
Fri, 15 May 2015 02:46  |
kytemanaic
Messages: 55
Registered: February 2009
|
Member |
|
|
Hi Anil, if your solution requires Oracle Application Framework to be installed, then I afraid the solution is not applicable to me. I'm running the java application on eclipse, Jdk 1.7.0_71
I got the following solution but have some warnings: Map is a raw type. References to generic type Map <K,V> should be parameterized
pl/sql code
SPOOL C:\cambridge\database\script\bulk_collect\jdbc\howto-get-a-table-as-a-out-parameter-in-oracle_with_date_from_table.log
--http://stackoverflow.com/questions/13801183/howto-get-a-table-as-a-out-parameter-in-oracle
--http://www.oratable.com/multiset-operations-pls-00306-error-solution/
SET ECHO ON
drop PROCEDURE collect_multiset_union;
drop PROCEDURE myprocedure_date_tab_page;
drop TYPE outputOneSQLType_date;
drop TYPE BODY tableOneExample_date;
drop TYPE tableOneExample_date;
drop TABLE outputOneSQLType_tab;
CREATE TABLE outputOneSQLType_tab(
somethingOne VARCHAR2 (4)
,somethingTwo NUMBER (12)
,somethingThree Date,
CONSTRAINT outputOneSQLType_tab_pk PRIMARY KEY (somethingOne)
);
INSERT INTO outputOneSQLType_tab(somethingOne, somethingTwo, somethingThree) VALUES ('a', 1 ,TO_DATE('2015-05-14 01:00:30', 'YYYY-MM-DD HH24:MI:SS'));
INSERT INTO outputOneSQLType_tab(somethingOne, somethingTwo, somethingThree) VALUES ('b', 2 ,TO_DATE('2015-05-14 02:00:30', 'YYYY-MM-DD HH24:MI:SS'));
INSERT INTO outputOneSQLType_tab(somethingOne, somethingTwo, somethingThree) VALUES ('c', 3 ,TO_DATE('2015-05-14 03:00:30', 'YYYY-MM-DD HH24:MI:SS'));
INSERT INTO outputOneSQLType_tab(somethingOne, somethingTwo, somethingThree) VALUES ('d', 4 ,TO_DATE('2015-05-14 04:00:30', 'YYYY-MM-DD HH24:MI:SS'));
INSERT INTO outputOneSQLType_tab(somethingOne, somethingTwo, somethingThree) VALUES ('e', 5 ,TO_DATE('2015-05-14 05:00:30', 'YYYY-MM-DD HH24:MI:SS'));
INSERT INTO outputOneSQLType_tab(somethingOne, somethingTwo, somethingThree) VALUES ('f', 6 ,TO_DATE('2015-05-14 06:00:30', 'YYYY-MM-DD HH24:MI:SS'));
INSERT INTO outputOneSQLType_tab(somethingOne, somethingTwo, somethingThree) VALUES ('g', 7 ,TO_DATE('2015-05-14 07:00:30', 'YYYY-MM-DD HH24:MI:SS'));
INSERT INTO outputOneSQLType_tab(somethingOne, somethingTwo, somethingThree) VALUES ('h', 8 ,TO_DATE('2015-05-14 08:00:30', 'YYYY-MM-DD HH24:MI:SS'));
INSERT INTO outputOneSQLType_tab(somethingOne, somethingTwo, somethingThree) VALUES ('i', 9 ,TO_DATE('2015-05-14 09:00:30', 'YYYY-MM-DD HH24:MI:SS'));
INSERT INTO outputOneSQLType_tab(somethingOne, somethingTwo, somethingThree) VALUES ('j', 10 ,TO_DATE('2015-05-14 10:00:30', 'YYYY-MM-DD HH24:MI:SS'));
create or replace TYPE tableOneExample_date AS OBJECT (
somethingOne VARCHAR2 (4)
,somethingTwo NUMBER (12)
,somethingThree Date
,MAP MEMBER FUNCTION map
RETURN VARCHAR2
);
/
create or replace TYPE BODY tableOneExample_date AS
MAP MEMBER FUNCTION map
RETURN VARCHAR2
AS
BEGIN
RETURN somethingOne;
END;
END;
/
create or replace TYPE outputOneSQLType_date IS TABLE OF tableOneExample_date;
/
SELECT * FROM (SELECT row_number() OVER (ORDER BY a.somethingOne) row_num, a.* FROM (SELECT * FROM outputOneSQLType_tab)a ) WHERE row_num>=2 and row_num<=4;
SELECT * FROM (SELECT row_number() OVER (ORDER BY a.somethingOne) row_num, a.* FROM (SELECT * FROM outputOneSQLType_tab)a ) WHERE row_num>=6 and row_num<=7;
create or replace PROCEDURE myprocedure_date_tab_page (
outputOne OUT outputOneSQLType_date,
p_limit IN NUMBER DEFAULT 500,
p_start IN NUMBER DEFAULT 1)
as
CURSOR c_data IS
SELECT tableOneExample_date(somethingOne, somethingTwo, somethingThree)
FROM (SELECT * FROM (SELECT row_number() OVER (ORDER BY a.somethingOne) row_num, a.* FROM (SELECT * FROM outputOneSQLType_tab)a ) WHERE row_num>=p_start and row_num<=p_start+p_limit-1);
v_outputOne outputOneSQLType_date;
ecode NUMBER;
emesg VARCHAR2(200);
begin
v_outputOne := outputOneSQLType_date();
outputOne := v_outputOne; --when collection is empty need to passed in an initialized collection to the calling program
OPEN c_data;
LOOP
BEGIN
FETCH c_data
BULK COLLECT INTO v_outputOne LIMIT p_limit;
EXCEPTION
WHEN OTHERS THEN
ecode := SQLCODE;
emesg := SQLERRM;
dbms_output.put_line('Exception Codes ' || TO_CHAR(ecode) || '-' || emesg);
END;
EXIT WHEN v_outputOne.count = 0;
/*
FOR indx in 1 .. v_outputOne.COUNT LOOP
dbms_output.put_line('v_outputOne(i).somethingOne ' || v_outputOne(indx).somethingOne);
dbms_output.put_line('v_outputOne.somethingTwo ' || v_outputOne(indx).somethingTwo);
dbms_output.put_line('v_outputOne.somethingThree ' || to_char(v_outputOne(indx).somethingThree, 'YYYY-MM-DD HH24:MI:SS'));
END LOOP;
*/
outputOne := v_outputOne;
END LOOP;
--CLOSE c_data;
/*
FOR indx in 1 .. outputOne.COUNT LOOP
dbms_output.put_line('outputOne(i).somethingOne after CLOSE c_data ' || outputOne(indx).somethingOne);
dbms_output.put_line('outputOne.somethingTwo after CLOSE c_data ' || outputOne(indx).somethingTwo);
dbms_output.put_line('outputOne.somethingThree after CLOSE c_data ' || to_char(outputOne(indx).somethingThree, 'YYYY-MM-DD HH24:MI:SS'));
END LOOP;
*/
end;
/
CREATE OR REPLACE PROCEDURE collect_multiset_union
(p_final_outputOne OUT outputOneSQLType_date, p_limit IN NUMBER DEFAULT 500)
AS
outputOne outputOneSQLType_date;
v_limit NUMBER DEFAULT p_limit;
v_page NUMBER DEFAULT 1;
v_final_outputOne outputOneSQLType_date;
BEGIN
v_final_outputOne := outputOneSQLType_date();
LOOP
myprocedure_date_tab_page(outputOne=>outputOne, p_limit=>v_limit, p_start=>(v_page-1)*v_limit+1);
dbms_output.put_line ('v_page ' || v_page);
dbms_output.put_line ('outputOne.count ' || outputOne.count);
v_final_outputOne := v_final_outputOne MULTISET UNION DISTINCT outputOne;
EXIT WHEN outputOne.count=0;
v_page := v_page+1;
/*
FOR indx in 1 .. outputOne.COUNT LOOP
dbms_output.put_line('outputOne(i).somethingOne ' || outputOne(indx).somethingOne);
dbms_output.put_line('outputOne.somethingTwo ' || outputOne(indx).somethingTwo);
dbms_output.put_line('outputOne.somethingThree ' || to_char(outputOne(indx).somethingThree,'YYYY-MM-DD HH24:MI:SS'));
END LOOP;
*/
END LOOP;
/*
FOR indx in 1 .. v_final_outputOne.COUNT LOOP
dbms_output.put_line('v_final_outputOne(i).somethingOne ' || v_final_outputOne(indx).somethingOne);
dbms_output.put_line('v_final_outputOne.somethingTwo ' || v_final_outputOne(indx).somethingTwo);
dbms_output.put_line('v_final_outputOne.somethingThree ' || to_char(v_final_outputOne(indx).somethingThree,'YYYY-MM-DD HH24:MI:SS'));
END LOOP;
*/
p_final_outputOne := v_final_outputOne;
END;
/
SET ECHO OFF
SPOOL OFF
here's how I test the procedure in PL/SQL code
SPOOL C:\cambridge\database\script\bulk_collect\jdbc\test_howto-get-a-table-as-a-out-parameter-in-oracle_with_date_from_table_page_batches_union_2.log
SET SERVEROUTPUT ON
SET ECHO ON
DECLARE
outputOne outputOneSQLType_date;
v_limit NUMBER DEFAULT 500;
v_start NUMBER DEFAULT 1;
v_page NUMBER DEFAULT 1;
final_outputOne outputOneSQLType_date;
BEGIN
v_limit:=3;
final_outputOne := outputOneSQLType_date();
collect_multiset_union (p_final_outputOne=>final_outputOne, p_limit=>v_limit);
FOR indx in 1 .. final_outputOne.COUNT LOOP
dbms_output.put_line('final_outputOne(i).somethingOne ' || final_outputOne(indx).somethingOne);
dbms_output.put_line('final_outputOne.somethingTwo ' || final_outputOne(indx).somethingTwo);
dbms_output.put_line('final_outputOne.somethingThree ' || to_char(final_outputOne(indx).somethingThree,'YYYY-MM-DD HH24:MI:SS'));
END LOOP;
END;
/
here's the pl sql output
skeris@ned>
skeris@ned>DECLARE
2 outputOne outputOneSQLType_date;
3 v_limit NUMBER DEFAULT 500;
4 v_start NUMBER DEFAULT 1;
5 v_page NUMBER DEFAULT 1;
6 final_outputOne outputOneSQLType_date;
7 BEGIN
8 v_limit:=3;
9 final_outputOne := outputOneSQLType_date();
10 collect_multiset_union (p_final_outputOne=>final_outputOne, p_limit=>v_limit);
11
12 FOR indx in 1 .. final_outputOne.COUNT LOOP
13 dbms_output.put_line('final_outputOne(i).somethingOne ' || final_outputOne(indx).somethingOne);
14 dbms_output.put_line('final_outputOne.somethingTwo ' || final_outputOne(indx).somethingTwo);
15 dbms_output.put_line('final_outputOne.somethingThree ' || to_char(final_outputOne(indx).somethingThree,'YYYY-MM-DD HH24:MI:SS'));
16 END LOOP;
17 END;
18 /
v_page 1
outputOne.count 3
v_page 2
outputOne.count 3
v_page 3
outputOne.count 3
v_page 4
outputOne.count 1
v_page 5
outputOne.count 0
final_outputOne(i).somethingOne a
final_outputOne.somethingTwo 1
final_outputOne.somethingThree 2015-05-14 01:00:30
final_outputOne(i).somethingOne b
final_outputOne.somethingTwo 2
final_outputOne.somethingThree 2015-05-14 02:00:30
final_outputOne(i).somethingOne c
final_outputOne.somethingTwo 3
final_outputOne.somethingThree 2015-05-14 03:00:30
final_outputOne(i).somethingOne d
final_outputOne.somethingTwo 4
final_outputOne.somethingThree 2015-05-14 04:00:30
final_outputOne(i).somethingOne e
final_outputOne.somethingTwo 5
final_outputOne.somethingThree 2015-05-14 05:00:30
final_outputOne(i).somethingOne f
final_outputOne.somethingTwo 6
final_outputOne.somethingThree 2015-05-14 06:00:30
final_outputOne(i).somethingOne g
final_outputOne.somethingTwo 7
final_outputOne.somethingThree 2015-05-14 07:00:30
final_outputOne(i).somethingOne h
final_outputOne.somethingTwo 8
final_outputOne.somethingThree 2015-05-14 08:00:30
final_outputOne(i).somethingOne i
final_outputOne.somethingTwo 9
final_outputOne.somethingThree 2015-05-14 09:00:30
final_outputOne(i).somethingOne j
final_outputOne.somethingTwo 10
final_outputOne.somethingThree 2015-05-14 10:00:30
PL/SQL procedure successfully completed.
skeris@ned>
skeris@ned>SET SERVEROUTPUT OFF
skeris@ned>
skeris@ned>SET ECHO OFF
here's the java code
package main.java.tutorial;
import java.sql.SQLData;
import java.sql.SQLException;
import java.sql.SQLInput;
import java.sql.SQLOutput;
import java.sql.DriverManager;
//import java.sql.SQLData;
//import java.sql.SQLException;
//import java.sql.SQLInput;
//import java.sql.SQLOutput;
import java.sql.CallableStatement;
import java.sql.Array;
import java.sql.Connection;
import java.util.Map;
import java.sql.Date;
import java.text.SimpleDateFormat;
public class TestArrdateTabAll implements SQLData {
private String sql_type;
public String attrOne;
public int attrTwo;
public Date attrThree;
public TestArrdateTabAll()
{
}
public TestArrdateTabAll (String sql_type, String attrOne, int attrTwo, Date attrThree)
{
this.sql_type = sql_type;
this.attrOne = attrOne;
this.attrTwo = attrTwo;
this.attrThree = attrThree;
}
@Override
public String getSQLTypeName() throws SQLException {
// TODO Auto-generated method stub
return sql_type;
}
@Override
public void readSQL(SQLInput stream, String typeName) throws SQLException {
// TODO Auto-generated method stub
sql_type = typeName;
attrOne = stream.readString();
attrTwo = stream.readInt();
attrThree = stream.readDate();
}
@Override
public void writeSQL(SQLOutput stream) throws SQLException {
// TODO Auto-generated method stub
stream.writeString(attrOne);
stream.writeInt(attrTwo);
stream.writeDate(attrThree);
}
public static void main(String[] args) {
try{
Class.forName("oracle.jdbc.driver.OracleDriver");
Connection conn=DriverManager.getConnection("jdbc:oracle:thin:@orac-scan.camline.com:1521/taf_preconnect", "stock", "stock");
int v_limit =6;
//int v_page =1;
CallableStatement stmt = conn.prepareCall("begin collect_multiset_union(?,?); end;");
stmt.setInt(2, v_limit);
stmt.registerOutParameter(1, java.sql.Types.ARRAY, "OUTPUTONESQLTYPE_DATE"); // YOUR ARRAY TYPE (TO MATCH THE API OUTPUT), NOT OBJECT
stmt.execute();
Array arr = stmt.getArray (1);
Map map = conn.getTypeMap();
map.put("TABLEONEEXAMPLE_DATE", Class.forName("main.java.tutorial.TestArrdateTabAll")); // YOUR OBJECT TYPE, NOT ARRAY.
Object[] values = (Object[]) arr.getArray();
for (int i=0; i < values.length; i++)
{
TestArrdateTabAll a = (TestArrdateTabAll)values[i];
SimpleDateFormat DATE_FORMAT = new SimpleDateFormat("yyyy-MM-dd HH:MM:SS");
String attrThree_str = DATE_FORMAT.format(a.attrThree);
System.out.println("somethingOne: " + a.attrOne);
System.out.println("somethingTwo: " + a.attrTwo);
System.out.println("somethingThree: " + attrThree_str);
}
}catch (Exception e) {
e.printStackTrace();
}
}
}
java output from eclipse is as follow:
somethingOne: a
somethingTwo: 1
somethingThree: 2015-05-14 01:05:00
somethingOne: b
somethingTwo: 2
somethingThree: 2015-05-14 02:05:00
somethingOne: c
somethingTwo: 3
somethingThree: 2015-05-14 03:05:00
somethingOne: d
somethingTwo: 4
somethingThree: 2015-05-14 04:05:00
somethingOne: e
somethingTwo: 5
somethingThree: 2015-05-14 05:05:00
somethingOne: f
somethingTwo: 6
somethingThree: 2015-05-14 06:05:00
somethingOne: g
somethingTwo: 7
somethingThree: 2015-05-14 07:05:00
somethingOne: h
somethingTwo: 8
somethingThree: 2015-05-14 08:05:00
somethingOne: i
somethingTwo: 9
somethingThree: 2015-05-14 09:05:00
somethingOne: j
somethingTwo: 10
somethingThree: 2015-05-14 10:05:00
these linees
Map map = conn.getTypeMap();
map.put("TABLEONEEXAMPLE_DATE", Class.forName("main.java.tutorial.TestArrdateTabAll")); // YOUR OBJECT TYPE, NOT ARRAY.
give the following warnings respectively:
Map is a raw type. References to generic type Map <K,V> should be parameterized
Type safety: The method put (Object, Object) belongs to raw type Map. References to generic type Map <K,V> should be parameterized
Wonder if you could enlighten me on how to resolve the above warning?
and many many thanks
|
|
|
|
Goto Forum:
Current Time: Mon Feb 03 17:29:47 CST 2025
|
] 
Blog
Search
Help
Members
Register
Login
Home
