I have a problem with running below procedure in oracle 11g because of the JSON value feature not available in 11g. Can you please help in correcting the procedure.
create or replace PROCEDURE "STANDARDIZE_ADDRESS" ( JSON_DOC IN VARCHAR2 , JSON_OUT OUT VARCHAR2 ) is CNTRY_IN VARCHAR2(100); STATE_IN VARCHAR2(100); CITY_IN VARCHAR2(100); LAT_IN VARCHAR2(100); LONG_IN VARCHAR(100); SOURCE VARCHAR2(100); COUNTRY_NAME VARCHAR2(100); COUNTRY_ID RAW(16); STATE_NAME VARCHAR2(100); STATE_ID RAW(16); CITY_NAME VARCHAR2(100); CITY_ID RAW(16); REGION_ID RAW(16); MAP_COUNTRY VARCHAR2(100); MAP_STATE VARCHAR2(100); MAP_CITY VARCHAR(100); COUNTRYSEQ NUMBER; UNK_REGION RAW(16); OUTPUT_STR VARCHAR2(3000); QUOTE_REPLACE VARCHAR2(4); BEGIN QUOTE_REPLACE := 'zqzq'; UNK_REGION := HEXTORAW('00000000000000000000000000000000'); -- Parse values from JSON select json_value(json_doc,'$ .Country'), json_value(json_doc,'$ .State'), json_value(json_doc,'$ .City'), -- json_value(json_doc,'$ .Latitude'), -- json_value(json_doc,'$ .Longitude'), json_value(json_doc,'$ .Source') into cntry_in, state_in, city_in, source from dual; -- Any blanks get treated as "None" (so there are no null entries in DB CNTRY_IN := NVL(CNTRY_IN,'none'); STATE_IN := NVL(STATE_IN,'none'); CITY_IN := NVL(CITY_IN,'none'); SOURCE := UPPER(NVL(SOURCE,'unknown')); -- Check to see if passed in address matches Master data BEGIN -- Query against standard list select COUNTRYNAME, COUNTRYID, STATENAME, STATEID, CITYNAME, CITYID into COUNTRY_NAME, COUNTRY_ID, STATE_NAME, STATE_ID, CITY_NAME, CITY_ID from STANDARDLIST_VIEW where (CountryName = CNTRY_IN or CountryCode = UPPER(CNTRY_IN)) and StateName = STATE_IN and CityName = CITY_IN; DBMS_OUTPUT.PUT_LINE('Using Standard Data'); EXCEPTION WHEN OTHERS THEN -- Replace special characters and Standardize case of data if (LENGTH(CNTRY_IN) > 2) then -- INITCAP function does not correctly case the character after an apostrophe. CNTRY_IN := REPLACE_SYMBOLS(REPLACE(INITCAP(CNTRY_IN),chr(39)||'S',chr(39)||'s')); else -- Two characters are assumed to be CountryCode CNTRY_IN := REPLACE_SYMBOLS(UPPER(CNTRY_IN)); end if; -- INITCAP function does not correctly case the character after an apostrophe. STATE_IN := REPLACE_SYMBOLS(REPLACE(INITCAP(STATE_IN),chr(39)||'S',chr(39)||'s')); if (LENGTH(STATE_IN) = 2 and (UPPER(CNTRY_IN) = 'UNITED STATES' or UPPER(CNTRY_IN) = 'US')) then -- Special case of US States are upper case 2-character, not title-cased STATE_IN := UPPER(STATE_IN); end if; -- INITCAP function does not correctly case the character after an apostrophe. CITY_IN := REPLACE_SYMBOLS(REPLACE(INITCAP(CITY_IN),chr(39)||'S',chr(39)||'s')); BEGIN -- Check mapping data to see if this has been translated to standard values select stdcountry,stdstate,stdcity into MAP_COUNTRY, MAP_STATE, MAP_CITY from GEO_MAPPING_VIEW where upper(srccountry) = upper(CNTRY_IN) and upper(srcstate) = upper(STATE_IN) and upper(srccity) = upper(CITY_IN); -- If we found a match use this data for lookup, else use our cleaned up data CNTRY_IN := MAP_COUNTRY; STATE_IN := MAP_STATE; CITY_IN := MAP_CITY; DBMS_OUTPUT.PUT_LINE('Found Mapped Data'); EXCEPTION WHEN OTHERS THEN -- don't worry about error. Use cleaned up data for building Non-Stand entries COUNTRY_ID := null; -- dummy statement to trap error END; BEGIN -- Query against standard list select COUNTRYNAME, COUNTRYID, STATENAME, STATEID, CITYNAME, CITYID into COUNTRY_NAME, COUNTRY_ID, STATE_NAME, STATE_ID, CITY_NAME, CITY_ID from STANDARDLIST_VIEW where (CountryName = CNTRY_IN or CountryCode = CNTRY_IN) and StateName = STATE_IN -- (StateName = STATE_IN or StateCode = UPPER(STATE_IN)) and CityName = CITY_IN; DBMS_OUTPUT.PUT_LINE('Using Mapped/Cleaned Data'); EXCEPTION -- Build out table entries based on what's missing WHEN OTHERS THEN -- Query Country table by CountryName/CountryCode BEGIN select COUNTRYNAME, ID into COUNTRY_NAME, COUNTRY_ID from MASTER_COUNTRIES where CountryName = CNTRY_IN or CountryCode = CNTRY_IN or CountryISO3Code = UPPER(CNTRY_IN); EXCEPTION WHEN OTHERS THEN -- If record not found, Create Country entry COUNTRY_ID := null; COUNTRY_NAME := CNTRY_IN; -- select ID into REGION_ID from MASTER_REGIONS where REGION = 'NAM'; REGION_ID := UNK_REGION; select SYS_GUID() into COUNTRY_ID from dual; -- Create value for CountryCode (starts at 10; iso3 issequnce value + 900) select SEQ_COUNTRYCODE.NEXTVAL into COUNTRYSEQ from dual; insert into MASTER_COUNTRIES (ID, COUNTRYCODE, COUNTRYISO3CODE, COUNTRYNAME, REGIONID, NONSTDSOURCE, NONSTDDATE, LASTMODIFIED) values (COUNTRY_ID, to_char(countryseq), to_char(countryseq + 900), COUNTRY_NAME, REGION_ID, SOURCE, SYSDATE, SYSTIMESTAMP); END; -- Query State table by StateName and CountryID BEGIN select STATENAME, STATEID into STATE_NAME, STATE_ID from MASTER_STATES where StateName = STATE_IN and CountryID = COUNTRY_ID; EXCEPTION WHEN OTHERS THEN -- If record not found, Create State entry STATE_ID := null; STATE_NAME := STATE_IN; select SYS_GUID() into STATE_ID from dual; insert into MASTER_STATES (STATEID, STATENAME, COUNTRYID, NONSTDSOURCE, NONSTDDATE, LASTMODIFIED) values (STATE_ID, STATE_NAME, COUNTRY_ID, SOURCE, SYSDATE, SYSTIMESTAMP); END; -- Query City table by CityName and StateID BEGIN select CITYNAME, CITYID into CITY_NAME, CITY_ID from MASTER_CITIES where CityName = CITY_IN and StateID = STATE_ID; EXCEPTION WHEN OTHERS THEN -- If record not found, Create City entry CITY_ID := null; CITY_NAME := CITY_IN; select SYS_GUID() into CITY_ID from dual; insert into MASTER_CITIES (CITYID, CITYNAME, STATEID, NONSTDSOURCE, NONSTDDATE, LASTMODIFIED) values (CITY_ID, CITY_NAME, STATE_ID, SOURCE, SYSDATE, SYSTIMESTAMP); END; DBMS_OUTPUT.PUT_LINE('Creating Non-Standard Data'); END; END; -- dbms_output.put_line(json_doc); -- Build JSON Output output_str := '{"Country":{"Name":"' || COUNTRY_NAME || '","ID":"' || RAWTOHEX(COUNTRY_ID) || '"},' || '"State":{"Name":"' || STATE_NAME || '","ID":"' || RAWTOHEX(STATE_ID) || '"},' || '"City":{"Name":"' || CITY_NAME || '","ID":"' || RAWTOHEX(CITY_ID) || '"}}'; -- dbms_output.put_line(cntry_in||','||state_in||','||city_in||','||source); -- dbms_output.put_line(country_name||','||country_id||','||state_name||','||state_id||','||city_name||','||city_id); json_out := output_str; EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE(SQLERRM); json_out := '{}'; END;