Использование TO_CHAR из TIMESTAMP вызывает ORA-01877, если его длина превышает 78 символов
Я сталкиваюсь со странной проблемой
Я пытаюсь преобразовать некоторые числа в "слова", просто делая это:
TO_CHAR(TO_TIMESTAMP(LPAD( nvl(trunc(99999999),0) , 9, '0'), 'FF9'),'FFSP') AS amt_in_words
что работает отлично, на выходе получается "ДЕВЯТЬ-ДЕВЯТЬ МИЛЛИОН Девятьсот девятьсот девять тысяч девятьсот девяносто девять"
но что произойдет, когда мы попробуем число, например 99999998?
TO_CHAR(TO_TIMESTAMP(LPAD( nvl(trunc(99999998),0) , 9, '0'), 'FF9'),'FFSP') AS amt_in_words
выдает ошибку "ORA-01877: строка слишком длинная для внутреннего буфера"
ну, это меньшее число, оно должно работать, но это не моя теория: он потерпел неудачу, потому что длина полученного текста была бы 79 символов, и по какой-то причине это не удалось
предложение 1: использовать приведение (EXPR как VARCHAR(100)), но оно дало ту же ошибку
есть идеи?
Пояснение: к сожалению, я работаю над Oracle Fusion BI buplisher, где, по-видимому, я не могу использовать любые процедуры Pl/sql, только обычные запросы select
2 ответа
PL/SQL, похоже, не сталкивается с одной и той же проблемой, поэтому вы можете написать свою собственную функцию для выполнения преобразования:
create or replace function spell_number(p_number number) return varchar2 is
l_str varchar2(200);
begin
l_str := to_char(to_timestamp(lpad(nvl(trunc(p_number), 0) , 9, '0'), 'FF9'), 'FFSP');
return l_str;
end;
/
Тогда еще более длинные значения работают:
select spell_number(99999999) from dual;
AMT_IN_WORDS
----------------------------------------------------------------------------------------------------
NINETY-NINE MILLION NINE HUNDRED NINETY-NINE THOUSAND NINE HUNDRED NINETY-NINE
select spell_number(99999998) from dual;
AMT_IN_WORDS
----------------------------------------------------------------------------------------------------
NINETY-NINE MILLION NINE HUNDRED NINETY-NINE THOUSAND NINE HUNDRED NINETY-EIGHT
select spell_number(999999999) from dual;
AMT_IN_WORDS
----------------------------------------------------------------------------------------------------
NINE HUNDRED NINETY-NINE MILLION NINE HUNDRED NINETY-NINE THOUSAND NINE HUNDRED NINETY-NINE
Все, что длиннее 9 цифр, игнорирует наименее значимые цифры, но, похоже, это ограничение формата SP:
with t (num) as (
select 99999998 from dual
union all select 99999999 from dual
union all select 999999999 from dual
union all select 1000000000 from dual
union all select 1000000001 from dual
union all select 1000000010 from dual
union all select 9999999999 from dual
union all select 10000000000 from dual
union all select 99999999999 from dual
union all select 100000000000 from dual
)
select num, spell_number(num) as amt_in_words from t;
NUM AMT_IN_WORDS
------------- ----------------------------------------------------------------------------------------------------
99999998 NINETY-NINE MILLION NINE HUNDRED NINETY-NINE THOUSAND NINE HUNDRED NINETY-EIGHT
99999999 NINETY-NINE MILLION NINE HUNDRED NINETY-NINE THOUSAND NINE HUNDRED NINETY-NINE
999999999 NINE HUNDRED NINETY-NINE MILLION NINE HUNDRED NINETY-NINE THOUSAND NINE HUNDRED NINETY-NINE
1000000000 ONE HUNDRED MILLION
1000000001 ONE HUNDRED MILLION
1000000010 ONE HUNDRED MILLION ONE
9999999999 NINE HUNDRED NINETY-NINE MILLION NINE HUNDRED NINETY-NINE THOUSAND NINE HUNDRED NINETY-NINE
10000000000 ONE HUNDRED MILLION
99999999999 NINE HUNDRED NINETY-NINE MILLION NINE HUNDRED NINETY-NINE THOUSAND NINE HUNDRED NINETY-NINE
100000000000 ONE HUNDRED MILLION
Разделите число на группы по тысячи и просто обработайте каждую из них:
Установка Oracle:
CREATE OR REPLACE FUNCTION TO_WORDS(
in_value IN INT
) RETURN VARCHAR2 DETERMINISTIC
AS
p_value INT := ABS( in_value );
p_words VARCHAR2(4000);
t_array CONSTANT SYS.ODCIVARCHAR2LIST := SYS.ODCIVARCHAR2LIST(
null,
' thousand ',
' million ',
' billion ',
' trillion ',
' quadrillion ',
' quintillion ',
' sextillion ',
' septillion ',
' octillion ',
' decillion ',
' undecillion ',
' duodecillion '
);
p_counter INT := 0;
BEGIN
WHILE p_value > 0 LOOP
p_counter := p_counter + 1;
IF MOD( p_value, 1000 ) > 0 THEN
p_words := TO_CHAR( TO_DATE( MOD( p_value, 1000 ), 'j' ), 'jsp' ) || t_array( p_counter ) || p_words;
END IF;
p_value := FLOOR( p_value / 1000 );
END LOOP;
RETURN CASE WHEN in_value < 0 THEN RTRIM( 'minus ' || p_words )
WHEN in_value = 0 THEN 'Zero'
ELSE RTRIM( p_words ) END;
END;
/
Запрос:
SELECT TO_WORDS( 999999999999998 ) FROM DUAL;
Выход:
TO_WORDS(999999999999998)
--------------------------------------------------------------------------------
nine hundred ninety-nine trillion nine hundred ninety-nine billion nine hundred
ninety-nine million nine hundred ninety-nine thousand nine hundred ninety-eight
Обновление - Использование только SQL
(Обратите внимание, это не просто)
Настройка схемы Oracle 11g R2:
CREATE TABLE NUMBERS ( value ) AS
SELECT 123456789012345678901 FROM DUAL UNION ALL
SELECT 999000 FROM DUAL;
Запрос 1:
WITH words ( id, word ) AS (
SELECT 0, null FROM DUAL UNION ALL
SELECT 1, ' thousand' FROM DUAL UNION ALL
SELECT 2, ' million' FROM DUAL UNION ALL
SELECT 3, ' billion' FROM DUAL UNION ALL
SELECT 4, ' trillion' FROM DUAL UNION ALL
SELECT 5, ' quadrillion' FROM DUAL UNION ALL
SELECT 6, ' quintillion' FROM DUAL UNION ALL
SELECT 7, ' sextillion' FROM DUAL UNION ALL
SELECT 8, ' septillion' FROM DUAL UNION ALL
SELECT 9, ' octillion' FROM DUAL UNION ALL
SELECT 10, ' decillion' FROM DUAL UNION ALL
SELECT 11, ' undecillion' FROM DUAL UNION ALL
SELECT 12, ' duodecillion' FROM DUAL
)
SELECT MAX( n.value ) AS value,
LISTAGG(
CASE
WHEN MOD( FLOOR( n.value / POWER( 1000, t.column_value ) ), 1000 ) = 0
THEN NULL
ELSE TO_CHAR(
TO_DATE(
MOD( FLOOR( n.value / POWER( 1000, t.column_value ) ), 1000 ),
'j'
),
'jsp'
) || w.word
END,
' '
) WITHIN GROUP ( ORDER BY t.COLUMN_VALUE DESC )
AS to_words
FROM numbers n,
TABLE(
CAST(
MULTISET(
SELECT LEVEL - 1
FROM DUAL
CONNECT BY POWER( 1000, LEVEL - 1 ) < n.value
) AS SYS.ODCINUMBERLIST
)
) t,
words w
WHERE t.column_value = w.id
GROUP BY n.ROWID
| VALUE | TO_WORDS |
|-----------------------|--------------------------------------|
| 12345678901234568901 | one hundred twenty-three quintillion |
| | four hundred fifty-six quadrillion |
| | seven hundred eighty-nine trillion |
| | twelve billion |
| | three hundred forty-five million |
| | six hundred seventy-eight thousand |
| | nine hundred one |
| | |
| 999000 | nine hundred ninety-nine thousand |