Java for PL / SQL in the package does not define the type of support (not found, throw exception invalid name pattern information), need to define schema-level type.
Oracle's API:
Code:
Schema Level Type:
create or replace type Guy as object
(
name varchar2 (50),
age number
)
create or replace type GUYS as Table of GUY
PL / SQL:
create or replace package ArrayNesta is
PROCEDURE insert_guys (
guys_in IN guys
);
end ArrayNesta;
create or replace package body ArrayNesta is
PROCEDURE insert_guys (
guys_in IN guys
)
IS
BEGIN
for i in guys_in.first .. guys_in.last
LOOP
insert into ASSOCIATEARRAYNESTA (name, age) values (guys_in (i). name, guys_in (i). age);
END LOOP;
END insert_guys;
end ArrayNesta;
Database Table:
create table ASSOCIATEARRAYNESTA (
name varchar2 (50),
age number
)
Java:
/ **
*
* /
package array;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.util.HashMap;
import java.util.Map;
import oracle.jdbc.pool.OracleDataSource;
import oracle.sql.ARRAY;
import oracle.sql.ArrayDescriptor;
import oracle.sql.STRUCT;
import oracle.sql.StructDescriptor;
/ **
* @ Author nfeng
*
* /
public class ArrayTest (
/ **
* @ Param args
* /
public static void main (String [] args) throws Exception (
OracleDataSource ods = new OracleDataSource ();
ods.setURL ("jdbc: oracle: thin: username / password @ host: port: database");
Connection conn = ods.getConnection ();
System.out.println (conn);
StructDescriptor sd = StructDescriptor.createDescriptor ("GUY", conn);
Object [] nesta = new Object [] ("nesta", 29);
STRUCT snesta = new STRUCT (sd, conn, nesta);
Object [] kisey = new Object [] ("kisey", 30);
STRUCT skisey = new STRUCT (sd, conn, kisey);
ArrayDescriptor ad = ArrayDescriptor.createDescriptor ("GUYS", conn);
ARRAY guysin = new ARRAY (ad, conn, new STRUCT [] (snesta, skisey));
CallableStatement cs = conn.prepareCall ("(call ArrayNesta.insert_guys (?))");
cs.setArray (1, guysin);
cs.execute ();
conn.close ();
)
)
没有评论:
发表评论