update into loop

From: chijar <chijar_at_gmail.com>
Date: 22 Jun 2006 16:58:14 -0700
Message-ID: <1151020693.952017.173040@y41g2000cwy.googlegroups.com>

Dear friends

Im talking and write in spanish language, so, apologize me if my english is very poor.

I have a problem in a procedure that is in an application. Well, the procedure is:

PROCEDURE Sp_actualiza_exp_documentos (

      Intid                     Expdntes.Id%TYPE,
      Strobservacion   IN       Expdntes.Observacion_documento%TYPE,
      Str_datos        IN       VARCHAR2,
      Int_Res              OUT      NUMBER,
      Str_user             IN       VARCHAR2

   )
   IS

      Intid_tdocumento       Tipo_documento.Id_tdocumento%TYPE;
      Intid_documento        Documentos.Id_documento%TYPE;
      Intpresentacion        NUMBER;
      Intnum_doc             Expdntes_tdocumento.Num_documento%TYPE;
      Strnombre_tdocumento   Tipo_documento.Nombre%TYPE;
      Strnombre_documento    Documentos.Nombre%TYPE;
      Strcadena_aux          VARCHAR2 (10000);
      Strcadena              VARCHAR2 (10000);
      Poscomma               NUMBER;
   BEGIN
      Strcadena := Str_datos;
      Strcadena_aux := Str_datos;


      --int_cuenta:=0;

      LOOP
            Poscomma := INSTR (Strcadena, ',');

        -----------------------------------------
         IF Poscomma = 0
         THEN
            Strcadena_aux := Strcadena;
            Strcadena := 'X';
         ELSE
            Intid_tdocumento :=
                            TO_NUMBER (SUBSTR (Strcadena, 1,   Poscomma
                                                             - 1));
            Strcadena :=
                       SUBSTR (Strcadena,   Poscomma


                                          + 1, LENGTH (Strcadena));

         END IF;

         Poscomma := INSTR (Strcadena, ',');

        -----------------------------------------
	IF Poscomma = 0
         THEN
            Strcadena_aux := Strcadena;
            Strcadena := 'X';
         ELSE
            Intid_documento :=
                            TO_NUMBER (SUBSTR (Strcadena, 1,   Poscomma
                                                             - 1));
            Strcadena :=
                       SUBSTR (Strcadena,   Poscomma


                                          + 1, LENGTH (Strcadena));

         END IF;
	--------------------------------------------


         Poscomma := INSTR (Strcadena, ',');

         IF Poscomma = 0
         THEN
            Strcadena_aux := Strcadena;
            Strcadena := 'X';
         ELSE
            Intnum_doc := TO_NUMBER (SUBSTR (Strcadena, 1,   Poscomma
                                                           - 1));
            Strcadena :=
                       SUBSTR (Strcadena,   Poscomma


                                          + 1, LENGTH (Strcadena));

         END IF;
        -----------------------------------------
         Poscomma := INSTR (Strcadena, ',');

         IF Poscomma = 0
         THEN
            Strcadena_aux := Strcadena;

            begin
            Intpresentacion :=
                            TO_NUMBER (Strcadena);
        EXCEPTION
            when others then
            Intpresentacion :=0;

            end ;
            Strcadena := 'X';
         ELSE
            Intpresentacion :=
                            TO_NUMBER (SUBSTR (Strcadena, 1,   Poscomma
                                                             - 1));
            Strcadena :=
                       SUBSTR (Strcadena,   Poscomma


                                          + 1, LENGTH (Strcadena));

         END IF;
         Strcadena_aux := nvl(Strcadena,'X');


         UPDATE Expdntes_tdocumento B
            SET B.Presentacion = TO_CHAR (Intpresentacion),
                B.Num_documento = Intnum_doc,
                B.Usrio_mdfcdor = Str_user,
                B.Fcha_mdfccion = SYSDATE
          WHERE (B.Id = Intid) AND B.Id_tdocumento = Intid_tdocumento
                AND B.Id_documento = Intid_documento;

         EXIT WHEN (Strcadena = ',' OR Strcadena_aux = 'X') OR
Strcadena = 'X';
      END LOOP;

      UPDATE Expdntes
         SET Observacion_documento = Strobservacion
       WHERE Id = Intid;

      Int_Res := 1;
   EXCEPTION
      WHEN OTHERS
      THEN
         Nstd_log_pkg.Sp_track_log (
            SQLCODE,
            'Sp_actualiza_exp_documentos',
            SQLERRM,
            'ORACLE'
         );
         Int_Res := 0;

  END;

**********************************************COMMENTS:
*****************************

- i think that the problem is in this update: UPDATE Expdntes_tdocumento B

            SET B.Presentacion = TO_CHAR (Intpresentacion),
                B.Num_documento = Intnum_doc,
                B.Usrio_mdfcdor = Str_user,
                B.Fcha_mdfccion = SYSDATE
          WHERE (B.Id = Intid) AND B.Id_tdocumento = Intid_tdocumento
                AND B.Id_documento = Intid_documento;

but how can i test?

• the table Expdntes_tdocumento have aprox 4 million of rows.

Any idea?
pls is important to me if you help me.

thanks a lot Received on Thu Jun 22 2006 - 18:58:14 CDT