To close the loop for anyone else who is interested in using java rather than pl/sql I would add the following.
for IN parameters Use the oracle.sql.XXX byte array parameter format. This can be tested for null if a SQL NULL is passed.
for OUT / IN OUT parameters use a java array eg int[] is used which can be set/tested for null.
for return parameters you cannot use primitive data types if you want to return null. eg use Integer immutable object type instead of int.
So an example below to return day of the week as a number using java.util.Calendar where Sunday is 1 and Saturday is 7
import java.lang.*;
import java.util.*;
import oracle.sql.*;
public class dateUtil {
public static Integer dow( oracle.sql.DATE the_date )
{
if ( the_date == null ) return null;
Calendar cal = Calendar.getInstance();
cal.setTime( the_date.dateValue() );
return new Integer( cal.get( Calendar.DAY_OF_WEEK ) );
}
}
$ loadjava -resolve -verbose -user earthman2/password dateUtil.java
--------------------------
SQL> create or replace function day_of_the_week( the_date in date ) return number
deterministic as
language java
name 'dateUtil.dow( oracle.sql.DATE ) return java.lang.Integer';
/
select day_of_the_week( sysdate ), nvl( day_of_the_week( null ), -1 ) from dual;
Main Topics
Browse All Topics
by: annamalai77Posted on 2004-05-26 at 20:22:23ID: 11168047
hi
my friend, check the link below
http://otn.oracle.com/tech
in general, when we write a procedure with parameters, if one of the parameter has no value, then we pass it with the value "null". the same logic u can also apply to solve ur problem.
before calling the procedure , check the value of the parameter, if it is null pass the procedure with a default value or null and check in the procedure for the parameter value.
regards
annamalai