Can someone help me add logging to my stored procedure?

From: Dafella <dafella007_at_yahoo.com>
Date: 26 Mar 2003 13:45:14 -0800
Message-ID: <203d44c5.0303261345.2f9cfb47@posting.google.com>

I have the following code that depends on spooling to create some sort of log. But if I try and capture my records and keys I get a buffer overflow.
How do I write my key, phone and userfield out to a plain ole text file?

Thanks

Here is my code:********************************

SET SERVEROUTPUT ON SPOOL ON
DECLARE  CURSOR vt_mlr_cursor IS Select master_key, tn, user1 from vt_mlr Where user1 is not null;

 USERFIELD VARCHAR2(100);
 R_count NUMBER := 0;    

 Field1 VARCHAR2(40);  
 Field2 VARCHAR2(10);  
 Field3 VARCHAR2(10);  
 Field4 VARCHAR2(10);  
 Field5 VARCHAR2(10);  

 Field_2_n_3 VARCHAR2(25);
 key VARCHAR2(10);
 phone VARCHAR2(11);  

 BEGIN   FOR vt_mlr_record IN vt_mlr_cursor
  LOOP
   BEGIN

   	key       := vt_mlr_record.master_key;
	phone	  := vt_mlr_record.tn;
   	USERFIELD := vt_mlr_record.user1;
      	Field1 := SUBSTR(vt_mlr_record.user1,1,11);                /*
this will return FTMYFLXA04W */
   	Field2 := SUBSTR(vt_mlr_record.user1,15,3);          /* this will
return VPI0043 */
   	Field3 := SUBSTR(vt_mlr_record.user1,23,3);          /* this will
return VCI0184 */
   	Field4 := SUBSTR(vt_mlr_record.user1,31,5);   /* this will return
00001 */
   	       		
     	UPDATE vt_mlr
     	SET
     		gen_dslam_clli = Field1,
     		gen_dslam_shelf = Field2,
     		gen_dslam_card = Field3,
		gen_dslam_port = Field4
  	WHERE
     		master_key = vt_mlr_record.master_key;
	 	R_count := R_count + 1;
		
		
		IF R_count >= 1000 THEN
		commit;
		R_count := 0;
		END IF;
	EXCEPTION
	 when others then
	 DBMS_OUTPUT.PUT_LINE('Error ' || key ||' '|| phone || ' ' ||
USERFIELD );
	R_count := R_count - 1;

   END;
  END LOOP;
  IF R_count > 0 THEN

        commit;
  END IF;

	 EXCEPTION
	 when others then
	 DBMS_OUTPUT.PUT_LINE('Error code ' || sqlcode || ' Error desc'

||sqlerrm);
 END;
/
SPOOL OFF

End is my code:********************************

Received on Wed Mar 26 2003 - 15:45:14 CST