Re: Can someone help me add logging to my stored procedure?

From: Craig Burtenshaw <crb_at_amsa.gov.au>
Date: Fri, 28 Mar 2003 10:01:52 +1100
Message-ID: <GpLga.7$Uq2.5040@nsw.nnrp.telstra.net>

"Dafella" <dafella007_at_yahoo.com> wrote in message news:203d44c5.0303261345.2f9cfb47_at_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?

Using utl_file and also for your errors increase the SERVEROUTPUT size to 10000000.

You will need to first edit your init.ora file and add the following parameter.
utl_file_dir=<location for logfile>

Stop and Start the database.

I have put the code modifications in below.

Here is your code:********************************

SET SERVEROUTPUT ON SIZE 10000000 SPOOL ON
DECLARE LogFile UTL_FILE.FILE_TYPE;

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
    LogFile := UTL_FILE.FOPEN
('path_from_utl_file_dir','logfilename','W',32768);

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 */

       utl_file.put_line (LogFile, 'Key: '||key||' Phone: '||phone||' UserField: '||USERFIELD);

       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;
 UTL_FILE.FCLOSE (LogFile);
 EXCEPTION
 when others then
 DBMS_OUTPUT.PUT_LINE('Error code ' || sqlcode || ' Error desc' ||sqlerrm); END;
/
SPOOL OFF

End of your code:********************************

Good Luck. Received on Thu Mar 27 2003 - 17:01:52 CST