sqloracle-databaseplsqlto-char

ORA-01481: invalid number format model (convert number to char)

I'm confused as to why I'm getting this error. I use a function that stores a NUMBER variable and returns a NUMBER (e.g. 123456).

I want to convert this to a format of my choosing, so I would use to_char:

 select to_char(total_count_prods('apple'),'1234-56') from dual;

And I keep getting that error. What's the issue? If I run

select total_count_prods('apple') from dual;

Then I get the following output:

 123456

How can I convert that to:

 1234-56
Solution

  • You can use SUBSTR:

    SELECT SUBSTR(total_apples, 1, 4) || '-' || SUBSTR(total_apples, 5)
FROM   (
  SELECT total_count_prods('apple') AS total_apples
  FROM   DUAL
);

    Or REGEXP_REPLACE:

    SELECT REGEXP_REPLACE(
         total_count_prods('apple'),
         '(\d{2})$',
         '-\1'
       )
FROM   DUAL;

    Or, divide by 100 and use TO_CHAR and set the decimal separator to -:

    SELECT TO_CHAR(
         total_count_prods('apple')/100,
         'FM9999D99',
         'NLS_NUMERIC_CHARACTERS=-,'
       )
FROM   dual;

    fiddle