ORA-01830 将数字转换为单词时 [英] ORA-01830 when converting number to words

问题描述

高值是十进制格式，例如.- 100.10，我想把它转换成word，所以我写在脚本下面，但没有通过这个执行..

High value is in decimal format eg.- 100.10, I want to convert it into word so I write below script but not getting execution by this..

SELECT SYMBOL, HIGH, UPPER(TO_CHAR(TO_DATE(HIGH,'J'),'JSP'))
AMT_IN_WORDS FROM BHAV; 

获取错误

ORA-01830

请纠正错误的地方......

please correct this where am wrong....

先谢谢你...

推荐答案

您可以创建一个函数.

CREATE OR REPLACE FUNCTION big_amt_in_words (p_input VARCHAR2) RETURN VARCHAR2
IS
   v_running_input NUMBER;
   v_num NUMBER;
   v_amt_in_words VARCHAR2(2000);
BEGIN
   v_running_input := P_input;
   FOR i IN (
             SELECT RPAD(1, (rownum*3)+1, 0) num_value,
                    CASE LENGTH(RPAD(1, (rownum*3)+1, 0))
                        WHEN 4 THEN 'THOUSAND'
                        WHEN 7 THEN 'MILLION'
                        WHEN 10 THEN 'BILLION'
                        WHEN 13 THEN 'TRILLION'
                        WHEN 16 THEN 'QUADRILLION'
                        WHEN 19 THEN 'QUINTILLION'
                        WHEN 22 THEN 'SEXTILLION'
                        WHEN 25 THEN 'SEPTILLION'
                        WHEN 28 THEN 'OCTILLION'
                    END place_value
              FROM DUAL
           CONNECT BY rownum < 10
             ORDER BY rownum desc)
    LOOP
        v_num := TRUNC(v_running_input/i.num_value,0);
        IF v_num > 0 THEN
            v_amt_in_words := v_amt_in_words||' '||TO_CHAR(TO_DATE(v_num,'J'), 'JSP')||' '||i.place_value;
            v_running_input := v_running_input - (v_num * i.num_value);
        END IF;
    END LOOP;
    v_amt_in_words := v_amt_in_words||' '||TO_CHAR(TO_DATE(TRUNC(v_running_input),'J'), 'JSP')
                      ||' AND '||UPPER(TO_CHAR(TO_DATE((ROUND(v_running_input-TRUNC(v_running_input),2)*100),'J'),'JSP'))||' CENTS';
    RETURN TRIM(v_amt_in_words);
END;
/

要使用它，

SELECT BIG_AMT_IN_WORDS(65763245345658.12) amt_in_words 
  FROM DUAL;

Output
---------------------------------------------
SIXTY-FIVE TRILLION SEVEN HUNDRED SIXTY-THREE BILLION TWO HUNDRED FORTY-FIVE MILLION THREE HUNDRED FORTY-FIVE THOUSAND SIX HUNDRED FIFTY-EIGHT AND TWELVE CENTS

这篇关于ORA-01830 将数字转换为单词时的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！