Hi All,

I am using Spring Framework and Hibernate...I have a stored procedure which is to called from the program...

Stored Proc is :

CREATE OR REPLACE PROCEDURE getDeptEntries(dnam OUT VARCHAR2,dno IN NUMBER)
AS
sCall VARCHAR2(500);
nTotal VARCHAR2(50);
BEGIN
sCall := 'SELECT dname FROM DEPT WHERE DEPTNO = :dno';
EXECUTE IMMEDIATE sCall INTO nTotal;
dnam := nTotal;
end getDeptEntries;

And the PRogram is......

package secondEx;

import java.sql.Types;
import java.util.HashMap;
import java.util.Iterator;
import java.util.Map;

import javax.sql.DataSource;

import org.springframework.jdbc.core.SqlOutParameter;
import org.springframework.jdbc.core.SqlParameter;
import org.springframework.jdbc.datasource.DriverManagerDataSource;
import org.springframework.jdbc.object.StoredProcedure;

public class TestSP {

public static void main(String[] args) {

System.out.println("DB TestSP!");
TestSP t = new TestSP();
t.test();
System.out.println("Done!");
}

void test() {
DriverManagerDataSource ds = new DriverManagerDataSource();
ds.setDriverClassName("oracle.jdbc.OracleDriver");
ds.setUrl("jdbc:oracle:thin:@localhost:1521:sequenom");
ds.setUsername("sequenom");
ds.setPassword("sequenom");

MyStoredProcedure sproc = new MyStoredProcedure(ds);
Map res = sproc.execute();
printMap(res);
System.out.println("fin execute" );
}

private class MyStoredProcedure extends StoredProcedure {
public static final String SQL = "getDeptEntries";
private int count = 0;

public MyStoredProcedure(DataSource ds) {
super(ds,SQL);
setSql(SQL);
declareParameter(new SqlOutParameter("dname",Types.VARCHAR));
declareParameter(new SqlParameter("deptno",Types.INTEGER));
compile();
}

public int getCount() {
return count;
}

public Map execute() {
Map inParameters = new HashMap(1);
inParameters.put( "deptno",new Integer(21) );

Map out = execute(inParameters);
System.out.println(out);
return out;
}
}

private static void printMap(Map r) {
Iterator i = r.entrySet().iterator();
while (i.hasNext()) {
System.out.println((String) i.next().toString());
}
}
}


When I run this...I am getting the following error:


Exception in thread "main" org.springframework.jdbc.UncategorizedSQLException: CallableStatementCallback; uncategorized SQLException for SQL [{call getDeptEntries(?, ?)}]; SQL state [72000]; error code [1008]; ORA-01008: not all variables bound
ORA-06512: at "SEQUENOM.GETDEPTENTRIES", line 7
ORA-06512: at line 1
; nested exception is java.sql.SQLException: ORA-01008: not all variables bound
ORA-06512: at "SEQUENOM.GETDEPTENTRIES", line 7
ORA-06512: at line 1

java.sql.SQLException: ORA-01008: not all variables bound
ORA-06512: at "SEQUENOM.GETDEPTENTRIES", line 7
ORA-06512: at line 1



Thnx in advance..