For details about how to obtain the custom type of Oracle stored procedure through JDBC

SQL code

-- Create Table student (ID int, name varchar (20), age int, sex varchar (2), birth date); insert into student values (1, 'zhang san ', 23, 'male', sysdate); insert into student values (2, 'Li si', 24, 'male', sysdate); insert into student values (3, 'wang 5 ', 25, 'mal', sysdate); -- create a package create or replace package testpackage as type testrecord1 is record (ID int, name varchar (20 )); type testtable1 is table of testrecord1 index by binary_integer; Procedure testprocedure1 (result out testtable1); End testpackage; -- create or replace package body testpackage as procedure testprocedure1 (result out testtable1) as record1 testrecord1; v_id int; v_name varchar (20); cursor1 sys_refcursor; begin open cursor1 for select ID, name from student; loop fetch cursor1 into v_id, v_name; if cursor1 % notfound then exit; else record1.id: = v_id; record1.name: = v_name; Result (result. count): = record1; end if; end loop; end testprocedure1; end testpackage; -- call the Stored Procedure correctly. Declare Table1 testpackage. testtable1; I INT: = 0; begin testpackage. testprocedure1 (Table1); while I <table1.count loop dbms_output.put_line (Table1 (I ). ID); I: = I + 1; end loop; end;

Java code

// Class is wrong. forname ("oracle. JDBC. driver. oracledriver "); connection con = drivermanager. getconnection ("JDBC: oracle: thin: @ 127.0.0.1: 1521: Test", "XXX", "yyy"); oraclecallablestatement cs = (oraclecallablestatement) con. preparecall ("{call testpackage. testprocedure1 (?)} "); CS. registeroutparameter (1, Oracle. JDBC. oracletypes. array, "testpackage. testtable1 ".touppercase());cs.exe cute (); array = cs. getarray (1); datum [] DATA = array. getequallearray (); For (datum D: Data) {struct = (struct) D; datum [] stringvalue = struct. getoracleattributes (); object [] nostringvalue = struct. getattributes (); system. out. print (bigdecimal) nostringvalue [0]); // ID system. out. println (new string (stringvalue [1]. getbytes (); // name} CS. close (); con. close ();

------ Solution --------------------------------------------------------

Java code

// Java calls the Oracle stored procedure when the group parameter is imported to Oracle. SQL. struct; import oracle. SQL. structdescriptor; Public returntype batchaddterminals (terminaluserstype [] tus) {returntype ret = new returntype (); ret. setcode (0); // connection conn = c3p0pool. getinstance (). getconn (); generalconnect GC = new generalconnect (); connection conn = GC. getconnection (); oracle. JDBC. oracleconnection oracleconn = (Oracle. JDBC. oracleconnecti On) Conn; callablestatement stat = NULL; If (oracleconn! = NULL) {try {structdescriptor SD = new structdescriptor ("type_terminal_user", oracleconn); struct [] Results = new struct [Tus. length]; for (INT I = 0; I <Tus. length; I ++) {object [] O = new object [7]; O [0] = new INTEGER (TUS [I]. getepid (); O [1] = new string (TUS [I]. getUserName (); O [2] = new string (TUS [I]. getmobile (); O [3] = new string (TUS [I]. gettermcode (); O [4] = new string (TUS [I]. getuserdesc (); O [5] = new string (TUS [I]. getsmsmobile (); O [6] = new string (TUS [I]. getPassword (); Results [I] = new struct (SD, oracleconn, O);} string SQL = "{call pack_user_terminals.batchaddterminals (?,?,?)} "; Stat = oracleconn. preparecall (SQL); stat. registeroutparameter (2, Java. SQL. types. integer); stat. registeroutparameter (3, Java. SQL. types. varchar); oracle. SQL. arraydescriptor des_table_terminal_user = oracle. SQL. arraydescriptor. createdescriptor ("table_terminal_user", oracleconn); oracle. SQL. array ora_array1 = new Oracle. SQL. array (des_table_terminal_user, oracleconn, results); stat. setarray (1, ora_array1); stat.exe cute (); ret. setcode (stat. getint (2); ret. setdesc (stat. getstring (3);} catch (Java. SQL. sqlexception ex) {log. getinstance (). outlog ("database access exception" + ex. getmessage (); ret. setdesc ("database access exception" + ex. getmessage ();} catch (exception ex) {log. getinstance (). outlog ("Other exceptions" + ex. getmessage (); ret. setdesc ("Other exceptions" + ex. getmessage ();} finally {closedb. attemptclose (STAT); closedb. attemptclose (conn) ;}} return ret ;}

 

Arraydescriptor. createdescriptor (type, Conn); the schema-level object type can be a separate name. The default value is schema, but package cannot be added, the solution to the Code in Oracle JDBC is only to package. type creates a synonym (try the synonym) or you can use a schema-level object (the custom type is placed outside the package definition ), JDBC is not very good. This is a long-standing defect to my knowledge, the oracle JDBC driver does not support
Using the arraydescriptor for array data types (varray or nested table) that are defined inside of a package. the same is true for structdescriptor as well. if you want to use array and object data types, you must define them outside of a package. then you'll
Be able to use the descriptors in your JDBC programs. as far as I know, you can only create an "arraydescriptor" and a "structdescriptor" for database types. in other words, types that were created using the create type (DDL) statement. you cannot create an "arraydescriptor"
Or a "structdescriptor" For types created in PL/SQL packages.