Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
1.3k views
in Technique[技术] by (71.8m points)

oracle - Plsql to spell number (currency) to Italian currency without hardcoded the translation number

I could not find a post to do it automatically as I don't want to hard coded the mapping between English to Italian.

Is there any way to write the spelling number to any language in PLSQL, Oracle 10g or 11i ?

I do not have this built in package: AP_AMOUNT_UTILITIES_PKG in my oracle.

See Question&Answers more detail:os

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Reply

0 votes
by (71.8m points)
CREATE OR REPLACE FUNCTION Currency_conversion(currency IN NUMBER) 
RETURN VARCHAR2 IS 
    txt varchar2(4000);
    tlum  varchar2(4000);
    babelTxt varchar(32000);
    req   utl_http.req;
    resp  utl_http.resp;
    webtext VARCHAR2(4000);
    webextract VARCHAR2(4000);
BEGIN
    -- This is used to convert number to words in english
    SELECT CAST( to_char( to_timestamp( lpad(currency,9,'0'), 'FF9' ), 'FFSP' ) AS VARCHAR2(100) )
    into txt from dual;

    -- FFSP is used to spell Fraction second in words for example HH24SP
    -- can be used to spell hours from 0 to 23,i used FFSP to get max range
    txt:=translate(txt,'x-','x ');

    -- The below logic is taken from
    -- https://forums.oracle.com/forums/thread.jspa?threadID=1122273(and)start=15(and)    tstart=0
    --need to test once
    -- request that exceptions are raised for error status codes
    utl_http.set_response_error_check(enable => TRUE); 
    -- allow testing for exceptions like Utl_Http.Http_Server_Error
    utl_http.set_detailed_excp_support(enable => TRUE);

    txt:=utl_url.escape(txt);

    babelTxt := 'http://babelfish.yahoo.com/translate_txt?doit=done' || '&'   || 'lp=en_it' || '&' || 'intl=1' || '&' || 'ei=utf8' || '&' || 'trtext=' || txt;
    req:=utl_http.begin_request(url => babelTxt, method => 'GET');
    resp := utl_http.get_response(r => req);
    BEGIN
        LOOP
            --utl_http.read_text(r => resp, data => webtext, len => 32767);
                utl_http.read_line(r => resp , data => webtext);

            webextract := regexp_substr(webtext,'<div id="result"><div style="padding:0.6em;">(.+)</div></div>');

            if webextract is not null 
            then
                select regexp_replace(webextract,'<div id="result"><div style="padding:0.6em;">(.+)</div></div>','1')        
                into tlum FROM dual;
            end if;

            exit when tlum is not null;

            END LOOP;
    EXCEPTION
     WHEN utl_http.end_of_body THEN
     NULL;
    END;



    utl_http.end_response(r => resp);

    return tlum;

    EXCEPTION
        WHEN OTHERS THEN
        dbms_output.put_line(SQLERRM);
        return '';
END;
/
exit;

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
OGeek|极客中国-欢迎来到极客的世界,一个免费开放的程序员编程交流平台!开放,进步,分享!让技术改变生活,让极客改变未来! Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

...