CODEWITHSUNDEEP
javaplsql
spiderman
spiderman

Reputation: 11132

Java- PLSQL- Call Table of records from 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?

Upvotes: 1

Views: 4048

Answers (1)

spiderman
spiderman

Reputation: 11132

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

Upvotes: 1

Related Questions