0

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

2
  • 1
    Why are you using PL/SQL at all? You could write this as a series of SQL statements and achieve much better performance -- not to mention the much simpler code. Commented Apr 11, 2013 at 9:53
  • +1 to Colin -- this PL/SQL approach is harder to code, harder to maintain, more error prone, slower. In fact there is no benefit to it at all. Commented Apr 12, 2013 at 18:04

1 Answer 1

0
CREATE OR REPLACE PROCEDURE IB_ARCHIVE_FDS_TEST(
    TABLE_NAME VARCHAR2,
    COLUMN_NAME VARCHAR2,
    criteria VARCHAR2,
    ACTIVE_PERIOD NUMBER
) IS
    -- Define a record type for IB_FDS_LOG2
    TYPE ib_fds_log_rec IS RECORD (
        message_id IB_FDS_LOG2.message_id%TYPE,
        fds_activity_type IB_FDS_LOG2.fds_activity_type%TYPE,
        ref_no IB_FDS_LOG2.ref_no%TYPE,
        direction IB_FDS_LOG2.direction%TYPE,
        type IB_FDS_LOG2.type%TYPE,
        ref_ref_no IB_FDS_LOG2.ref_ref_no%TYPE,
        time_request IB_FDS_LOG2.time_request%TYPE,
        time_response IB_FDS_LOG2.time_response%TYPE,
        request_soapid IB_FDS_LOG2.request_soapid%TYPE,
        response_soapid IB_FDS_LOG2.response_soapid%TYPE,
        status IB_FDS_LOG2.status%TYPE,
        is_feedback IB_FDS_LOG2.is_feedback%TYPE,
        session_id IB_FDS_LOG2.session_id%TYPE,
        status_fds_message IB_FDS_LOG2.status_fds_message%TYPE,
        request IB_FDS_LOG2.request%TYPE,
        response IB_FDS_LOG2.response%TYPE
    );

    -- Define a collection of the above record type
    TYPE ib_fds_log_tab IS TABLE OF ib_fds_log_rec;

    -- Declare a cursor
    CURSOR c_ib_fds_log IS
        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, request, response
        FROM IB_FDS_LOG2
        WHERE direction = 0 AND status_fds_message = 0 AND time_request < trunc(sysdate - ACTIVE_PERIOD + 1);

    -- Declare a collection variable
    v_ib_fds_log ib_fds_log_tab;
BEGIN
    -- Fetch data from the cursor into the collection
    OPEN c_ib_fds_log;
    FETCH c_ib_fds_log BULK COLLECT INTO v_ib_fds_log;
    CLOSE c_ib_fds_log;

    -- Now you can iterate over the collection and perform your operations
    FOR i IN 1..v_ib_fds_log.COUNT LOOP
        -- Access fields using v_ib_fds_log(i).field_name
        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, request, response
        ) VALUES (
            v_ib_fds_log(i).message_id, v_ib_fds_log(i).fds_activity_type, v_ib_fds_log(i).ref_no,
            v_ib_fds_log(i).direction, v_ib_fds_log(i).type, v_ib_fds_log(i).ref_ref_no,
            v_ib_fds_log(i).time_request, v_ib_fds_log(i).time_response, v_ib_fds_log(i).request_soapid,
            v_ib_fds_log(i).response_soapid, v_ib_fds_log(i).status, v_ib_fds_log(i).is_feedback,
            v_ib_fds_log(i).session_id, v_ib_fds_log(i).status_fds_message, v_ib_fds_log(i).request,
            v_ib_fds_log(i).response
        );
        COMMIT;

        -- You can perform other operations here
    END LOOP;
END;
/

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.