I have created below stored procedure in oracle.
CREATE OR REPLACE PROCEDURE "UPDATE_ASSET_LOB_PROC"(
asset_id IN integer,
distribution_id_list IN distribution_id)
IS
CURSOR dist_id
IS
select adt.lkp_dist_type from Asset_Dist_Type adt where adt.asset_id= asset_id;
BEGIN
delete from Asset_Dist_Type where asset_id= asset_id;
commit;
for i IN dist_id
LOOP
insert into Asset_Dist_Type values (asset_id_list,i.lkp_dist_type);
commit;
END LOOP;
END UPDATE_ASSET_LOB_PROC;
distribution_is a custom type. I have created it as
`CREATE TYPE distribution_id AS TABLE OF NUMBER;`
My java code is as follows.
Integer[] idArray = new Integer[selectedDistributionTypes.size()];
idArray = selectedDistributionTypes.toArray(idArray);
entityManager.createNativeQuery("CALL UPDATE_ASSET_LOB_PROC (:assetIdParam,:distributionIdParam)")
.setParameter("assetIdParam", assetId).setParameter("distributionIdParam", idArray).executeUpdate();
I am getting
`Caused by: java.sql.SQLException: ORA-06553: PLS-306: wrong number or types of arguments in call to 'UPDATE_ASSET_LOB_PROC'`.
What is the wrong thing here? I guess it is with stored procedure. But I am not much into writing stored procedure. OR is it feasible to pass a comma separated list and split it in stored procedure?
Got solution to this problem. I removed the created custom type "distribution id" and made distribution_id_list as varchar2. From bean, I sent a comma separated list and in stored procedure, removed the comma's looping over it as below.
SELECT REGEXP_SUBSTR (distribution_id_list, '[^,]+', 1,LEVEL) dist_id
FROM DUAL
CONNECT BY REGEXP_SUBSTR (distribution_id_list, '[^,]+', 1, LEVEL) IS NOT NULL
Inside a for loop I can use dist_id to whatever the operation I want.
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments