Function PL/SQL ORACLE

Question

I need to do a function with the following sql

This sql converts from numbers to letters

With
  Numero as (
  select 3 N from dual
  )
  ,
  PreProcesado1 as (
  select   N
        , floor(mod(N, 10)) Unidades
    from Numero
  )
  select   N,     case Unidades 
                  when 0 then ''
                  when 1 then 'one'
                  when 2 then 'two'
                  when 3 then 'three'
                  when 4 then 'four'
                  when 5 then 'five'
                  when 6 then 'six'
                  when 7 then 'seven'
                  when 8 then 'eight'
                  when 9 then 'nine'
                end
              end
            from PreProcesado1;

I’m doing it like this but it doesn’t work, must have an input parameter which in this case is entry_numero and which should be converted

create or replace function fun_departamento(entry_numero number)
return varchar2 is
response varchar2(120);
begin
  With
      Numero as (
      select entry_numero N from dual
      )
      ,
      PreProcesado1 as (
      select   N
            , floor(mod(N, 10)) Unidades
        from Numero
      )
      select   N,     case Unidades 
                      when 0 then ''
                      when 1 then 'one'
                      when 2 then 'two'
                      when 3 then 'three'
                      when 4 then 'four'
                      when 5 then 'five'
                      when 6 then 'six'
                      when 7 then 'seven'
                      when 8 then 'eight'
                      when 9 then 'nine'
                    end
                  end
                  into response
                  from PreProcesado1; 
return response;
end;
/

I get this error, I'd appreciate your help

PL/SQL: SQL Statement ignored
PL/SQL: ORA-00947: not enough values

Answer 1

As you said that you want to do it your way, then see lines #16 and #29 as

• line 16: you're selecting two values (N and CASE expression)
• line 29: you're trying to put 2 values into a single variable (response)

Therefore, remove N from line #16 (or add another variable to insert into).

SQL> create or replace function fun_departamento(entry_numero number)
  2  return varchar2 is
  3  response varchar2(120);
  4  begin
  5    With
  6        Numero as (
  7        select entry_numero N from dual
  8        )
  9        ,
 10        PreProcesado1 as (
 11        select   N
 12              , floor(mod(N, 10)) Unidades
 13          from Numero
 14        )
 15        select
 16                 --  N,                  --> without it! ...
 17                   case Unidades
 18                        when 0 then ''
 19                        when 1 then 'one'
 20                        when 2 then 'two'
 21                        when 3 then 'three'
 22                        when 4 then 'four'
 23                        when 5 then 'five'
 24                        when 6 then 'six'
 25                        when 7 then 'seven'
 26                        when 8 then 'eight'
 27                        when 9 then 'nine'
 28                    end
 29                    into response      --> ... as you're inserting into a single variable
 30                    from PreProcesado1;
 31  return response;
 32  end;
 33  /

Function created.

Testing:

SQL> select fun_departamento(5) from dual;

FUN_DEPARTAMENTO(5)
--------------------------------------------------------------------------------
five

SQL>

Answer 2

This is a simpler way to do what you need:

to_char( to_date(n,'J'),'jsp')

For example:

SQL> select to_char( to_date(4,'J'),'jsp') as n from dual;

N
----------
four

Notice that this does not handle 0, so you may need:

select
  case
    when n != 0 then to_char( to_date(n,'J'),'jsp')
  end
from ...

Here you find something more.