如何在oracle中将表作为out参数 [英] Howto get a table as a out parameter in oracle
问题描述
我正在尝试将oracle过程调用的out参数强制转换为对象。它不起作用，因为 - 据我所知 - 我需要定义一个地图，它告诉方法如何施放它。如果地图是空的或不正确的填充，它默认为STRUCT类型的Objekt - 这在我的情况下是错误的。
I am trying to cast the out parameter of an oracle procedure call to a object. It does not work, because - as I understand it - I need to define a map, which tells the method how to cast it. If the map is emtpy or not correcty filled, it defaults to an Objekt of the type STRUCT - which is wrong in my case.
我已经建立了一个样本，它应该说明问题：
I have build a sample, which should illustrate the problem:
-- Procedure in database
PROCEDURE myprocedure (
inputParam IN VARCHAR2 (4),
outputOne OUT outputOneSQLType
outputTwo OUT outputTwoSQLType);
-- inside of a package
inside a package mypackage
-- first type
create or replace
TYPE outputOneSQLType IS TABLE OF tableOneExample
-- table of type
create or replace
TYPE tableOneExample AS OBJECT (
somethingOne VARCHAR2 (4)
,somethingTwo NUMBER (12)
)
//java from here
import oracle.jdbc.OracleCallableStatement;
import oracle.jdbc.OracleTypes;
import oracle.jdbc.oracore.OracleTypeADT;
import oracle.sql.STRUCT;
...
oracle.jdbc.driver.OracleConnection oracleConn = (oracle.jdbc.driver.OracleConnection) con.getMetaData().getConnection();
final OracleCallableStatement storedProc = (OracleCallableStatement)oracleConn.prepareCall("{call mypackage.myprocedure("+
":inputParam, :outputOne, :outputTwo)}");
storedProc.setString("inputParam", "SomeValue");
storedProc.registerOutParameter("outputOne", OracleTypes.STRUCT, "OUTPUTONESQLTYPE");
storedProc.registerOutParameter("outputTwo", OracleTypes.STRUCT, "OUTPUTTWOSQLTYPE");
storedProc.execute();
//So far so good
//now I am lost - I need to define the map to get the object?
//What should be inside the map?
Hashtable newMap = new Hashtable();
newMap.put("outputOneSQLType", ?????.class);
//If the map is empty, it defaults to STRUCT...
final STRUCT myObject = (STRUCT)storedProc.getObject("somethingOne",newMap);
// myObject.getBytes() gives me an object... but I cannot cast it to anything
由于地图错误，我不能使用像：
Because of the faulty map, I cannot use someting like:
final MyClass myObject = (MyClass)storedProc.getObject("somethingOne",newMap);
我应该如何填写地图？
编辑1
无法更改oracle数据库中的条目。我只是被允许使用它们。因此
The entries in the oracle database can not be changed. I am just allowed to use them. Therefor
stmt.registerOutParameter(2, java.sql.Types.ARRAY, "OUTPUTONESQLTYPE");
不起作用。因为一旦我不使用OracleTypes.STRUCT就抛出异常。看来outputOneSQLType里面有一个类型为OracleTypeCOLLECTION的对象
does not work. Because a exception is thrown as soon as I do not use "OracleTypes.STRUCT". It seems that inside of outputOneSQLType there is an object of type "OracleTypeCOLLECTION"
当我尝试
Hashtable newMap = new Hashtable();
newMap.put("outputOneSQLType",OracleTypeCOLLECTION.class);
final OracleTypeCOLLECTION myObject = (OracleTypeCOLLECTION)storedProc.getObject("somethingOne",newMap);
我得到一个例外： InstantiationException：oracle.jdbc.oracore.OracleTypeCOLLECTION
@DazzaL：我将尝试定义一个SQLDATA接口。也许这将是解决方案
@DazzaL: I will try to define a SQLDATA interface. Maybe that will be the solution
解决方案
- 准备好的语句应该类似于begin package.procedure（...）; end;和 NOT {call package.procedure（...）}）
- 需要一个SQLData接口。
- The prepared statement should be something like "begin package.procedure(...); end;" and NOT "{call package.procedure(...)})
- A SQLData Interface is needed.
@DazzaL：你统治！Thx。
@DazzaL: you rule! Thx.
推荐答案
你必须定义一个sqldata对象来映射它。
you have to define a sqldata object to map this.
文档： http://docs.oracle.com/cd/E11882_01/java.112/e16548/oraarr.htm#JJDBC28574
例如：
SQL> create or replace TYPE tableOneExample AS OBJECT (
2 somethingOne VARCHAR2 (4)
3 ,somethingTwo NUMBER (12)
4 );
5 /
Type created.
SQL> create or replace TYPE outputOneSQLType IS TABLE OF tableOneExample;
2 /
Type created.
SQL>
SQL> create or replace PROCEDURE myprocedure (
2 inputParam IN VARCHAR2,
3 outputOne OUT outputOneSQLType)
4 as
5 begin
6 outputOne := outputOneSQLType(tableOneExample('a', 1), tableOneExample('b', 2));
7 end;
8 /
Procedure created.
现在我们定义SQLDATA接口：
now we we define the SQLDATA interface:
import java.sql.*;
public class TestArr implements SQLData
{
private String sql_type;
public String attrOne;
public int attrTwo;
public TestArr()
{
}
public TestArr (String sql_type, String attrOne, int attrTwo)
{
this.sql_type = sql_type;
this.attrOne = attrOne;
this.attrTwo = attrTwo;
}
// define a get method to return the SQL type of the object
public String getSQLTypeName() throws SQLException
{
return sql_type;
}
// define the required readSQL() method
public void readSQL(SQLInput stream, String typeName)
throws SQLException
{
sql_type = typeName;
attrOne = stream.readString();
attrTwo = stream.readInt();
}
// define the required writeSQL() method
public void writeSQL(SQLOutput stream)
throws SQLException
{
stream.writeString(attrOne);
stream.writeInt(attrTwo);
}
}
确保流的输入和排序写入/读取与您的oracle类型相同，因为任何不一致都会给出内部表示错误。
make sure the inputs and ordering of the stream writes/reads are the same as your oracle type, as any inconsistencies will give internal representation errors.
然后在主类中，您将如下映射：
then in the main class, you'd map this as follows:
CallableStatement stmt = conn.prepareCall("begin myprocedure(?,?); end;");
stmt.setString(1, "foo");
stmt.registerOutParameter(2, java.sql.Types.ARRAY, "OUTPUTONESQLTYPE"); // YOUR ARRAY TYPE (TO MATCH THE API OUTPUT), NOT OBJECT
stmt.execute();
Array arr = stmt.getArray (2);
Map map = conn.getTypeMap();
map.put("TABLEONEEXAMPLE", Class.forName("TestArr")); // YOUR OBJECT TYPE, NOT ARRAY.
Object[] values = (Object[]) arr.getArray();
for (int i=0; i < values.length; i++)
{
TestArr a = (TestArr)values[i];
System.out.println("somethingOne: " + a.attrOne);
System.out.println("somethingTwo: " + a.attrTwo);
}
结果bieng：
M:\Documents\Sample Code\1>javac TestArr.java
M:\Documents\Sample Code\1>javac ArrayTest.java
Note: ArrayTest.java uses unchecked or unsafe operations.
Note: Recompile with -Xlint:unchecked for details.
M:\Documents\Sample Code\SQLComplexArray>java ArrayTest
Opening Oracle connection...done.
somethingOne: a
somethingTwo: 1
somethingOne: b
somethingTwo: 2
这篇关于如何在oracle中将表作为out参数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
