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

Question

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.

Answer 1

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;