Re: ORA 01401 error inserted value too large for column

In article <614118737wnr_at_mssint.demon.co.uk> Tim Jinkerson <TIMJ_at_mssint.demon.co.uk> writes:
>From: Tim Jinkerson <TIMJ_at_mssint.demon.co.uk>
>Subject: Re: ORA 01401 error inserted value too large for column
>Date: Wed, 05 Jul 1995 13:23:39 GMT
 

>In article: <333981103wnr_at_rossuk.demon.co.uk> Rod Green
><Rodgr_at_rossuk.demon.co.uk> writes:
>>
>> We are getting this error. I have debugged the program code but cannot
>> find which column is giving the error.
>>
>> I tried producing a trace file to see if that helped, but it does not
>> show where the error occurred, or not that I can see.
>>
>> Any ideas
>> -- -------
>> --------------------------------------------------------------------| Rod
>> Green EMail rodgr_at_rossuk.demon.co.uk || Mail
>> sent via Demon Internet - Full IP for 10/Month Tel:0181 371 1234
>> |---------------------------------------------------------------------------
>>
>>
>You don't actually state what you are programming in, but I assume you
>are using PL/SQL 2.0. Pre-compiled PL/SQL is hard to debug because of its
>lack of interfaces to the otherside world. The approach I take is to
>write a small packaged function that accepts a char param, and places
>the param in to an oracle pipe. You then have to write a 3 gl program
>to sit on that pipe waiting for a message. Insert in to your code
>suitable calls to that function, passing it the values of your
>variables to see which one too large, ie
 

> package.function('acc no is ' || to_char(acc_no));
 

>Info about oracle pipe can be found in the Oracle 7 Server Applications
>Developer's Guide.
 

>If any-one knows a better way of debugging PL/SQL blocks, or has
>experience of maintaining PL/SQL blocks in the region of 1500 to 2000
>lines of code (or larger), please share any hints we use.
 

>Tim Jinkerson
>Technical Consultant
>MSS International
>Egham

The two problems I've had with PLSQL are:

1. The line number in error NEVER matches my original source.
2. The 'column-too-small' error which was part of this original post.

Solutions:

1. Wrote a script which removes all source lines preceeding a PLSQL routine and any 'blank' lines folllowing the start of the PLSQL routine. The start is defined as DECLARE in a standalone routine or PROCEDURE for a procedure.
2. Within every PLSQL pgm I use a ERROR_LOG table to trap errors: Create table ERROR_LOG (logid varchar2(32), logtext varcahr2(2000));

     and I call a common procedure:

     Procedure LOG_IT (char log_key, char log_stuff) Is
     Begin
          etc.
     End 

     The LOG_IT procedure is called from an EXCEPTION-HANDLER and posts the 

Sorry can't be more detailed, but this 'free' newsreader does not support cut/paste.

\louis Received on Thu Jul 06 1995 - 00:00:00 CEST