Call a procedure from Spring

How to call a procedure using spring :

In this blog post I would take you through the approach of calling a procedure from spring using "StoredProcedure" approach.

StoredProcedure is an abstract class which has to be extended.

Define a class which extends StoredProcedure, below is a sample which is shown,

Then call a procedure which is shown as below,

Sample procedure in Oracle

Sample oracle procedure which involves loops and also statements to execute. Exception handling not done perfectly but could use for a start ups.
---------------------------------------------------------------------------------------------------
create or replace 
PROCEDURE MANAGE_MONITOR_RECORDS
IS
EVENT_CAT varchar2(100);
PROCESS_NAME VARCHAR2(100);
COUNT_TOTAL NUMBER;
SQL_STMT VARCHAR2(200);
BEGIN
for x in (select distinct PROCESS_INSTANCE_ID FROM HMT_PROCESS_AUDIT_INFO)
LOOP
DBMS_OUTPUT.PUT_LINE(X.PROCESS_INSTANCE_ID);
SELECT COUNT(*) INTO COUNT_TOTAL FROM GLF_EVENT_LOG WHERE LOG_INSTANCE_ID = x.PROCESS_INSTANCE_ID;
IF (COUNT_TOTAL > 0 ) THEN
  SELECT EVENT_CAT,PROCESS_CODE INTO EVENT_CAT,PROCESS_NAME FROM GLF_EVENT_LOG
          WHERE LOG_INSTANCE_ID=x.PROCESS_INSTANCE_ID AND ROWNUM=1;
  DBMS_OUTPUT.PUT_LINE(EVENT_CAT);
  DBMS_OUTPUT.PUT_LINE(PROCESS_NAME);
  SQL_STMT := 'UPDATE HMT_PROCESS_AUDIT_INFO SET KEYWORD2=:1,KEYWORD3=:2 WHERE PROCESS_INSTANCE_ID=:3';
  EXECUTE IMMEDIATE SQL_STMT USING ''||EVENT_CAT||'',''||PROCESS_NAME||'',''||x.PROCESS_INSTANCE_ID||''; 
  DBMS_OUTPUT.PUT_LINE('Updated the entry in HMT_PROCESS_AUDIT_INFO table');
  EVENT_CAT := NULL;
ELSE
  DBMS_OUTPUT.PUT_LINE('ELSE BLOCK');
END IF;
END LOOP;
COMMIT;
EXCEPTION
WHEN OTHERS THEN
 return;
END;