i've problem here. i wanna create a Stored procedure like below, but i encountered error PLS-00436: implementation restriction: cannot reference fields of BULK In-BIND table of records.
create or replace
PROCEDURE IB_ARCHIVE_FDS_TEST(TABLE_NAME varchar2, COLUMN_NAME varchar2, criteria varchar2, ACTIVE_PERIOD number)
IS
TYPE subset_fds IS RECORD(
message_id ib_fds_log.message_id%TYPE,
fds_activity_type ib_fds_log.fds_activity_type%TYPE,
ref_no ib_fds_log.ref_no%TYPE,
direction ib_fds_log.direction%TYPE,
type ib_fds_log.type%TYPE,
ref_ref_no ib_fds_log.ref_ref_no%TYPE,
time_request ib_fds_log.time_request%TYPE,
time_response ib_fds_log.time_response%TYPE,
request_soapid ib_fds_log.request_soapid%TYPE,
response_soapid ib_fds_log.response_soapid%TYPE,
status ib_fds_log.status%TYPE,
is_feedback ib_fds_log.is_feedback%TYPE,
session_id ib_fds_log.session_id%TYPE,
status_fds_message ib_fds_log.status_fds_message%TYPE);
TYPE fds_tabs IS TABLE OF subset_fds INDEX BY PLS_INTEGER;
TYPE fds_message_id IS TABLE OF IB_FDS_LOG.message_id%TYPE INDEX BY PLS_INTEGER;
TYPE fds_request IS TABLE OF IB_FDS_LOG.request%TYPE INDEX BY PLS_INTEGER;
TYPE fds_response IS TABLE OF IB_FDS_LOG.response%TYPE INDEX BY PLS_INTEGER;
v_fds_log fds_tabs;
v_message_id fds_message_id;
v_request fds_request;
v_response fds_response;
BEGIN
SELECT message_id, fds_activity_type, ref_no, direction, type, ref_ref_no, time_request, time_response, request_soapid, response_soapid, status, is_feedback, session_id, status_fds_message BULK COLLECT INTO v_fds_log FROM IB_FDS_LOG2 where direction = 0 and status_fds_message = 0 and time_request < trunc(sysdate - ' || ACTIVE_PERIOD || '+ 1);
SELECT request BULK COLLECT INTO v_request FROM IB_FDS_LOG2 where direction = 0 and status_fds_message = 0 and time_request < trunc(sysdate - ' || ACTIVE_PERIOD || '+ 1);
SELECT response BULK COLLECT INTO v_response FROM IB_FDS_LOG2 where direction = 0 and status_fds_message = 0 and time_request < trunc(sysdate - ' || ACTIVE_PERIOD || '+ 1);
FOR i IN v_fds_log.FIRST .. v_fds_log.LAST LOOP
v_message_id(i) := v_fds_log(i).message_id;
END LOOP;
FORALL indx IN 1 .. v_fds_log.COUNT
INSERT INTO IB_FDS_LOG3 (message_id,fds_activity_type,ref_no,direction,type,ref_ref_no,time_request,time_response,request_soapid,response_soapid,status,is_feedback,session_id,status_fds_message)VALUES (v_fds_log(indx).message_id,v_fds_log(indx).fds_activity_type,v_fds_log(indx).ref_no,v_fds_log(indx).direction,v_fds_log(indx).type,v_fds_log(indx).ref_ref_no,v_fds_log(indx).time_request,v_fds_log(indx).time_response,v_fds_log(indx).request_soapid,v_fds_log(indx).response_soapid,v_fds_log(indx).status,v_fds_log(indx).is_feedback,v_fds_log(indx).session_id,v_fds_log(indx).status_fds_message);
COMMIT;
FORALL indx IN 1 .. v_fds_log.COUNT
INSERT INTO IB_FDS_LOG3 (RESPONSE) VALUES (v_fds_log(indx).response);
COMMIT;
FORALL indx IN 1 .. v_fds_log.COUNT
INSERT INTO IB_FDS_LOG3 (REQUEST) VALUES (v_fds_log(indx).request);
COMMIT;
FORALL indx IN 1 .. v_fds_log.COUNT
DELETE FROM IB_FDS_LOG2 WHERE message_id = v_message_id(indx);
COMMIT;
END;
please help me to solve my problem, thanks