Integration

Hi All

I have 2 stored a procedures that I want to call from my Session bean. One of them takes some parameters but the other doesn't. When I call the procedures, I get the following errors described below. I have tested both stored procedures in sql plus and sqldveleoper and they both work OK. The funny thing is, I have another stored procedure which I can call sucessfully from the same session bean without any problems so I'm a bit confused as to why its playing up now.

I am using OC4J 10.1.3.4 standalone and Oracle 10g Db 10.2.0.4 and the DB platform that I use is Oracle10gPlatform and Eclipselink as my JPA provider. I have also tried it against toplink(not toplink essentials) and still no joy

Here is the error that I get when I try and call the procedure that takes parameters from the session bean

Internal Exception: java.sql.SQLException: ORA-06550: line 1, column 7:
PLS-00306: wrong number or types of arguments in call to 'HASHDATA'
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored

Error Code: 6550
Call: BEGIN HashData(input=>?, hashedValue=>?, hashed=>?); END;
	bind => [password1, => hashedValue, => hashed]
Query: DataReadQuery()

here are my stored procedure that take the parameters

create or replace procedure hashData(input IN VARCHAR2, hashedValue OUT BLOB, hashed OUT BOOLEAN)  as  

inputRaw RAW(2000);
--hashed BOOLEAN := false;
begin 

dbms_output.put_line('the data to be hashed is '||input);

dbms_output.put_line('converting input to raw ...');
inputRaw := utl_i18n.string_to_raw(input,'AL32UTF8');

dbms_output.put_line('Hashing Data ...');
hashedValue := DBMS_CRYPTO.Hash (src=>inputRaw,typ=>DBMS_CRYPTO.HASH_SH1);

dbms_output.put_line('hash is '||UTL_I18N.raw_to_char(dbms_lob.substr(hashedValue, 4000,1)));

if (hashedValue  is not null) then 
 

 hashed :=true;
  dbms_output.put_line('hashedValue IS NOT  null');
 else
 hashed := false;
  dbms_output.put_line('hashedValue IS null');
 end if;

end;

here is my code in the session bean to call the procedure that take parameters i.e. (Hashdata)

public UserBean() {
		// TODO Auto-generated constructor stub

		sessMan = SessionManager.getManager();
		session = sessMan.getSession("Session", Thread.currentThread()
				.getContextClassLoader());

	}

public Serializable hashData(String input){

		logger.debug("Hashing Data ... "); 
		hashedValue= null; 
		boolean hashed=false; 
	
		StoredProcedureCall call = new StoredProcedureCall();

		ValueReadQuery query = new ValueReadQuery();
		call.setProcedureName("HashData");
		call.addNamedArgumentValue("input", input);
		call.addNamedOutputArgument("hashedValue", "hashedValue", java.sql.Blob.class);
		call.addNamedOutputArgument("hashed", "hashed", java.lang.Integer.class);
				
		query.addArgument("input");
		
		query.setCall(call);
		
		session.executeQuery(query);
		 
		if ((Boolean)hashed){
			logger.debug("The data has been hashed and the hash value is: "+hashedValue);
		}
		
		else{
			logger.debug("The data has could not be hashed");
			hashedValue=null;
		}
		
		return (Serializable)hashedValue;
		}

When I call the other that doesn't take the parameters, I get the following error.

Internal Exception: java.sql.SQLException: ORA-00900: invalid SQL statement

Error Code: 900
Call: BEGIN testJPAProc(); END;
Query: DataReadQuery()

Here is the procedure that takes no parameters

create or replace procedure testJPAProc is 
 
 begin
 dbms_output.put_line('testJPAProc called');
 end;

Heres is code for calling procedures that does not take any parameters

                                StoredProcedureCall call = new StoredProcedureCall();
  
		ValueReadQuery query = new ValueReadQuery();
		
		call.setProcedureName("testJPAProc");
		query.setCall(call);
		
		session.executeQuery(query);

Can you guys tell me where I am going wrong if I am doing something wrong. Why do I get these errors? because as far as I can tell the code (i.e. java and pl/sql)are both correct unless there's something that I've missed. This is a bit funny because I have another stored procedure that I can call successfully.


Thanks