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

You could also create a procedure that created a record in a log table. Use PRAGMA AUTONOMOUS TRANSACTION in the declaration so that the commits are independent of your process' commits and rollbacks.

On Fri, 28 Mar 2003 10:01:52 +1100, "Craig Burtenshaw" <crb_at_amsa.gov.au> wrote:

>"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 - 21:37:53 CST