How use to_char in nvl()

Question

V_MAX:=NVL(CREAR_DEPART(V_NOMB),TO_CHAR('the value is null'));

DBMS_OUTPUT.PUT_LINE(V_MAX);

The function gives me a type number

How can I use nvl to get a value in varchar2?

Answer 1

That string already is a ... well, string, you don't to_char it.

SQL> with test (v_nomb) as
  2    (select to_number(null) from dual)
  3  select nvl(to_char(v_nomb), 'the value is null') result
  4  from test;

RESULT
-----------------
the value is null

SQL>

Or, in your case,

V_MAX := NVL(to_char(CREAR_DEPART(V_NOMB)), 'the value is null');

---

As you commented, function returns a number. Or - in order to capture it with a NVL - let it return null.

SQL> create or replace function crear_depart(par_nomb in number)
  2  return number is
  3  begin
  4    return null;
  5  end;
  6  /

Function created.

SQL> set serveroutput on;
SQL> declare
  2    v_max  varchar2(20);
  3    v_nomb number := 2;
  4  begin
  5    v_max := nvl(to_char(crear_depart(v_nomb)), 'the value is null');
  6    dbms_output.put_line('v_max = ' || v_max);
  7  end;
  8  /
v_max = the value is null

PL/SQL procedure successfully completed.

SQL>