Home » Developer & Programmer » JDeveloper, Java & XML » Passing array structure from java callable stmnt to a pl/sql stored procedure
Passing array structure from java callable stmnt to a pl/sql stored procedure [message #474860] Thu, 09 September 2010 06:25 Go to next message
junior9
Messages: 4
Registered: September 2010
Junior Member
Hi,

I want to pass an array of records as input from my java layer to plsql stored procedure.

Say, I want to pass an array of recordType1.
Now, recordType1 has "m" elements.
The last element in recordType1 is again an array of some recordType2.

I have created objects at database level of recordType1 and recordType2.
Then I have created table type of these objects.

The object creation is as:

create or replace type vertexData as object(
J_Code varchar2(12),
S_Code varchar2(2),
C_Name varchar2(40),
City_Name varchar2(40),
Auth_Level number(9),
Tax_Rate number(9),
Tax_Amount number(18),
Non_Taxable_Amount number(18),
Tax_Exempt_Amount number(18)
);

create type VertexDataArray as table of vertexData ;

create or replace type physicalPaymentInput as object(
Payment_Method number(9),
Total_Payment_Amount number(9),
Replen_Amount number(9),
Non_Replen_Amount number(9),
Payment_Ref varchar2(10),
Payment_Text varchar2(100),
vertex_arry VertexDataArray
);

create type physicalPaymentInputrec as table of physicalPaymentInput;

I have also created corresponding classes in java.

My java code is as follows:
I am expecting physicalPaymentInputArray as an input to my wrapper. So, I am just passing the same input to the ArrayDescriptor.createDescriptor. The code is as follows:



public static void applyPayment_1(AccountSecurity as,CustomerPK aCustomerPK,

AccountPK aAccountPK, int paymentChannel,

long paymentDate,String paymentCurrency,

int phyPaymentSeq,

PhysicalPaymentInput_1[] physicalPaymentInputArray)

throws ApplicationException, NullParameterException, SQLException {



int location = as.getLocation();

DataPartitioningPolicy policy = aContext.getDataPartitioningPolicy();

try {

// steps to perform

DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver());
Connection connection = DriverManager.getConnection

("jdbc:oracle:thin:@camdl106:1521:DEV6", "rb444", "rb444");
ArrayDescriptor arrayDescriptor = ArrayDescriptor.createDescriptor(

"PHYSICALPAYMENTINPUTREC", connection);

ARRAY physicalPaymentInputArray1 = new ARRAY(arrayDescriptor, connection, physicalPaymentInputArray);

try {

CallableStatement dbcall = connection.prepareCall("{call Custom.applypayment_1("
+ "?,?,?,?,?,?,?,?,?" + ")}");
try {
setVarchar30("genevaUser", dbcall, 1, policy.getGenevaUserName());
setVarchar40("j2eeUserName", dbcall, 2, policy.getJ2EEUserName());
setVarchar20("customerRef", dbcall, 3, aCustomerPK.getCustomerRef() );
setVarchar20("accountNum", dbcall, 4, aAccountPK.getAccountNum() );
setNumber9("paymentChannel", dbcall, 5, paymentChannel);
setDate("paymentDate", dbcall,6, paymentDate);
setVarchar20("paymentCurrency", dbcall, 7, paymentCurrency);
setNumber9("physicalPaymentSeq", dbcall,8,phyPaymentSeq);
dbcall.setArray(9, physicalPaymentInputArray1);
dbcall.execute();
}
finally {
dbcall.close();
}
}
finally {
connection.close();
}
}
catch (SQLException e) {
throw ExceptionMapper.classifySQLException(e, location);
}
}

I am getting a runtime exception saying "java.sql.exception:Fail to convert to internal representation."

Please help me to resolve the above problem,as I am unable to proceed.
I did my bit of google search but could not find anything relevant to my scenario.
Please help,its very urgent.


Thanks for all your help and time

[Updated on: Thu, 09 September 2010 06:33]

Report message to a moderator

Re: Passing array structure from java callable stmnt to a pl/sql stored procedure [message #474870 is a reply to message #474860] Thu, 09 September 2010 07:48 Go to previous messageGo to next message
Kevin Meade
Messages: 2103
Registered: December 1999
Location: Connecticut USA
Senior Member
I am not sure this post is going to be very helpful but here goes anyways.

I have lots of experience in passing data from java to oracle, but zeroe experience passing arrays of records from java to oracle. I always stop at single column arrays and rely on the native mapping of datatypes for scalar arrays to work. My business likes this because it does not require the creation of oracle specific java classes.

That said, clearly this is supposed to be doable. The problem I have always run into and everyone else I have worked with too is creating the java class to map correctly to the oracle datatype. Additionally you are using nested types so that makes it even harder.

My only thought is that your java class is not right or not being used. This is very common. Have you done lots of this kind of data passing before? How did you create the class? by hand? or using JDeveloper?

I know there are experts on this site who do this because I have seen their answers in the past. Maybe they will chime in.

Kevin
Re: Passing array structure from java callable stmnt to a pl/sql stored procedure [message #474945 is a reply to message #474870] Fri, 10 September 2010 00:07 Go to previous messageGo to next message
junior9
Messages: 4
Registered: September 2010
Junior Member
Hey Kevin,

Thanks for the reply.

No, I have never passed array from java to stored procedure. But I guess for a single element array we can pass it from java to stored procedure.

I am using J2EE spring framework, my build script automatically creates java class when I define the structure in an xml file.

Now, I am testing to pass a 1D array (String,int,String) from java to stored procedure.
If it is successful, then I will retry to hit the root problem again i.e. passing a 2D array from java to stored procedure.

Please let me know, if you come accrosss solution of some similar problem

Thanks
Re: Passing array structure from java callable stmnt to a pl/sql stored procedure [message #474954 is a reply to message #474945] Fri, 10 September 2010 05:06 Go to previous messageGo to next message
junior9
Messages: 4
Registered: September 2010
Junior Member
I tried passing an array having(String,int,String) structure with the following java code:

----------------------------------------------------------
Connection connection = DriverManager.getConnection
("jdbc:oracle:thin:@camdl106:1521:DEV6", "rb444", "rb444");
CreateTestData_1 crtd2 = new CreateTestData_1();
CreateTestData_1 crtd1 = new CreateTestData_1("a1",8,"c1");
CreateTestData_1[] crt = {crtd1,crtd2};
ArrayDescriptor arrayDescriptor = ArrayDescriptor.createDescriptor(
"CREATETESTDATAREC", connection);

ARRAY createTestDataArray1 = new ARRAY(arrayDescriptor, connection, crt);
-----------------------------------------------------------

But, I am getting the error: Fail to convert to internal representation.

"CREATETESTDATAREC" is defined as
create or replace type createtestdatarec as table of createtestdata in my database.
createtestdata is an object in database as
create or replace type createtestdata as object(
accountNum varchar2(20),
total number,
customerRef varchar2(20));

The signature of the stored procedure to which I am passing this array is:
procedure test(TestRecArray IN createtestdatarec );


The java data bean CreateTestData_1 is as follows:
public CreateTestData_1(
java.lang.String _accountNumber,
int _total,
java.lang.String _customerRef
)

Please,can anyone tell where am I going wrong.
I would really appreciate any help from you.

Thanks
Re: Passing array structure from java callable stmnt to a pl/sql stored procedure [message #474969 is a reply to message #474954] Fri, 10 September 2010 07:22 Go to previous messageGo to next message
Alessandro Rossi
Messages: 166
Registered: September 2008
Location: Rome
Senior Member
This may help you.

It's not so simple, but it's possible.

Bye

Re: Passing array structure from java callable stmnt to a pl/sql stored procedure [message #474977 is a reply to message #474969] Fri, 10 September 2010 09:42 Go to previous messageGo to next message
tahpush
Messages: 961
Registered: August 2006
Location: Stockholm/Sweden
Senior Member

Haven't tried it with Java. Have got it working with .NET using ODP (Oracle Data Provider For .NET).
But you are using the thin jdbc driver, which I don't think have the OCI layer if I recall correctly.
I believe JDBC needs OCI for exchanging collections.
So have look at the thick jdbc driver that provides the OCI layer.

[Updated on: Fri, 10 September 2010 09:47]

Report message to a moderator

Re: Passing array structure from java callable stmnt to a pl/sql stored procedure [message #475033 is a reply to message #474954] Sat, 11 September 2010 02:18 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9077
Registered: November 2002
Location: California, USA
Senior Member
You might find some useful information and examples that you can adapt here:

http://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:826426940238#48839713014211
Re: Passing array structure from java callable stmnt to a pl/sql stored procedure [message #475127 is a reply to message #474860] Mon, 13 September 2010 04:03 Go to previous messageGo to next message
junior9
Messages: 4
Registered: September 2010
Junior Member
I went through the links suggested...but still am not able to progress Sad Sad

Can anyone please help and quote with some example..
Thanks
Re: Passing array structure from java callable stmnt to a pl/sql stored procedure [message #475194 is a reply to message #475127] Mon, 13 September 2010 10:03 Go to previous message
Alessandro Rossi
Messages: 166
Registered: September 2008
Location: Rome
Senior Member
You probably didn't read deeply the documentation.

Where it says Using a Type Map to Map Array Elements and Creating and Using Custom Object Classes for Oracle Objects.

It says
Quote:

If your array contains Oracle objects, then you can use a type map to associate the objects in the array with the corresponding Java class. If you do not specify a type map, or if the type map does not contain an entry for a particular Oracle object, then each element is returned as an oracle.sql.STRUCT object.

If you want the type map to determine the mapping between the Oracle objects in the array and their associated Java classes, then you must add an appropriate entry to the map.


That means that to bind a collection of custom object types to a statement you must:

    - create a Oracle Object Type specular to the Java Class implemented by the elements your Java Array,
    - specify a Map where you put an entry to specify the correspondence between the java class and the Oracle Object Type.

Then it illustrates an example about the EMPLOYEE Object Type and the EmployeeObj Java class.

Where it says Creating and Using Custom Object Classes for Oracle Objects

there is this part
Quote:

If you want to create custom object classes for your Oracle objects, then you must define entries in the type map that specify the custom object classes that the drivers will instantiate for the corresponding Oracle objects.

You must also provide a way to create and populate instances of the custom object class from the Oracle object and its attribute data. The driver must be able to read from a custom object class and write to it. In addition, the custom object class can provide getXXX and setXXX methods corresponding to the attributes of the Oracle object, although this is not necessary. To create and populate the custom classes and provide these read/write capabilities, you can choose between the following interfaces:


    The JDBC standard SQLData interface
    The ORAData and ORADataFactory interfaces provided by Oracle


The custom object class you create must implement one of these interfaces. The ORAData interface can also be used to implement the custom reference class corresponding to the custom object class. However, if you are using the SQLData interface, then you can use only weak reference types in Java, such as java.sql.Ref or oracle.sql.REF. The SQLData interface is for mapping SQL objects only.


then

Quote:

Understanding the ORAData Interface

One of the choices in making an Oracle object and its attribute data available to Java applications is to create a custom object class that implements the oracle.sql.ORAData and oracle.sql.ORADataFactory interfaces. The ORAData and ORADataFactory interfaces are supplied by Oracle and are not a part of the JDBC standard.


and

Quote:

Understanding ORAData Features

The ORAData interface has the following advantages:
-It recognizes Oracle extensions to the JDBC. ORAData uses oracle.sql.Datum types directly.
-It does not require a type map to specify the names of the Java custom classes you want to create.
-It provides better performance. ORAData works directly with Datum types, the internal format the driver uses to hold Oracle objects.

The ORAData and ORADataFactory interfaces do the following:


    -The toDatum method of the ORAData class transforms the data into an oracle.sql.* representation.
    -ORADataFactory specifies a create method equivalent to a constructor for your custom object class. It creates and returns an ORAData instance. The JDBC driver uses the create method to return an instance of the custom object class to your Java application or applet. It takes as input an oracle.sql.Datum object and an integer indicating the corresponding SQL type code as specified in the OracleTypes class.


ORAData and ORADataFactory have the following definitions:

public interface ORAData 
{ 
    Datum toDatum (OracleConnection conn) throws SQLException;
} 
 
public interface ORADataFactory 
{ 
    ORAData create (Datum d, int sql_Type_Code) throws SQLException; 
} 


Where conn represents the Connection object, d represents an object of type oracle.sql.Datum and sql_Type_Code represents the SQL type code of the Datum object.






This means that your Java class should implement ORAData interface to acquire Collections of Custom Object Type from the Oracle RDBMS to the Java Application and that it should implement ORADataFactory to create a Collection of Custom Object Type elements to pass to the RDBMS.

The missing parts of my explanations are available at the documentation page, with enough details so if you miss some concepts please read the manual.

A sample of this elements put together is in the following code.

create the object type and the corresponding collection type in the RDBMS.

create type try_obj as object (
		field_a number,
		field_b varchar2(10)
	)
/

create type try_obj_tab as table of try_obj
/


Define a corresponding Java class implementing ORAData and ORADataFactory with a sample main() method that binds an instance an array to a SQL sdtatements.

import java.sql.*;
import java.util.*;

import oracle.jdbc.*;
import oracle.jdbc.pool.OracleDataSource;
import oracle.sql.*;


public class JTryObj implements ORADataFactory,ORAData{
	private NUMBER field1;
	private CHAR field2;
	
	public JTryObj(OracleConnection conn,int n,String c) throws SQLException {
		field1 = new NUMBER(n);
		field2 = new CHAR(c,oracle.sql.CharacterSet.make(conn.getStructAttrCsId()));
	}
	
	public JTryObj(NUMBER n, CHAR c) {
		field1 = n;
		field2 = c;
	}
	public JTryObj(Object[] attributes) {
		this(
				(NUMBER) attributes[0],
				(CHAR) attributes[1]
			);
	}
	public JTryObj(Datum d) throws SQLException {
		this(((STRUCT) d).getOracleAttributes());
	}

	@Override
	public ORAData create(Datum d, int sqlType) throws SQLException {
		if (d == null)
			return null;
		else {
			return new JTryObj(d);
		}
	}
	
	public STRUCT toSTRUCT(Connection conn) throws SQLException  {
		StructDescriptor sd =
			StructDescriptor.createDescriptor("TRY_OBJ", conn);
		Object [] attributes = { field1,field2 };
		return new STRUCT(sd, conn, attributes);
	}
	@Override
	public Datum toDatum(Connection conn) throws SQLException {
		return toSTRUCT(conn); 
	}
	
	
	public static void main(String args[]) throws SQLException, ClassNotFoundException {
		// initialize the connection
		OracleConnection conn = null;
		OracleDataSource ods = new OracleDataSource();
		ods.setDriverType("oci8");
		ods.setUser(args[0]);
		ods.setPassword(args[1]);
		ods.setTNSEntryName(args[2]);
		conn = (OracleConnection) ods.getConnection();
		
		// create the java array
		JTryObj javaArray[] = {new JTryObj(conn,1,"abc"),new JTryObj(conn,2,"dce")};
		
		// Map the java class to the Oracle type
		Map map = conn.getTypeMap();
		map.put("TRY_OBJ", Class.forName("JTryObj"));
		ArrayDescriptor jTryObjArrayDesc = ArrayDescriptor.createDescriptor (
				"TRY_OBJ_TAB",
				conn
			);
		
		// create the Callable statement		
		OracleCallableStatement stat = (OracleCallableStatement) conn.prepareCall("select * from table(:tab)");
		
		// create an Oracle collection on client side to use as parameter
		ARRAY oracleCollection = new ARRAY(jTryObjArrayDesc,conn,javaArray);
		
		// bind the collection
		stat.setArrayAtName("tab", oracleCollection);
		
		// execute the statement and print results
		ResultSet rset = stat.executeQuery();
		while (rset.next()) {
			System.out.println(String.format("Row = %d %s",rset.getInt("FIELD_A"),rset.getString("FIELD_B")));
		}
	}
}


and here the output of its execution:

Row = 1 abc
Row = 2 dce



Bye Alessandro

[Updated on: Mon, 13 September 2010 10:05]

Report message to a moderator

Previous Topic: Issues while retriving data from xmltype
Next Topic: Varchar2 to XML conversion
Goto Forum:
  


Current Time: Thu Mar 28 14:18:58 CDT 2024