在ORACLE中定义如下TABLE数据类型:CREATE OR REPLACE TYPE tUserInfoSt AS OBJECT
(
vUserName VARCHAR2(150),
vAge NUMBER(2),
vSalary NUMBER(10,2)
); CREATE OR REPLACE TYPE tUserList AS TABLE OF tUserInfoSt ;
现在有一个存储过程 pCountAll 的入口参数是 tUserList .
要在JAVA中调用存储过程 pCountAll .问题:如何在JAVA中设置ORACLE中自定义TABLE类型(tUserList) 的 LIST 传递给存储过程 ?
(
vUserName VARCHAR2(150),
vAge NUMBER(2),
vSalary NUMBER(10,2)
); CREATE OR REPLACE TYPE tUserList AS TABLE OF tUserInfoSt ;
现在有一个存储过程 pCountAll 的入口参数是 tUserList .
要在JAVA中调用存储过程 pCountAll .问题:如何在JAVA中设置ORACLE中自定义TABLE类型(tUserList) 的 LIST 传递给存储过程 ?
import java.util.ArrayList;
import java.util.Map;import oracle.jdbc.OracleCallableStatement;
import oracle.jdbc.OracleTypes;
import oracle.jdbc.oracore.OracleType;
import oracle.sql.*;
public class plsql {
public static Connection getConnection() throws Exception {
Class.forName("oracle.jdbc.driver.OracleDriver");
return DriverManager.getConnection(
"jdbc:oracle:thin:@hostIP:1521:InstanceName", "userID", "password");
}
public static void main(String[] args) throws Exception{
Connection conn = null;
try{
conn = getConnection();
String sql="{call pCountAll(?) }";
OracleCallableStatement stmt = (OracleCallableStatement)conn.prepareCall(sql);
stmt.registerOutParameter(1,OracleTypes.ARRAY,"tUserList");//tUserList的大小写敏感
stmt.execute();
ARRAY simpleArray = stmt.getARRAY(1);
Object[] ay = (Object[])simpleArray.getArray();
int i = ay.length;
STRUCT st = (STRUCT)ay[0];
System.out.println("++++"+st.getSQLTypeName());
Object[] oj = st.getAttributes();
String str = (String)oj[0];
System.out.println(str);
stmt.close();
conn.close(); }catch(Exception e){
e.printStackTrace();
conn.close();
}
}}可以参考我的一个回复:
http://topic.csdn.net/u/20080325/23/16c822ce-4a85-45a2-a092-e9509e58b884.html
import java.util.ArrayList;
import java.util.Map;import oracle.jdbc.OracleCallableStatement;
import oracle.jdbc.OracleTypes;
import oracle.jdbc.oracore.OracleType;
import oracle.sql.*;
public class plsql {
public static Connection getConnection() throws Exception {
Class.forName("oracle.jdbc.driver.OracleDriver");
return DriverManager.getConnection(
"jdbc:oracle:thin:@hostIP:1521:InstanceName", "userID", "password");
}
public static void main(String[] args) throws Exception{
Connection conn = null;
try{
conn = getConnection();
String sql="{call pCountAll(?) }";
OracleCallableStatement stmt = (OracleCallableStatement)conn.prepareCall(sql);
stmt.registerOutParameter(1,OracleTypes.ARRAY,"tUserList");//tUserList的大小写敏感
stmt.execute();
ARRAY simpleArray = stmt.getARRAY(1);
Object[] ay = (Object[])simpleArray.getArray();
int i = ay.length;
STRUCT st = (STRUCT)ay[0];
System.out.println("++++"+st.getSQLTypeName());
Object[] oj = st.getAttributes();
String str = (String)oj[0];
System.out.println(str);
stmt.close();
conn.close(); }catch(Exception e){
e.printStackTrace();
conn.close();
}
}}
-----------------------------
参考上面的代码试一下。
你贴得好像和我的一样,什么意思?
还是我的有错误,拟修改过了,只是我眼睛看花了,没发现?
呵呵,有意思!:D
import java.util.ArrayList;
import java.util.Map; import oracle.jdbc.OracleCallableStatement;
import oracle.jdbc.OracleTypes;
import oracle.jdbc.oracore.OracleType;
import oracle.sql.*;
public class plsql {
public static Connection getConnection() throws Exception {
Class.forName("oracle.jdbc.driver.OracleDriver");
return DriverManager.getConnection(
"jdbc:oracle:thin:@hostIP:1521:InstanceName", "userID", "password");
}
public static void main(String[] args) throws Exception{
Connection conn = null;
try{
conn = getConnection();
String sql="{call pCountAll(?) }";
OracleCallableStatement stmt = (OracleCallableStatement)conn.prepareCall(sql);
stmt.registerOutParameter(1,OracleTypes.ARRAY,"tUserList");//tUserList的大小写敏感
stmt.execute();
ARRAY simpleArray = stmt.getARRAY(1);
Object[] ay = (Object[])simpleArray.getArray();
int i = ay.length;
STRUCT st = (STRUCT)ay[0];
System.out.println("++++"+st.getSQLTypeName());
Object[] oj = st.getAttributes();
String str = (String)oj[0];
System.out.println(str);
stmt.close();
conn.close(); }catch(Exception e){
e.printStackTrace();
conn.close();
}
} }