Home » Developer & Programmer » JDeveloper, Java & XML » pass empty java array to associative array in plsql (10.2.0.3.0)
pass empty java array to associative array in plsql [message #384439] Tue, 03 February 2009 19:03 Go to next message
Kevin Meade
Messages: 1934
Registered: December 1999
Location: Connecticut USA
Senior Member
My java guys have only limited experience in talking with Oracle. Today we ran into a problem. We are using setPlsqlIndexTable to pass a java string array to a plsql packaged procedure. It works fine.

It works fine, until there is no data to pass, then we start getting all sort of errors.

So... how do we pass a null array to Oracle?

Also... how do we pass an empty array to Oracle? (not sure they are the same thing).

We have been trying the setNull methods and are not getting anywhere. Here is a simple plsql package spec. If you can give me the line(s) of java that will pass an emptry array to this procedure I'd appreciate it.


create or replace package pkg_array_testing is

   type t_sa is table of varchar2(4000) index by binary_integer;

   procedure dump_indexby_table ( p_array in t_sa);

end;
/
show errors


Right now everyone has settled into a HACK. Whenever they want to pass a null array, they put one element into it with a null value. They have to check and execute different code to create this, and my procedures have to unpack the array and discard the first value if empty (sucks). It works but we hate it.

Kevin

Re: pass empty java array to associative array in plsql [message #384453 is a reply to message #384439] Tue, 03 February 2009 22:19 Go to previous message
Kevin Meade
Messages: 1934
Registered: December 1999
Location: Connecticut USA
Senior Member
Found another guy at work who says he knows the solutions. I'll post it here. We will be testing it tomorrow. I thought we had done this before but maybe not. In any event the trick is to tell setPlsqlIndexTable that there are zero rows in the array. Here is his java code:

public class TestOracleProc
{
  /**
  * @param args
  */
  public static void main(String[] args) 
 {
   try
  {
   Class.forName("oracle.jdbc.driver.OracleDriver") ;

   OracleConnection conn = (OracleConnection)DriverManager.getConnection("jdbc:oracle:thin:<user/pass@connect>");
   String[] str = null;
   OraclePreparedStatement cs = (OraclePreparedStatement)conn.prepareCall( "begin pkg_array_testing.dump_indexby_table (:x); end;" );
   cs.setPlsqlIndexTable(1, str, 255, 0, OracleTypes.VARCHAR, 4000);

   cs.execute();
  }
   catch(Exception ex)
   {
     ex.printStackTrace();
     System.out.println("msg:"+ex.getMessage());
   }
 }
}


I'll post again when I find out for sure if it works or not.

Kevin
Previous Topic: Storing XML docs in Oracle 10g
Next Topic: export xml from table data (merged)
Goto Forum:
  


Current Time: Tue Jul 29 18:04:21 CDT 2014

Total time taken to generate the page: 0.10696 seconds