1

I have a PLSQL code with the following signature.

procedure getall(
   p_id               in number,
   p_code             in varchar2,
   x_result           out tt_objs);

type rt_obj is record(
    val1              mytable.attr1%type
    val2              mytable.attr2%type
    val3              mytable.attr2%type
);

type tt_objs is table of rt_obj index by binary_integer;

What should be the Java code that can invoke this procedure and read x_result?

AppleGrew
  • 9,302
  • 24
  • 80
  • 124

2 Answers2

2

Maybe this could be what you are looking for.

This should be the interesting part:

//oracle.sql.ARRAY we will use as out parameter from the package
//and will store pl/sql table
ARRAY message_display = null; 

//ArrayList to store object of type struct 
ArrayList arow= new ArrayList();

//StructDescriptor >> use to describe pl/sql object
//type in java.
StructDescriptor voRowStruct = null; 

//ArrayDescriptor >> Use to describe pl/sql table
//as Array of objects in java
ArrayDescriptor arrydesc = null;

//Input array to pl/sql procedure
ARRAY p_message_list = null;

//Oracle callable statement used to execute procedure
OracleCallableStatement cStmt=null;

try
{
//initializing object types in java.
voRowStruct = StructDescriptor.createDescriptor("RECTYPE",conn); 
arrydesc = ArrayDescriptor.createDescriptor("RECTAB",conn);
}

catch (Exception e)
{
throw OAException.wrapperException(e);
}

for(XXVORowImpl row = (XXVORowImpl)XXVO.first(); 
row!=null;
row = (XXVORowImpl)XXVO.next())
{
//We have made this method to create struct arraylist
// from which we will make ARRAY
//the reason being in java ARRAY length cannot be dynamic
//see the method defination below.
populateObjectArraylist(row,voRowStruct,arow); 
}

//make array from arraylist
STRUCT [] obRows= new STRUCT[arow.size()];
for(int i=0;i < arow.size();i++)
{
obRows[i]=(STRUCT)arow.get(i);
}

try 
{
p_message_list = new ARRAY(arrydesc,conn,obRows); 
}
catch (Exception e)
{
throw OAException.wrapperException(e);
}

//jdbc code to execute pl/sql procedure 
try
{
cStmt
=(OracleCallableStatement)conn.prepareCall("{CALL ioStructArray.testproc(:1,:2)}"); 
cStmt.setArray(1,p_message_list);
cStmt.registerOutParameter(2,OracleTypes.ARRAY,"RECTAB"); 
cStmt.execute();

//getting Array back
message_display = cStmt.getARRAY(2);
//Getting sql data types in oracle.sql.datum array
//which will typecast the object types
Datum[] arrMessage = message_display.getOracleArray();

//getting data and printing it
for (int i = 0; i < arrMessage.length; i++)
{ 
oracle.sql.STRUCT os = (oracle.sql.STRUCT)arrMessage[i];
Object[] a = os.getAttributes(); 
System.out.println("a [0 ] >>attribute1=" + a[0]); 
System.out.println("a [1 ] >>attribute2=" + a[1]);
System.out.println("a [2 ] >>attribute3=" + a[2]);
Eggi
  • 1,684
  • 4
  • 20
  • 31
  • 1
    In case both `rectype` and `rectab` are in a package. How do I access them? – AppleGrew Mar 09 '12 at 09:03
  • Have you considered using a ref cursor? http://www.oracle-base.com/articles/misc/UsingRefCursorsToReturnRecordsets.php – Eggi Mar 09 '12 at 09:08
  • I am not sure how to do that. Furthermore the package within which it is defined is not owned by me. So, I cannot modify the package. – AppleGrew Mar 09 '12 at 09:14
  • The only option I now see is to write my own wrapper over the main procedure. I am now considering using ref cursor but am stuck again. How do I get the values from the collection returned to the ref cursor? – AppleGrew Mar 09 '12 at 09:49
  • 1
    This answer is good and complete, you can see http://stackoverflow.com/questions/9129181/jpa-storedprocedurecall-object-type-in-parameter/9139457#9139457 for more information. – Sam Mar 10 '12 at 10:02
  • 1
    Yes I was trying to do something not possible now. Refer ans: http://stackoverflow.com/questions/6410452/fetch-oracle-table-type-from-stored-procedure-using-jdbc for more info. – AppleGrew Mar 12 '12 at 11:20
1

Yep, it's not possible directly. You can either

  • Create a public type with the same structure as the PLSQL record and follow the Eggi's advice. Similar approach uses Oracle JPublisher. JPublisher can help you to automate this process.
  • Or you can use anonymous PLSQL block to create or read PLSQL records. We are thinking about creating a library do it automatically in our company.
  • Or you can create a wrapper functions to wrap records to XML (both in Java and PLSQL). Then pass XML as Xmltype or CLOB between DB and Java. We have already this solution for some complicated structures. It's tedious and slows down a processing a little bit, but it works.
xmedeko
  • 7,336
  • 6
  • 55
  • 85