| pass empty java array to associative array in plsql [message #384439] |
Tue, 03 February 2009 19:03  |
 |
Kevin Meade
Messages: 2103
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  |
|
Kevin Meade
Messages: 2103
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
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
