Trouble with TO_CHAR

[weberm]

I am attempting to increment a counter field which is stored as a CHAR(4) using a NUMBER(4) variable and the TO_CHAR function but keep getting a VALUE ERROR exception:

 v_detail_line := v_detail_line +1;
 CCRR_rec.fee_detail_line_number := TO_CHAR(v_detail_line,'0009');

However, using LPAD works just fine:

v_detail_line := v_detail_line +1;
CCRR_rec.fee_detail_line_number := LPAD(v_detail_line,4,'0');

Does anyone know what's going on here? I'm curious but am probably overlooking something.

 

[SantaMufasa]

The two functions produce similar results: the "to_char" function produces an "extra" left-side character position for a '-' in the case of a negative resulting value; the "lpad" function does not produce the extra sign position.

I cannot, however, explain (without more information), why you are receiving the "value error" exception. Can you please introduce several "dbms_output.put_line" commands amongst your existing code to confirm the "before" and "after" values of "v_detail_line"?

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[weberm]

If TO_CHAR creates an extra character then that cerainly would explain why I got a VALUE_ERROR exception! However, none of the values were negative.

In any event, I was running it in debug mode in TOAD and v_detail_line = 2 when it invokes TO_CHAR. Weird,huh?

Would it help if I mentioned CCRR_rec.fee_detail_line_number was a record declared as a ROWTYPE of a table named CCRR with field fee_detail_line_number as CHAR(4)? I tried declaring v_detail_line as NUMBER and NUMBER(4) without any success.

 

[LKBrwnDBA]

-- Or --

Use this mask:

CCRR_rec.fee_detail_line_number := TO_CHAR(v_detail_line,'FM0009');

----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

 

[SantaMufasa]

Amazing how full disclosure can solve a problem <grin>...the problem with "fee_detail_line_number as CHAR(4)" is that it is not large enough to accommodate your 4-character mask plus the sign byte:

declare
    x varchar2(4);
begin
    x := to_char(4,'0009');
end;
/

ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error: character string buffer too small
ORA-06512: at line 4

Let us know if this resolves your issue.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[weberm]

Ahh, that explains it! Thanks guys!

 

[LKBrwnDBA]

Exactly:

SQL> declare
  2      x varchar2(4);
  3  begin
  4      x := to_char(4,'FM0009');
  5* end;
SQL>/

PL/SQL procedure successfully completed.

----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb