Java-PLSQL-来自java的记录调用表 [英] Java- PLSQL- Call Table of records from java
问题描述
PLSQL包过程声明
PLSQL package-procedure declarations
TYPE custom_type IS TABLE OF single_rec_type;
TYPE single_rec_type IS RECORD(
//id, name etc
)
问题:
但custom_type没有直接的Java类型表示[如 OracleTypes.CLOB
或 OracleTypes.CURSOR
]
因为custom_type是PLSQL类型而不是SQL类型。
But custom_type has no direct Java type representation [like OracleTypes.CLOB
or OracleTypes.CURSOR
]
because custom_type is a PLSQL type and not a SQL type.
当我用Google搜索时,我遇到了这两个选项:
要表示它,
When I googled, I came across these two options: To represent it ,
(1)从过程创建一个SQL TYPE(或者我们可以从java绑定的包装器PLSQL函数。
参考: java - 在oracle存储过程中传递数组
(1) create a SQL TYPE from procedure(or a wrapper PLSQL function) that we can bind from java. Reference: java - passing array in oracle stored procedure
(2)将输出参数注册到我们的类型并使用SQLData对象来表示记录。
参考:如何获得一张桌子作为oracle中的out参数
(2) Register the output parameter with our type and use SQLData object to represent a record. Reference: Howto get a table as a out parameter in oracle
callableStatement.registerOutParameter(8, OracleTypes.ARRAY, "custom_type");
执行此操作时,我收到错误消息:
On doing this, I get the error:
java.sql.SQLException: invalid name pattern: MYDB_OWNER.custom_type
at oracle.jdbc.oracore.OracleTypeADT.initMetadata(OracleTypeADT.java:554)
at oracle.jdbc.oracore.OracleTypeADT.init(OracleTypeADT.java:471)
一个建议是在架构内声明custom_type TYPE,而不是在包内声明。
或通过创建公共同义词和赠款。
One suggestion was to declare the custom_type TYPE inside the schema, instead of declaring inside the package. or by creating public synonym and giving grants.
问题 - 关于第二种方法,宣布任何自定义是正确的做法在模式级别输入?
Question - Regarding the second approach, is it correct practice to declare any custom type in schema level?
推荐答案
是的,这是它的唯一工作方式。我按照第二种方法中提到的链接
如何在oracle中获取一个表作为out参数
Yes, That's the only way it works. I followed the link mentioned in second approach Howto get a table as a out parameter in oracle
并且它有效。包级别更改包括(1)在模式级别声明 custom_type
和 single_rec_type
[作为全局,不在包内]和(2)用 AS OBJECT
替换 IS RECORD
。
and it worked. The Package level changes included (1) Declaring the custom_type
and single_rec_type
in schema level [as global, not inside the package] and (2) Replacing IS RECORD
with AS OBJECT
.
Java代码与链接中提到的内容不同,包括在 map.put(SINGLE_REC_TYPE)中提供类的完整名称,Class.forName(com.example.SRecord));
The Java code changes apart from what was mentioned in the link, includes giving the complete name for the class in map.put("SINGLE_REC_TYPE", Class.forName("com.example.SRecord"));
另外需要注意的是,在那个例子中,它提到了 stream.readString();
。如果您阅读了API,它会说'读取流中的下一个属性并将其作为Java编程语言中的String返回。'。因此,如果对象内部有三个属性,则使用此方法三次,如此
Another thing to notice is that in that example, it mentioned stream.readString();
. If you read the API, it says 'Reads the next attribute in the stream and returns it as a String in the Java programming language.' . So if you have three attributes inside the object, then use the method three times like this
id = stream.readString();
name = stream.readString();
designation = stream.readString();
该帖中提到了另一点;关于对象内属性的数据类型。如果存在类型不匹配,则会出现内部表示错误。
Another point is well mentioned in that post; Regarding the datatypes of attributes inside the object. If there are type mismatch, you get internal representation errors.
例如:正确方式:
SRecord.java
public String id; \\varchar in plsql procedure
public String name; \\varchar in plsql procedure
这篇关于Java-PLSQL-来自java的记录调用表的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!