>我在 Oracle 数据库中有以下存储过程
create or replace procedure cust_details(idValue Custdetails.id_value%type,
idUser Custdetails.id_user%type,
idType Custdetails.id_type%type,
nickName Custdetails.nickname%type,
imageData Custdetails.IMAGE%type) IS
bene_id number(10);
current_seq number(10);
isExist number(2);
BEGIN
select count(*)
into isExist
from iddetails
where id_value = idValue;
if isExist = 0 then
select beneficiary_seq.nextval into current_seq from dual;
insert into details
(beneficiary_id, date_added)
values
(current_seq, sysdate);
insert into Custdetails
(beneficiary_id, id_user, nickname,image)
valuesnumber
(current_seq, idUser, nickName,imageData);
insert into iddetails
(beneficiary_id, id_type, id_value)
values
(current_seq, idType, idValue);
else
select beneficiary_id
into bene_id
from iddetails
where id_value = idValue;
insert into Custdetails
(beneficiary_id, id_user, nickname,image)
values
(bene_id, idUser, nickName,imageData);
end if;
END;
--Custdetails.idValue is varchar2(40)
--Custdetails.IMAGE is Blob
我从Java类调用它,如下所示:
CallableStatement callableStatement = con.prepareCall("{CALL cust_details(?,?,?,?,?)}");
callableStatement.setString(1, "9855154");
callableStatement.setString(2, "123");
callableStatement.setString(3, "sdsd");
callableStatement.setString(4, "ssdsd");
byte [] imageData = l_rs.getBlob (0); // setting bytes
if(// if byte array has data){
Blob imgDataBlob = con.createBlob();
imgDataBlob.setBytes(1, imageData);
callableStatement.setBlob(5, imgDataBlob);
}
else{
// even this is giving same error i also tried OracleTypes.Blob
callableStatement.setNull (2, java.sql.Types.BLOB);
}
callableStatement.execute();
但是调用此过程在 if 和 else 情况下都会产生以下错误
由以下原因引起:java.sql.SQLException:ORA-06550:第 1 行,第 7 列: PLS-00306:调用"CUST_DETAILS"时参数的数量或类型错误
知道我在这里做错了什么吗
我有以下工作正常,我正在使用的驱动程序Ojdbc14.jar
CREATE OR REPLACE PROCEDURE my_proc (idvalue NUMBER,
imagedata tobedeleted.img%TYPE
)
IS
BEGIN
INSERT INTO tobedeleted
VALUES (idvalue, imagedata
);
END my_proc;
爪哇代码
ResultSet res = sta.executeQuery(
"SELECT * FROM TOBEDELETED for update ");
while (res.next()) {
myblob = res.getBlob("IMG");
}
callablestatement =
conn.prepareCall("{CALL my_proc(?,?)}");
callablestatement.setInt(1, 100);
if(myblob==null){
callablestatement.setNull(2,java.sql.Types.BLOB);
}
else {
byte[] chuck = {(byte)0x00, (byte)0x00, (byte)0x00,
(byte)0x00, (byte)0x00, (byte)0x00};
myblob.setBytes(1,chuck);
callablestatement.setBlob(2,myblob);
}
int affectedRows = callablestatement.executeUpdate();