Hi,
I need to pass List<Long> from hibernate to my stored procedure. Below is the code for the same -
Here is my query:
Code:
Query q = getSession().getNamedQuery("cloneSpecialClauses")
.setLong("grpId",groupId)
.setLong("maId",maId)
.setLong("amdId",amdId)
.setString("user",user)
.setParameterList("deleted_spc_ids",deleted_spc_ids)
.setParameterList("updated_spc_ids",updated_spc_ids);
q.uniqueResult();
Code:
<sql-query name="cloneClauses" callable="true">
{ call clone_clause( :grpId, :maId, :amdId, :user, :deleted_spc_ids, :updated_spc_ids) }
</sql-query>
My oracle stored procedure looks like this:
Code:
CREATE OR REPLACE TYPE t_in_list AS TABLE OF NUMBER;
CREATE OR REPLACE PROCEDURE clone_clause (
grpid NUMBER,
maid NUMBER,
amdid NUMBER,
cuser VARCHAR2,
deleted_spc_ids t_in_list,
updated_spc_ids t_in_list
)
AS
BEGIN
FOR elem in 1 .. deleted_spc_ids.count LOOP
DELETE FROM abc th
WHERE th.ma_id IN (SELECT ma_id
FROM def
WHERE ma_id <> maid AND grp_id = grpid)
AND th.amd_id = amdid
AND th.spc_cla_id = deleted_spc_ids(elem);
END LOOP;
FOR elem1 in 1 .. updated_spc_ids.count LOOP
UPDATE abc c1
SET is_applicable =
(select is_applicable from abc where ma_id=maid
and spc_cla_id=updated_spc_ids(elem1)),
date_modif = SYSDATE,
user_modif = cuser
WHERE ma_id IN (SELECT ma_id
FROM def
WHERE ma_id <> maid AND grp_id = grpid)
AND amd_id = amdid
AND spc_cla_id = updated_spc_ids(elem1);
END LOOP;
END;
Exception:
Code:
PLS-00306: wrong number or types of arguments in call clone_clause
Is there something wrong with my stored procedure or the way i am passing the List to my stored procedure?
Login
FAQ
Search
