• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

Passing array of Oracle colection objects from java to PL SQL

 
Greenhorn
Posts: 27
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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:
 
Sheriff
Posts: 22783
131
Eclipse IDE Spring VI Editor Chrome Java Windows
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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.
 
Sheriff
Posts: 3837
66
Netbeans IDE Oracle Firefox Browser
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Posts: 27
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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 Vashko
Sheriff
Posts: 3837
66
Netbeans IDE Oracle Firefox Browser
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Posts: 27
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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 Vashko
Sheriff
Posts: 3837
66
Netbeans IDE Oracle Firefox Browser
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Posts: 27
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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 ???
struct_datumArray.JPG
[Thumbnail for struct_datumArray.JPG]
struct_datumArray
 
Martin Vashko
Sheriff
Posts: 3837
66
Netbeans IDE Oracle Firefox Browser
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Posts: 27
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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 Vashko
Sheriff
Posts: 3837
66
Netbeans IDE Oracle Firefox Browser
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Posts: 27
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

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 Vashko
Sheriff
Posts: 3837
66
Netbeans IDE Oracle Firefox Browser
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Posts: 27
  • Likes 2
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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 Vashko
Sheriff
Posts: 3837
66
Netbeans IDE Oracle Firefox Browser
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Thanks a lot for sharing the solution!
 
reply
    Bookmark Topic Watch Topic
  • New Topic