Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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

share|improve this question
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. –  Colin 't Hart Apr 11 '13 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. –  David Aldridge Apr 12 '13 at 18:04

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Browse other questions tagged or ask your own question.