Plsql for spell number (currency) in Italian currency without hard coding of the transfer number

Question

I could not find a message to do this automatically, since I do not want to hardcode the mapping between English and Italian.

Is there a way to spell number in 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;