Hi Tom,

I have a function which converts input string to a number and returns it as varchar.

string Exp : MALT011000012345MTLCAST001SMT84

<code>  FUNCTION translate_alpha (
    p_str                                VARCHAR2
  )
    RETURN VARCHAR2
  AS
    l_str                         VARCHAR2 (100) := UPPER (p_str);
    l_out_str                     VARCHAR2 (100) := '';
    l_str_tok                     CHAR (1);
    l_index                       BINARY_INTEGER;
  BEGIN
    register_proc ('validateIBAN.translate_alpha', 'p_str = ' || p_str);
    g_comments                 := 'tokenizer';

    FOR l_index IN 1 .. LENGTH (l_str)
    LOOP
      l_str_tok                  := SUBSTR (l_str,
                                            l_index,
                                            1
                                           );

      IF l_str_tok IN ('0', '1', '2', '3', '4', '5', '6', '7', '8', '9')
      THEN
        l_out_str                  := l_out_str || l_str_tok;
      ELSE
 <code>       l_out_str                  := l_out_str || ASCII (l_str_tok) - 55;

END IF;
END LOOP;

RETURN l_out_str;
END;
</code></code>

In this
l_out_str := l_out_str || ASCII (l_str_tok) - 55;

If the length of l_out_stris >39 , the concat operator appending value as '00' if l_str_tok is a alphabet irrespective of the value of ASCII (l_str_tok) - 55;
Could you explain me why it is appending zeros.
The maximum length of numeric that oracle allows is 39.
The issue is fixed using below by changing the logic as below.
ELSE
l_ascii := ASCII (l_str_tok) - 55;
l_out_str := l_out_str || l_ascii;
END IF;

Thank you in advance.

select dump('1' || 1-0), dump(11), dump('11') from dual;

DUMP('1'||1-0)      DUMP(11)            DUMP('11')        
------------------- ------------------- -------------------
Typ=2 Len=2: 193,12 Typ=2 Len=2: 193,12 Typ=96 Len=2: 49,49

select dump('1' || to_char(1-0)), dump(11), dump('11') from dual;

DUMP('1'||TO_CHAR( DUMP(11)            DUMP('11')        
------------------ ------------------- -------------------
Typ=1 Len=2: 49,49 Typ=2 Len=2: 193,12 Typ=96 Len=2: 49,49