my dog learned polymorphism*
The moose likes Other Application Frameworks and the fly likes Strange problem calling oracle function with spring Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Frameworks » Other Application Frameworks
Bookmark "Strange problem calling oracle function with spring" Watch "Strange problem calling oracle function with spring" New topic
Author

Strange problem calling oracle function with spring

Rashid Darvesh
Ranch Hand

Joined: Feb 13, 2004
Posts: 189
hi guys
i have a very simple function getname which return string
FUNCTION GETNAME RETURN VARCHAR2 IS
BEGIN
RETURN 'PORTAL';
END;
everytng is fine and i call it using spring stored procedure class
public class MyStoredProcedure extends StoredProcedure{
private static final String SQL = "GETNAME";
public MyStoredProcedure(DataSource ds) {
setDataSource(ds);
setFunction(true);
setSql(SQL);
declareParameter(new SqlOutParameter("returnname", Types.VARCHAR));
compile();
}

public Map execute() {
Map inputs = new HashMap();
return execute(inputs);
}
}
and this is my main class which tests it
public class StoredProcedureTest {
public static void main(String[] args) {
StoredProcedureTest t = new StoredProcedureTest();
t.test();
System.out.println("Done!");
}

void test() {
DriverManagerDataSource ds = new DriverManagerDataSource();
ds.setDriverClassName("oracle.jdbc.OracleDriver");
ds.setUrl("jdbc racle:thin:@xxx:1521:xxx");
ds.setUsername("xx");
ds.setPassword("xx");
try{
MyStoredProcedure sproc = new MyStoredProcedure(ds);

Map results = sproc.execute();
printMap(results);
}catch(Exception e){
e.printStackTrace();
}
}

private static void printMap(Map results) {
for (Iterator it = results.entrySet().iterator(); it.hasNext(); ) {
System.out.println(it.next());
}
}
}
This works fine and i get the result as portal.
the issue happens is when i add a parameter to getname function so my oracle function looks like
FUNCTION GETNAME (username IN VARCHAR2) RETURN VARCHAR2 IS
BEGIN
RETURN 'PORTAL';
END;
now i get the returnname output value as null the follogin is the changes to my Stored procedure class, i add one input parameter and now in the main test class i get the return name variable as empty

public class MyStoredProcedure extends StoredProcedure{
private static final String SQL = "GETNAME";
public MyStoredProcedure(DataSource ds) {
setDataSource(ds);
setFunction(true);
setSql(SQL);
declareParameter(new SqlParameter("username",Types.VARCHAR));
declareParameter(new SqlOutParameter("returnname", Types.VARCHAR));
compile();
}

public Map execute() {
// the 'sysdate' sproc has no input parameters, so an empty Map is supplied...
Map inputs = new HashMap();
inputs.put("username","john");
return execute(inputs);
}
}

May i know what i am doing wrong

thanks
Rashid Darvesh
Ranch Hand

Joined: Feb 13, 2004
Posts: 189
hello

can anybody please help me.

Rashid
Ryan Nowak
Greenhorn

Joined: Dec 06, 2007
Posts: 4
Rashid,

Put the return parameter first in your declaration.

This was hard to find in the documentation, I struggled with it for a while.
Rashid Darvesh
Ranch Hand

Joined: Feb 13, 2004
Posts: 189
Thanks Ryan
Really hard to find the reason behind this.

Anyway it works
Thanks
Ryan Nowak
Greenhorn

Joined: Dec 06, 2007
Posts: 4
If you try running with debugging on in your logger it will display the sql string that it generates, of the form :

? = call Schema.Package.Function(?, ?, ?)

Where using unnamed parameters (thats what StoredProcedure does), they are bound in order of declaration. So that's why the return parameter must go first. The reasoning seems totally reasonable to me, but this should be documented.

The isFunction boolean property determines whether there is a "? = " at the beginning. So without setting it to a function, you would just have "call Schema...."
 
 
subject: Strange problem calling oracle function with spring