spremuta_at_gmail.com wrote:
> DA Morgan ha scritto:
>
>> Post the PL/SQL that is causing the 1555.
>> --
>> Daniel Morgan
>
>
> this should be the code...
>
> DECLARE
>
> /*-------------------------------------------------------------------------------*/
> /*-- dichiarazione variabili
> */
>
> /*-------------------------------------------------------------------------------*/
> WORK_R_POLO
> NETFLOW_IUM_EXT_H_DESTINATION.POLO%TYPE;
> WORK_R_DESTINATION_ADDRESS
> NETFLOW_IUM_EXT_H_DESTINATION.DESTINATION_ADDRESS%TYPE;
> WORK_R_PROTOCOL
> NETFLOW_IUM_EXT_H_DESTINATION.PROTOCOL%TYPE;
> WORK_R_START_TIME
> NETFLOW_IUM_EXT_H_DESTINATION.START_TIME%TYPE;
> WORK_R_BYTES
> NETFLOW_IUM_EXT_H_DESTINATION.BYTES%TYPE;
> WORK_R_PACKETS
> NETFLOW_IUM_EXT_H_DESTINATION.PACKETS%TYPE;
>
> CONTATORE_COMMIT INTEGER(06) := 0;
> FREQUENZA_COMMIT INTEGER(06) := 250000;
>
> /*-------------------------------------------------------------------------------*/
> /*-- dichiarazione cursori
> */
>
> /*-------------------------------------------------------------------------------*/
> CURSOR RNETFLOW_IUM_CUR1_DESTINATION IS
> SELECT
> POLO,
> DESTINATION_ADDRESS,
> PROTOCOL,
> TRUNC(START_TIME,'dd'),
> SUM(BYTES),
> SUM(PACKETS)
> FROM NETFLOW_IUM_EXT_H_DESTINATION
> WHERE TRUNC(START_TIME,'dd') = TRUNC(SYSDATE-1,'dd')
> GROUP BY
> POLO,DESTINATION_ADDRESS,PROTOCOL,TRUNC(START_TIME,'dd');
>
> BEGIN
>
> /*-------------------------------------------------------------------------------*/
> /*-- loop di lettura tabella tramite cursore
> */
>
> /*-------------------------------------------------------------------------------*/
> OPEN RNETFLOW_IUM_CUR1_DESTINATION;
> LOOP
> FETCH RNETFLOW_IUM_CUR1_DESTINATION
> INTO
> WORK_R_POLO,
> WORK_R_DESTINATION_ADDRESS,
> WORK_R_PROTOCOL,
> WORK_R_START_TIME,
> WORK_R_BYTES,
> WORK_R_PACKETS;
> EXIT WHEN RNETFLOW_IUM_CUR1_DESTINATION%NOTFOUND;
>
> /*-------------------------------------------------------------------------------*/
> /*-- insert NETFLOW_IUM_EXT_D_DESTINATION
> */
>
> /*-------------------------------------------------------------------------------*/
> INSERT INTO
> NETFLOW_IUM_EXT_D_DESTINATION
> (POLO,
> DESTINATION_ADDRESS,
> PROTOCOL,
> START_TIME,
> BYTES,
> PACKETS
> )
> VALUES
> (WORK_R_POLO,
> WORK_R_DESTINATION_ADDRESS,
> WORK_R_PROTOCOL,
> WORK_R_START_TIME,
> WORK_R_BYTES,
> WORK_R_PACKETS
> );
> IF CONTATORE_COMMIT = FREQUENZA_COMMIT
> THEN CONTATORE_COMMIT := 1;
> COMMIT;
> END IF;
> END LOOP;
> CLOSE RNETFLOW_IUM_CUR1_DESTINATION;
> COMMIT;
>
>
> INSERT_NETFLOW_IUM_LOG('INSERT_NETFLOW_IUM_DESTINATION_D.SQL','OK','ELABORAZIONE
> OK');
>
> EXCEPTION
> WHEN OTHERS THEN
>
> INSERT_NETFLOW_IUM_LOG('INSERT_NETFLOW_IUM_DESTINATION_d.SQL','KO',SQLCODE||'-'||SUBSTR(SQLERRM,
> 1, 100));
> END;
What is the point of the cursor loop? Replace it with a simple
INSERT INTO ...
SELECT ... FROM ...;
But what is causing your problem is the commit inside the loop as has
already been pointed out.
IF CONTATORE_COMMIT = FREQUENZA_COMMIT
> THEN CONTATORE_COMMIT := 1;
> COMMIT;
> END IF;
Serves no purpose except to generate errors.
--
Daniel Morgan
University of Washington
Puget Sound Oracle Users Group
Received on Tue Sep 12 2006 - 08:49:05 CDT
