• Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Strange problem calling oracle function with spring

 
Rashid Darvesh
Ranch Hand
Posts: 189
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 189
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
hello

can anybody please help me.

Rashid
 
Ryan Nowak
Greenhorn
Posts: 4
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 189
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Thanks Ryan
Really hard to find the reason behind this.

Anyway it works
Thanks
 
Ryan Nowak
Greenhorn
Posts: 4
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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...."
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic