JDBC call to PLSQL block having Oracle type object parameter

Greenhorn

Posts: 6

posted 14 years ago

Number of slices to send:

Optional 'thank-you' note:

Send

Hi Guys,

I have been atempting migration from Oracle 9i to 10g(both app server and DB) for my application and am stuck on two issues related to Oracle TYPE parameters.

1. When the "Type" parameter in IN/OUT we get the "protocol violation" error, and if we change the parameter to just IN
it completes the JDBC call successfully without error.
2. Irrespective of the TYPE paramter being just IN or IN/OUT some values seem to be lost when call is made.

The code snippet below replicates what is happening in my applcation and output printed in the end, any suggestions are highly appreciated.

Thanks
-Rohit

These are the environment details

App server version : Oracle Application Server 10g 10.1.2.0.2
Database version : Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit
JDBC ver. :10.1.0.5.0

<%@ page language="java" contentType="text/html; charset=ISO-8859-1" pageEncoding="ISO-8859-1"%>
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<%@page import="java.util.*"%>
<%@page import="javax.naming.Context"%>
<%@page import="java.rmi.RemoteException"%>
<%@page import="javax.naming.InitialContext"%>
<%@page import="javax.ejb.EJBHome"%>
<%@page import="java.sql.Connection"%>
<%@page import="java.math.BigDecimal"%>
<%@page import="oracle.jdbc.OracleCallableStatement"%>
<%@page import="java.sql.*"%>
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=ISO-8859-1">
<title>Test Type Lookup</title>
</head>
<body>
<%!
public oracle.sql.ARRAY getARRAY(String a_strSQLTypeName,Object[][] a_arrObject,Connection con)
throws Exception
{
    oracle.sql.ARRAY array=null;
    if(con != null)
	{
		if(a_strSQLTypeName != null)
		{
					oracle.sql.ArrayDescriptor ad=oracle.sql.ArrayDescriptor.createDescriptor(a_strSQLTypeName.toUpperCase(),con);
					if(a_arrObject != null)
					{
						array= new oracle.sql.ARRAY(ad,con,a_arrObject);
					}
					else
					{
						throw new Exception("Null or Empty Array Object");
					}
			
		}
		else
		{
            throw new Exception("Null or Empty SQLTypeName");
		}

}
	else
	{
        throw new Exception("Unable to get the Connection");
	}
	return array;
}
public OracleCallableStatement getOracleCallableStatement(String a_strSqlString,Connection con)
throws Exception
{
	OracleCallableStatement oracleCallableStatement = null;
	
	if (con == null)
	{
	    throw new Exception("Unable to get the Connection");
	}
	
	try
	{
	    oracleCallableStatement = (OracleCallableStatement)con.prepareCall(a_strSqlString);
	}
	catch (Exception sqlex)
	{
	    throw new Exception(sqlex.getMessage());
	}
	
	return oracleCallableStatement;
}
%>
<%
	Hashtable propContext = new Hashtable();
	propContext.put(Context.INITIAL_CONTEXT_FACTORY,"com.evermind.server.rmi.RMIInitialContextFactory");
	propContext.put(Context.PROVIDER_URL,"ormi://localhost:23791//test");

Context _oCtx1 = new InitialContext(propContext);
	
	javax.sql.DataSource ds  = (javax.sql.DataSource) _oCtx1.lookup("jdbc/test");
	
	Connection con = null;
	java.sql.DatabaseMetaData dm = null;
	java.sql.ResultSet rs = null;
	
	
	try {
		con = ds.getConnection();
		Object[][] intArray=new Object[1][];
		intArray[0] = new Object[9];
		intArray[0][0]="";
		intArray[0][1]="BC132";
		intArray[0][2]="WB1242";
		intArray[0][3]= new Integer(1);
		intArray[0][4]=	"245353";
		intArray[0][5]= new BigDecimal((new Double(10.00)).doubleValue());
		intArray[0][6]= new BigDecimal(new Double(10).doubleValue());
		intArray[0][7]= "1230245";	
		intArray[0][8]= "2010-10-20 10:25:23";

oracle.sql.ARRAY array_to_pass = getARRAY("TY_TESTYPE", intArray,con);

OracleCallableStatement oCallableStatement = null;
		oCallableStatement = getOracleCallableStatement("{call COM_TEST_TYPE_PRC(?,?)}",con);
		oCallableStatement.setARRAY(1, array_to_pass);
		oCallableStatement.setInt(2,10);

oCallableStatement.execute();

if (con != null) {
			
		} else
		{
			out.println("Error: No active Connection");
		}
		
		out.println("success");
		
	} catch (Exception e) {
		out.println("failure");
		e.printStackTrace();
	}finally{
		if(con!=null)
		{
			con.close();
		}
	}
	dm = null;
%>
</body>
</html>

/*********************************************Dababase********************/

CREATE OR REPLACE 
TYPE "OBJ_TESTTYPE" AS OBJECT
(
  STR1      VARCHAR2(17),
  STR2      VARCHAR2(17),
  STR3      VARCHAR2(17),
  N1        NUMBER(3),
  STR4      VARCHAR2(12),
  D1        NUMBER(16,3),
  D2        NUMBER(16,4),
  STR5      VARCHAR2(15),
  DT1      	DATE
  );

CREATE OR REPLACE
TYPE "TY_TESTYPE" AS TABLE OF OBJ_TESTTYPE;

CREATE OR REPLACE PROCEDURE COM_TEST_TYPE_PRC (
	   pio_ty_testtype   IN OUT TY_TESTYPE,
       pi_test      	  IN     NUMBER
)
AS
BEGIN
      com_insert_exception_prc(1,1,SYSDATE,pio_ty_topup_dtl(1).str1,'pio_ty_topup_dtl(1).str1');
      com_insert_exception_prc(1,1,SYSDATE,pio_ty_topup_dtl(1).str2,'pio_ty_topup_dtl(1).str2');
      com_insert_exception_prc(1,1,SYSDATE,pio_ty_topup_dtl(1).str3,'pio_ty_topup_dtl(1).str3');
      com_insert_exception_prc(1,1,SYSDATE,pio_ty_topup_dtl(1).n1,'pio_ty_topup_dtl(1).n1');
      com_insert_exception_prc(1,1,SYSDATE,pio_ty_topup_dtl(1).d1,'pio_ty_topup_dtl(1).d1');
	  com_insert_exception_prc(1,1,SYSDATE,pio_ty_topup_dtl(1).d2,'pio_ty_topup_dtl(1).d2');
	  com_insert_exception_prc(1,1,SYSDATE,pio_ty_topup_dtl(1).str4,'pio_ty_topup_dtl(1).str4');
	  com_insert_exception_prc(1,1,SYSDATE,pio_ty_topup_dtl(1).dt1,'pio_ty_topup_dtl(1).dt1');

EXCEPTION
WHEN OTHERS
THEN
	  com_insert_exception_prc(1,1,SYSDATE,SQLERRM,SQLCODE);
END;
/

output:

STRPOLNBR	STRERRORDESC
              	pio_ty_testtype(1).str1
              	pio_ty_testtype(1).str2
              	pio_ty_testtype(1).str3
20-Oct-10 	pio_ty_testtype(1).dt1
10          	pio_ty_testtype(1).d1
10          	pio_ty_testtype(1).d2
               	pio_ty_testtype(1).str4
1             	pio_ty_testtype(1).n1

And when my army is complete, I will rule the world! But, for now, I'm going to be happy with this tiny ad:

Clean our rivers and oceans from home

https://www.kickstarter.com/projects/paulwheaton/willow-feeders