Re: update into loop

chijar wrote:
> 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

>I have a problem in a procedure that is in an application.

But, What is the problem? An ORA-XXXXX error? Unexpected results? What?

What Oracle version? What OS? What are you intending to do?

(Especifica cual es el problema. Si no, no te podremos ayudar)

Saludos.

Carlos. Received on Fri Jun 23 2006 - 01:28:54 CDT