java plsql,Java-PLSQL-来自java的记录调用表

PLSQL package-procedure declarations

TYPE custom_type IS TABLE OF single_rec_type;

TYPE single_rec_type IS RECORD(

//id, name etc

)

Problem:

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.

When I googled, I came across these two options:

To represent it ,

(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) 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)

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?

解决方案

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

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.

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"));

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.

eg: correct way:

SRecord.java

public String id; \\varchar in plsql procedure

public String name; \\varchar in plsql procedure