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

To increase output buffer size:
set serveroutput on <size>

e.g.
set serveroutput on size 1000000

If you are writing a lot of information to the log, you might be better off logging to a table or to a file (using utl_file package).

Anurag

"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?
>
> 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 - 19:37:50 CST