Description Eric Kwan 2013-09-10 02:34:22 EDT

Incorrect SQL binding values shown in server log for array type when logger level is set FINE. My case is calling a stored procedure with varray type of varchar parameter. The following program code runs successfully and I have checked all the parameters can be passed to the stored procedure. But, the server log incorrectly show "bind => [Testing!, null, => o_result]" where the "null" is shown incorrectly, my stored procedure actually received the array of String and successfully return back to EJB, (middleware -WebLogic Server). I have , system.out, printed the result back as "updateData... ... ... end: result=done :: 111.", where the value 111 is the String array input parameter of the stored procedure. Therefore the log shows the "null" value is not really a null to database. All the server logs in production would be not useful due to many stored procedure in my application with array input. Please help to fix!
I have tried both varray and table types are the same


EJB method
=================================

		StoredProcedureCall  spcall = new StoredProcedureCall ();
		spcall.setProcedureName("b_pl_sql_util_pkg.p_test_ins_data");
		spcall.addNamedArgument("p_val", "p_val", JDBCTypes.VARCHAR_TYPE.getSqlCode());
		spcall.addNamedArgument("i_char_array", "i_char_array", JDBCTypes.ARRAY_TYPE.getSqlCode(),"CHAR20_ARRAY");
		spcall.addNamedOutputArgument("o_result", "o_result", JDBCTypes.VARCHAR_TYPE.getSqlCode());
		
		DataReadQuery dmq = new DataReadQuery ();
		dmq.addArgument("p_val");
		dmq.addArgument("i_char_array",ArrayList.class);
		dmq.addArgument("o_result");
		
		dmq.setCall(spcall);		
		dmq.addArgumentValue("Testing!");
		char20List = new ArrayList<String>();
		char20List.add("111");		
		dmq.addArgumentValue(char20List);
		dmq.addArgumentValue("");

		UnitOfWorkImpl unitOfWork = (UnitOfWorkImpl)((JpaEntityManager)em.getDelegate()).getActiveSession();
		
		List results = (List) unitOfWork.executeQuery(dmq);
		System.out.println("updateData...[unitOfWork.executeQuery] ... ... list count="+results.size()+".");
		DatabaseRecord record= (DatabaseRecord)results.get(0);
		System.out.println("updateData... ... ... end: result="+record.getValues("o_result")+".");	


DB Stored Proc		
=============================
  procedure p_test_ins_data(p_val varchar2, i_char_array CHAR20_ARRAY,  o_result out varchar2) is
  v_num number:=100;
  begin
  insert into sw_tmp_account_sequence_audit values (p_val);
  if i_char_array is not null then 
    v_num := to_number(i_char_array(1));
  
    o_result:='done :: '||i_char_array(1);
  else
    o_result:='null array';
  end if;
  end;		
  
 DB Type 
=============================

create or replace type char20_array as table of varchar2(10)
==============================


standard out log
========================
[EL Fine]: sql: 2013-09-10 14:05:59.634--ServerSession(19207842)--Connection(10660362)--Thread(Thread[[ACTIVE] ExecuteThread: '13' for queue: 'weblogic.kernel.Default (self-tuning)',5,Pooled Threads])--BEGIN b_pl_sql_util_pkg.p_test_ins_data(p_val=>?, i_char_array=>?, o_result=>?); END;
	bind => [Testing!, null, => o_result]
updateData... ... ... end: result=done :: 111.
============================