wood burning stoves 2.0*
The moose likes JDBC and the fly likes Passing array of Oracle colection objects from java to PL SQL Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Android Security Essentials Live Lessons this week in the Android forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Passing array of Oracle colection objects from java to PL SQL" Watch "Passing array of Oracle colection objects from java to PL SQL" New topic
Author

Passing array of Oracle colection objects from java to PL SQL

DeepakN kumar
Greenhorn

Joined: Apr 14, 2012
Posts: 27
Hi All,

I am trying to pass array of oracle collection object from Java to PL SQL code. The database is oracle 10g and i am using ojdbc14.jar as thin driver.Also please note that mine is a J2ee application running in Websphere Application server 6.x.

The issue is that in the PL SQl side, MAP_IN(i).codeProduct is always empty. But i am able to loop through the pl sql for loop .So i assume is that MAP_IN contains the array object but somehow i am not able to access the value in in it( i mean MAP_IN(i).codeProduct).

Please can some one help as its a very urgent requirement? Also note that I am not getting any exception from java side


Please find below the oracle type details:


create or replace type productCodeArray as table of char(6);

/

create or replace type incompatibleProduct as object(codeProduct char(6), arrayCodeProduct productCodeArray, cCanal char(4));

/

create or replace type incompatibleProductTable as table of incompatibleProduct;

/


Java code:

pl sql code:
Rob Spoor
Sheriff

Joined: Oct 27, 2005
Posts: 19656
    
  18

Welcome to the Ranch!
Can you please UseCodeTags next time? Thanks. Also, please UseOneThreadPerQuestion. I've already removed your other post.

DeepakN kumar wrote:Please can some one help as its a very urgent requirement?

Please EaseUp. There is no such thing as "urgent" around here.


SCJP 1.4 - SCJP 6 - SCWCD 5 - OCEEJBD 6
How To Ask Questions How To Answer Questions
Martin Vajsar
Sheriff

Joined: Aug 22, 2010
Posts: 3606
    
  60

I don't see anything outright wrong with it. I assume you've checked the obvious (namely, that the values you're expected to see in PL/SQL are actually being sent; I would suggest to add extensive logging of the values into the populateObjectArraylist method to be sure).

You might try to use a newer Oracle driver. You can use the 11g thin driver (ojdbc5.jar or ojdbc6.jar), as it is fully compatible with 10g database. (It has some unrelated advantages over older drivers, such as mapping JDBC batch updates into Oracle's ARRAY inserts.)

I've also found this in the 11g driver documentation:
oracle.sql.CHAR is not an exact representation of the data in Oracle format. oracle.sql.CHAR is constructed from java.lang.String. There is no advantage of using oracle.sql.CHAR because java.lang.String is always faster and represents the same character sets, excluding a couple of desupported character sets.

Note:
Oracle strongly recommends you to use standard Java types and convert any existing oracle.sql type of data to standard Java types. Internally, the Oracle JDBC drivers strive to maximize the performance of Java standard types. oracle.sql types are supported only for backward compatibility and their use is discouraged.

If you post an SSCCE, I can try to dig deeper in it.
DeepakN kumar
Greenhorn

Joined: Apr 14, 2012
Posts: 27
THanks for the reply. I have now simplified the code and am only passing Oracle type “incompatibleProduct” and no array of object this time. Still I am not able to get the value at the PL SQL side. Also I have no exception from Java and PL SQL. I have not tried with the 11g thin driver yet.Please see the code below.


(1) Java Code:


(2) PL SQL:


(3) Create Oracle Type:


(4) Output printed in table: test1( codeProduct is not printed):


SQL> select * from test1;

A
------------------------------------------
MAP IN:-


Martin Vajsar
Sheriff

Joined: Aug 22, 2010
Posts: 3606
    
  60

I've executed your example as it is (good job providing the SSCCE, by the way ) and it worked for me:


As I've said earlier, I'm on 11g database/driver. I'd suggest to switch to 11g driver and try again.

Note: I've added code tags to your post again to make it more readable, you can do so by using the Code button.
DeepakN kumar
Greenhorn

Joined: Apr 14, 2012
Posts: 27
I have added the ojdbc5.jar in classpath but still the issue persists :-(. Is the issue due to Oracle 10g DataBase?
Also, the datumArray of Struct is displaying value as ??? for mapKey and canal. Is this the reason why the value is not reaching PL/SQL? This was also an issue with ojdbc1.4.jar. Thats why i tried using CHAR.
Also i have not updated the datasource of Websphere and it is still pointing to ojdbc1.4.jar but i think that shouldn't be a problem as i am using driverManager.getConenction().

Could you please send me the email id of your's so that i can send the screenshot of the datumArray ?
Martin Vajsar
Sheriff

Joined: Aug 22, 2010
Posts: 3606
    
  60

I'm not sure whether the issue could be connected to Oracle 10g. We still try to make sure the driver version is correct (see below).

Regarding the driver version: I've run your example in a standalone project and I'd suggest you to do the same to be absolutely sure you're using the driver version you want to. You've already everything set up, it took me just a few minutes to run your test in an independent project on my side. I'd say you should not go further before trying this. I'm not that skilled in app servers to be able to tell whether the Websphere having a datasource with the old driver can pose a problem.

I've no idea what datumArray is, it is was not mentioned so far. What you describe seems like an encoding or font issue. If you want to post a screenshot, it is possible here too; just click on the Attachments tab below the textbox where you write your post. (It is always better to keep the discussion on the forum so that other readers can also benefit from it; see UseTheForumNotEmail.)
DeepakN kumar
Greenhorn

Joined: Apr 14, 2012
Posts: 27
I will try that and let you know. By the way please find attached the datumArray screenshot. Can you just see from your side that you are also getting the value as ???



[Thumbnail for struct_datumArray.JPG]

Martin Vajsar
Sheriff

Joined: Aug 22, 2010
Posts: 3606
    
  60

I've re-run your example with 10.2 JDBC driver (still 11G database though) and it worked as well. I also see the value of the fields in the datumArray field correctly (S123 and C3).

I've no idea what to do next. It might be a character encoding issue, though it is unclear to me in which encoding the string "S123" could cause problems.

Let's see what happens if you run that outside of Websphere.
DeepakN kumar
Greenhorn

Joined: Apr 14, 2012
Posts: 27
It works fine in standalone application with both ojdbc1.4.jar and ojdbc5.jar. But the same code fails when run inside the Websphere application server 6.x :-(.
Any idea how to make it work in websphere?
Martin Vajsar
Sheriff

Joined: Aug 22, 2010
Posts: 3606
    
  60

There might (just might) be a problem in NLS setup. Run the following statement:
from your application and just write out this output somewhere (eg. on the console or to the log). Try to compare the output you get from Websphere app with the standalone app.

Post the result here.
DeepakN kumar
Greenhorn

Joined: Apr 14, 2012
Posts: 27

Please find the values from both standalone and Websphere. Both seems similar.

StandAlone:


param=NLS_LANGUAGE value=AMERICAN

param=NLS_TERRITORY value=AMERICA

param=NLS_CURRENCY value=$

param=NLS_ISO_CURRENCY value=AMERICA

param=NLS_NUMERIC_CHARACTERS value=.,

param=NLS_CALENDAR value=GREGORIAN

param=NLS_DATE_FORMAT value=DD.MM.RR

param=NLS_DATE_LANGUAGE value=AMERICAN

param=NLS_SORT value=BINARY

param=NLS_TIME_FORMAT value=HH.MI.SSXFF AM

param=NLS_TIMESTAMP_FORMAT value=DD-MON-RR HH.MI.SSXFF AM

param=NLS_TIME_TZ_FORMAT value=HH.MI.SSXFF AM TZR

param=NLS_TIMESTAMP_TZ_FORMAT value=DD-MON-RR HH.MI.SSXFF AM TZR

param=NLS_DUAL_CURRENCY value=$

param=NLS_COMP value=BINARY

param=NLS_LENGTH_SEMANTICS value=BYTE

param=NLS_NCHAR_CONV_EXCP value=FALSE



inside Websphere:


param=NLS_LANGUAGE value=AMERICAN

param=NLS_TERRITORY value=AMERICA

param=NLS_CURRENCY value=$

param=NLS_ISO_CURRENCY value=AMERICA

param=NLS_NUMERIC_CHARACTERS value=.,

param=NLS_CALENDAR value=GREGORIAN

param=NLS_DATE_FORMAT value=DD-MON-RR

param=NLS_DATE_LANGUAGE value=AMERICAN

param=NLS_SORT value=BINARY

param=NLS_TIME_FORMAT value=HH.MI.SSXFF AM

param=NLS_TIMESTAMP_FORMAT value=DD-MON-RR HH.MI.SSXFF AM

param=NLS_TIME_TZ_FORMAT value=HH.MI.SSXFF AM TZR

param=NLS_TIMESTAMP_TZ_FORMAT value=DD-MON-RR HH.MI.SSXFF AM TZR

param=NLS_DUAL_CURRENCY value=$

param=NLS_COMP value=BINARY

param=NLS_LENGTH_SEMANTICS value=BYTE

param=NLS_NCHAR_CONV_EXCP value=FALSE

Martin Vajsar
Sheriff

Joined: Aug 22, 2010
Posts: 3606
    
  60

Indeed, this does not seem to be the problem.

It might still be worth a try to change the driver in Websphere datasource, though I don't give it much chance. I cannot think of anything else to try.
DeepakN kumar
Greenhorn

Joined: Apr 14, 2012
Posts: 27
Finally I managed to solve this issue :-).

Please find below a working code for passing oracle collection object from Java to PL SQL in Websphere Application Server 6.1. After the below code, i have also explained the set-up required in Websphere (and other application servers may be) to make it work.

Also note that the Database is Oracle 10g and i am using oracle thin driver ojdbc14.jar.



The set-up required to make it work:

The reason why the values were not coming in the PL SQL side was because of NLS_CHARACTERSET issue. The java client side was not able to resolve to the character set of the Oracle DB.

Also note that " Nearly all JDBC character set conversions for CHAR, VARCHAR and NCHAR etc. happen on the server side, (i.e. using the NLSRTL engine on the database side), so typically no client side character set library (NLS_CHARSET12.jar) is needed.

Client character set conversions are required for Oracle OBJECTS and COLLECTION TYPES only. Now if your db character set and the client character set are either UTF8, WE8DEC, US7ASCII or ISO-Latin-1, then NLS_CHARSET12.jar is not needed also; because they can be supported in CLASSES12.jar .

The only time you need NLS_CHARSET12.jar is when you are using OBJECTS or COLLECTION TYPES, and your db and/or client character set are not one of the 4 previously listed character sets."


(1) Since we are using oracle collection and objects, it means that we need to solve the client side character set conversion. To solve the character set conversion on client side, CLASSES12.jar is required. Apart from that we need an additional jar file called: NLS_CHARSET12.jar because our database NLS_CHARACTERSET is WE8ISO8859P15 which is different from the above set mentioned. Below sql query can be used to find this.

SELECT * FROM nls_database_parameters WHERE parameter IN ('NLS_CHARACTERSET', 'NLS_NCHAR_CHARACTERSET’)

(2) Now I have added the two jar files: CLASSES12.jar & NLS_CHARSET12.jar to the classpath. These jar files are available in the oracle DB side at the path ${ORACLE_HOME}/jdbc/lib.

(3) However, adding the above jar files to class path didn’t solve the issue and I was still not able to see the values at the PL SQL side. On further checking I could see that this was a classloader issue. The classloader used to load the ear file and the above mentioned jar files were different and hence these jar files were not visible to the application. So I have placed these jar files in the path: jre\lib\ext as below:
D:\VS\IBM\WebSphere\AppServer\java\jre\lib\ext à If the JRE used is Websphere Application Server JRE
D:\VS\IBM\WebSphere\AST\runtimes\base_v61_stub\java\jre\lib\extà If the JRE used is Websphere JRE.
(4) This actually resolved the issue and I am able to see the values at the PL SQL side.


How to create Oracle Type



PL SQL


Martin Vajsar
Sheriff

Joined: Aug 22, 2010
Posts: 3606
    
  60

Thanks a lot for sharing the solution!
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Passing array of Oracle colection objects from java to PL SQL
 
Similar Threads
Passing in array parameter to an Oracle stored procedure
How to pass java objects to PL/SQL procedures?
Need Expert Advice
Java - Passing Object to Storeprocedure (Spring JDBCTemplate)
Need Help with pl/sql (Compilation Error)