Problem with stored procedure table parameter

[Rafal Mrozik]

Hello, I have the problem with stored procedure with paramater which is table type.

I have defined my own type

CREATE OR REPLACE TYPE STRING_LST AS TABLE OF VARCHAR2(80);

and i have stored procedure like this

  PROCEDURE probe(outList OUT STRING_LST) IS
  BEGIN
    outList := STRING_LST();
    outList.EXTEND();
    outList(outList.LAST) := 'ONE';
    outList.EXTEND();
    outList(outList.LAST) := 'TWO';
    outList.EXTEND();
    outList(outList.LAST) := 'THREE';
  END probe;
 

 
I have java class which connects to the database and calls this procedure
 
package mypackage;
 
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Types;
import java.sql.Array;
 
public class DAO_Test {
public static String toNormalString (String aa) {
if (aa != null && aa.startsWith("0x")) {
StringBuffer str = new StringBuffer(64);
 
for (int i = 2; i < aa.length(); i += 2) {
str.append((char) Integer.parseInt(aa.substring(i, i + 2), 16));
}
return str.toString();
} else {
return aa;
}
}
 
 
public void probe ( ) {
try {
DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver());
Connection conn = DriverManager.getConnection("dbc:oracle:thin:@server_ip:database_name", "sa", "blahblah");
 
String sql = "{ CALL PROBE(?) }";
String sqlType = "STRING_LST";
 
CallableStatement cs = conn.prepareCall(sql);
cs.registerOutParameter(1, Types.ARRAY, sqlType);
 
cs.execute();
Array arr = cs.getArray(1);
String[] values = (String[]) arr.getArray();
 
for (int i = 0; i < values.length; i++) {
System.out.println("" + i + ": '" + toNormalString(values[i]) + "'");
}
 
cs.close();
conn.close();
} catch (SQLException e) {
logger.error("SQLException", e);
} catch (Exception e) {
logger.error("Exception", e);
}
}
 
 
public static void main (String args[]) throws SQLException {
DAO_Test test = new DAO_Test();
test.probe();
}
}

Now...

When I run this class on my local machine the result is OK nad looks like
1: 'ONE'
2: 'TWO'
3: 'THREE'

But when I copy this class on the server and run it, the result is wrong
1: '???'
2: '???'
3: '???'

Here is my environment:

on local machine
eclipse 3.3.1
java 1.5 (java version compiler in eclipse is 1.4)

on server (WAS 5)
oracle 9.2.0.6.0
java 1.4.2_08

Result from table parameters are always 3 question marks :|
What is the problem?

[Rafal Mrozik]

Hmm I think i found the solution: nls_charset12.jar from page
http://www.oracle.com/technology/software/tech/java/sqlj_jdbc/htdocs/jdbc9201.html
I have copied this jar to WAS lib directory and it works fine

[Raghavan Muthu]

Thats great Rafal Mrozik. :thumb:

Welcome to JavaRanch