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

Question

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

Answer 1

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

Answer 2

Your function total_count_prods() is returning a number, exactly as you would expect. The problem is that '1234-56' is not a valid format model for converting a number to a string - exactly as the error says.