Manju Krishna

I am trying to call a stored proc having table type IN and OUT parameter using jdbc.

When I tried as a standalone program I am not having any issues. Once I deployed the code in Tomcat, the below esxception is thrown

java.lang.ClassCastException: $Proxy0 cannot be cast to oracle.jdbc.OracleConnection
at oracle.sql.ArrayDescriptor.createDescriptor(ArrayDescriptor.java:160)
at oracle.sql.ArrayDescriptor.createDescriptor(ArrayDescriptor.java:128)


on executing this line of code,

arrayDescriptor = ArrayDescriptor.createDescriptor("ORG_HOOK_LIST", connection);

Upon analysis I found that the connection object I get here is of type,oracle.jdbc.driver.T4CConnection. But as per the code it expects a oracle.jdbc.OracleConnection.
I tried adding the ojdbc6.jar in tomcat/lib/common folder as suggested in few forums but did not help.

Please suggest a feasible solution.

Posting the solution
Java Code,
package test; import java.sql.Array; import java.sql.Connection; import java.sql.DriverManager; import java.sql.ResultSetMetaData; import java.sql.Struct; import java.sql.Types; import oracle.jdbc.OracleCallableStatement; import oracle.jdbc.OracleTypes; import oracle.sql.ARRAY; import oracle.sql.ArrayDescriptor; import oracle.sql.StructDescriptor; public class TableTypeWithObject { public static void main(String[] args) { new TableTypeWithObject().execute(); } public void execute(){ Connection connection = null; oracle.sql.ARRAY array =null; ArrayDescriptor arrayDescriptor = null; OracleCallableStatement cStmt = null; try{ Class.forName("oracle.jdbc.driver.OracleDriver"); connection = DriverManager.getConnection("jdbc:oracle:thin:@msi-vmpl3db2:1521:pl11gdb","pranaka","pranaka"); arrayDescriptor = ArrayDescriptor.createDescriptor("ORG_HOOK_LIST", connection); InputBean input[]= new InputBean[2]; input[0]=new InputBean(); input[0].setParamName("moving_node"); input[0].setParamVal("100000"); input[1]=new InputBean(); input[1].setParamName("parent_node"); input[1].setParamVal("200000"); Object[] objDetailList=null; objDetailList=new Object[input.length]; for (int i = 0; i < objDetailList.length; i++){ getInputArray(input[i]); } array = new oracle.sql.ARRAY(arrayDescriptor, connection,objDetailList); final String typeName = "ORG_HOOK_OBJECT"; final String typeTableName = "ORG_HOOK_LIST"; final StructDescriptor structDescriptor = StructDescriptor.createDescriptor(typeName.toUpperCase(), connection); final ResultSetMetaData metaData = structDescriptor.getMetaData(); cStmt =(OracleCallableStatement)connection.prepareCall("{call test_table_type_with_object(?,?)}"); cStmt.setArray(1, array); cStmt.registerOutParameter(2, Types.ARRAY, typeTableName); cStmt.execute(); Object[] data = (Object[]) ((Array) cStmt.getObject(2)).getArray(); for(Object tmp : data) { Struct row = (Struct) tmp; // Attributes are index 1 based... int idx = 1; for(Object attribute : row.getAttributes()) { System.out.println(metaData.getColumnName(idx) + " = " + attribute); ++idx; } System.out.println("---"); } cStmt.close(); connection.close(); }catch (Exception ex){ ex.printStackTrace(); } } public class InputBean { private String param_name; private String param_val; public InputBean(String param_name,String param_val){ this.param_name=param_name; this.param_val=param_val; } public InputBean(){ } /** * @return the canal */ public String getParamVal() { return this.param_val; } /** * @param canal the canal to set */ public void setParamVal(String paramVal) { this.param_val = paramVal; } /** * @return the mapKey */ public String getParamName() { return this.param_name; } /** * @param mapKey the mapKey to set */ public void setParamName(String paramName) { this.param_name = paramName; } } public static Object[] getInputArray(InputBean it){ Object[] objArr = new Object[2]; objArr[0]=it.getParamName(); objArr[1]=it.getParamVal(); return objArr; } }

SQL Table Types -

create or replace TYPE org_hook_object AS OBJECT ( param_name varchar2(1000), param_val varchar2(1000) ); create or replace TYPE org_hook_list IS TABLE OF org_hook_object;

SQL Procedure,

create or replace PROCEDURE test_table_type_with_object (inParam IN org_hook_list,outParam OUT org_hook_list) IS i NUMBER; name1 VARCHAR2(1000); val1 VARCHAR2(10000); BEGIN outParam := org_hook_list(); outParam.extend(2); FOR i IN inParam.FIRST .. inParam.LAST LOOP name1 := inParam(i).param_name || ' Out Param Name '|| i; val1 := inParam(i).param_val || ' Out Param Val '|| i; insert into test123 (name1,value1) values (name1,val1); outParam(i) := org_hook_object(name1,val1); END LOOP; END test_table_type_with_object;

I changed the type Name used in below line ,

statement.registerOutParameter(2,OracleTypes.ARRAY,"pre_hook_out_paramList");

to upper case and it worked. I have the complete code which takes both IN and OUT param as a table type.

Can someone let me know on where I can post this code so that others can benefit from the same.

Cheers

Per my last comment, the typeName variable is not used in my java class.

Re-posting all the java and SQL code

package test; import java.sql.Connection; import java.sql.DriverManager; import java.sql.SQLException; import oracle.jdbc.OracleCallableStatement; import oracle.jdbc.OracleTypes; import oracle.sql.ARRAY; public class Approach1 { /** * @param args */ public static void main(String[] args) { Connection connection = null; String query = "call test_table_type_only_out(?,?)"; try { Class.forName("oracle.jdbc.driver.OracleDriver"); connection = DriverManager.getConnection("jdbc:oracle:thin:@msi-vmpl3db2:1521:pl11gdb","pranaka","pranaka"); OracleCallableStatement statement = null; statement = (OracleCallableStatement)connection.prepareCall("{call test_table_type_only_out(?,?)}"); statement.setString(1, "Manju"); statement.registerOutParameter(2,OracleTypes.ARRAY,"pre_hook_out_paramList"); statement.execute(); ARRAY simpleArray = statement.getARRAY(1); System.out.println ("Array is of type " + simpleArray.getSQLTypeName()); System.out.println ("Array element is of type code " + simpleArray.getBaseType()); System.out.println ("Array is of length " + simpleArray.length()); String[] values = (String[])simpleArray.getArray(); for( int i = 0; i < values.length; i++ ) System.out.println( "row " + i + " = '" + values[i] + "'" ); statement.close(); connection.close(); } catch (ClassNotFoundException e) { // TODO Auto-generated catch block e.printStackTrace(); } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } } }
The type created as
create or replace type pre_hook_out_paramList is table of varchar2(1000);

Stored Procedure ,
create or replace PROCEDURE test_table_type_only_out ( inParam IN VARCHAR2 ,outParam OUT pre_hook_out_paramList ) IS i NUMBER; BEGIN outParam := pre_hook_out_paramList(); outParam.extend(2); outParam(1) := inParam; outParam(2) := inParam || ' Second Element'; END test_table_type_only_out;

Let me know od any further details

I posted a different class's exception wrongly,

the exception is java.sql.SQLException: invalid name pattern: PRANAKA.pre_hook_out_paramList

and these two lines part of earlier code is not there,
final StructDescriptor structDescriptor = StructDescriptor.createDescriptor(typeName.toUpperCase(), connection); final ResultSetMetaData metaData = structDescriptor.getMetaData();

Actual Class
package test; import java.sql.Connection; import java.sql.DriverManager; import java.sql.SQLException; import oracle.jdbc.OracleCallableStatement; import oracle.jdbc.OracleTypes; import oracle.sql.ARRAY; public class Approach1 { /** * @param args */ public static void main(String[] args) { Connection connection = null; String query = "call test_table_type_only_out(?,?)"; try { Class.forName("oracle.jdbc.driver.OracleDriver"); connection = DriverManager.getConnection("jdbc:oracle:thin:@msi-vmpl3db2:1521:pl11gdb","pranaka","pranaka"); OracleCallableStatement statement = null; statement = (OracleCallableStatement)connection.prepareCall("{call test_table_type_only_out(?,?)}"); statement.setString(1, "Manju"); statement.registerOutParameter(2,OracleTypes.ARRAY,"pre_hook_out_paramList"); statement.execute(); ARRAY simpleArray = statement.getARRAY(1); System.out.println ("Array is of type " + simpleArray.getSQLTypeName()); System.out.println ("Array element is of type code " + simpleArray.getBaseType()); System.out.println ("Array is of length " + simpleArray.length()); String[] values = (String[])simpleArray.getArray(); for( int i = 0; i < values.length; i++ ) System.out.println( "row " + i + " = '" + values[i] + "'" ); statement.close(); connection.close(); } catch (ClassNotFoundException e) { // TODO Auto-generated catch block e.printStackTrace(); } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } } }

I am trying to call a stored procedure from java using JDBC. The stored procedure is having table type out parameter.
import java.sql.CallableStatement; import java.sql.Connection; import java.sql.DriverManager; import java.sql.SQLException; import java.sql.Struct; import java.sql.Types; import java.sql.Array; import java.sql.ResultSetMetaData; import oracle.jdbc.OracleCallableStatement; import oracle.jdbc.OracleTypes; import oracle.sql.ARRAY; import oracle.sql.StructDescriptor; public class TestTableTypeCall { /** * @param args */ public static void main(String[] args) { Connection connection = null; String query = "call test_table_type_only_out(?,?)"; try { Class.forName("oracle.jdbc.driver.OracleDriver"); connection = DriverManager.getConnection("jdbc:oracle:thin:@msi-vmpl3db2:1521:pl11gdb","pranaka","pranaka"); // Get a description of your type (Oracle specific) final StructDescriptor structDescriptor = StructDescriptor.createDescriptor(typeName.toUpperCase(), connection); final ResultSetMetaData metaData = structDescriptor.getMetaData(); OracleCallableStatement statement = null; statement = (OracleCallableStatement)connection.prepareCall("{call test_table_type_only_out(?,?)}"); statement.setString(1, "Manju"); statement.registerOutParameter(2,OracleTypes.ARRAY,"pre_hook_out_paramList"); statement.execute(); ARRAY simpleArray = statement.getARRAY(1); System.out.println ("Array is of type " + simpleArray.getSQLTypeName()); System.out.println ("Array element is of type code " + simpleArray.getBaseType()); System.out.println ("Array is of length " + simpleArray.length()); String[] values = (String[])simpleArray.getArray(); for( int i = 0; i < values.length; i++ ) System.out.println( "row " + i + " = '" + values[i] + "'" ); statement.close(); connection.close(); } catch (ClassNotFoundException e) { // TODO Auto-generated catch block e.printStackTrace(); } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } } }


The Stored procedure is,
create or replace PROCEDURE test_table_type_only_out ( inParam IN VARCHAR2 ,outParam OUT pre_hook_out_paramList ) IS i NUMBER; BEGIN outParam := pre_hook_out_paramList(); outParam.extend(2); outParam(1) := inParam; outParam(2) := inParam || ' Second Element'; END test_table_type_only_out;

and created the type like,
create or replace type pre_hook_out_paramList is table of varchar2(1000);

When I run my jave class I get an SQLException,
java.sql.SQLException: invalid name pattern: pranaka.pre_hook_out_list

I tried going through existing forums, but did not get an answer to my current problem. Please help

Hi,
I need to format the given date in "yyyy-MMM-dd HH:mm:ss" format for any locale and the given date is in "yyyy-MM-dd HH:mm:ss" this format.

import java.text.ParseException; import java.text.SimpleDateFormat; import java.util.Date; import java.util.Locale; import java.util.TimeZone; public class SDF { public SimpleDateFormat makeDateFormatter(String pattern, Locale l, TimeZone tz) { SimpleDateFormat sdf = null; if (pattern != null && pattern.length() > 0) { Locale locale = l != null ? l : null; if (locale == null) sdf = new SimpleDateFormat(pattern); else sdf = new SimpleDateFormat(pattern, locale); /* * TimeZone timezone = tz != null?tz:null; if (timezone != null) * sdf.setTimeZone(timezone); */ } return sdf; } public static void main(String[] args) { String inString = "2011-08-04 11:30:00"; String pattern = "yyyy-MM-dd HH:mm:ss"; String output; Date now = null; String pattern2 = "yyyy-MMM-dd HH:mm:ss a"; //Locale locale2 = new Locale("es", "MX"); Locale locale2 = new Locale("ja", "JP"); //Locale locale2 = new Locale("en", "US"); TimeZone tz = TimeZone.getTimeZone("America/Los_Angeles"); SDF dt = new SDF(); SimpleDateFormat format = dt.makeDateFormatter(pattern, locale2, tz); SimpleDateFormat format2 = dt.makeDateFormatter(pattern2, locale2, tz); try { now = format.parse(inString); } catch (ParseException pe) { System.out.println("unparsable date"); } if (now == null) { output = ""; } else { synchronized (format2) { output = format2.format(now); } } System.out.println(" output " + output); } }


The output of the code for en_US and es_MX(spanish) are fine and are respectively the following
en_US -- > 2011-Aug-04 11:30:00
es_MX -- > 2011-ago-04 11:30:00

But for ja_JP -- > 2011-8-04 11:30:00 (The months MMM is returned as single digit M).
Should we have any special handling for japanese (ja_JP) ?
Please help ..

Thanks

I read through the article now. Jconsole has tabs to display Mbean data and there are other tabs to view VM statistics. Like threads used, CPU usage, classes loaded etc.. How can I get those information ?
The MC4J client just reads the Mbean properties and gives that, which is again a third party tool.
I wanted to know if I can leverage the JMX output to my simple Java application to display JVM details. Hope I conveyed it right
Please help.

I also wanted to know if there is a way to obtain the runtime JVM details of an application without using jConsole.
In my tool I do not want another UI like Jconsole to display data. I need those data and I would format a report as I need

I am developing a monitoring tool for our webhosted application. I have a skeleton structure of the application and one service to monitor HTTP status is implemented. I need to know about integrating JMX with my application.

Googling only gave results to integrate with jconsole. I need to know if we can represent the JMX data in our own application. Also can any one provide with any examples to obtain data.

I am having problems is starting up with this. Please help. Also didn’t find corrrect forum to post this. so posted in this java general forum.

Thanks for all your replies!

I have generated a jks file and using that for encrypt and decrypt.!
Moreover I am also using a third party helper called bouncycastle

So when i have encrypted with those keytool and password I try to decrypt using the same !

Hello All,

Is there any way to find if the particular file instance is encrypted or not.
One solution what I had was to decrypt and check if the exception is thrown else continue. But it would be an overhead for users not using encryption.

Please help on alternatives . Thanks !

Thanks Rene, this works..

Though it process the lines no of times the desired output is obtained. I can minimise the call to this function to handle data with this special processing.

Thanks a lot for each of your inputs.

That solves the (,) problem. But how about this string ?
eSOMS 'warning' line 2.

I was also got with this vicious circle..