--RAW Domain Value JSON services      
--**************************************************************************************************************    
-- Date: 2023-02-24
--Illegal Characters: " \ chr(10)   Converted to Characters:  ^ / |
--
--Replacement Legal Characters:   \"   \\\   |
--
---JSON. simple - Escaping Special Characters
---Backspace to be replaced with \b.
---Form feed to be replaced with \f.
---Newline to be replaced with \n.
---Carriage return to be replaced with \r.
---Tab to be replaced with \t.
---Double quote to be replaced with \"
---Backslash to be replaced with \\
---
---JSON.simple - Escaping Special Characters - 
---Tutorialspointhttps://www.tutorialspoint.com › json_simple › json_simpl...
--
---What special characters break JSON?
---In JSON the only characters you must escape are \, ", and control codes. Thus in order to escape your structure, you'll need a JSON specific function. As you might know, all of the escapes can be written as \uXXXX where XXXX is the UTF-16 code unit¹ for that character.Oct 4, 2018
---
---Escaping strings in JSON | Edureka Communityhttps://www.edureka.co › community › escaping-strings-i...
--
SET TERMOUT OFF;
set feedback off
set verify off
set linesize 5000
set define off
set pagesize 0
set trimspool on


--
spool ActivityGroupType.json
--headerinfo: 
select	
    '{' ||chr(10)||
	'    "root": {' ||chr(10)||
	'      "Activity Group Type": [' 
--    '{' ||chr(10)||
--    '   "WQXDomainValue": "' ||chr(10)||
--    '      "WQXEntity": "' ||chr(10)||
--    '         "WQXElement": "' ||chr(10)||
--    '            "WQXElementRow": [' 
from dual;
SELECT 
--SELECT replace( LTRIM(MAX(SYS_CONNECT_BY_PATH(elementrow, '|')), '|'), '|', ','||chr(10)) as elementrecord
	case when rn=1 then '' else ',' end as rowdelimiter, 
	elementrow 
from ( SELECT rownum rn, elementrow
FROM (SELECT
	'      {' ||chr(10)|| 
	'      "Domain": "Activity Group Type(ActivityGroupTypeName)",' ||chr(10)||
	'      "UID": "'||agtyp_uid||'",' ||chr(10)||
	'      "Name": "'|| replace(replace(translate(agtyp_name,'"\'||chr(10),'^/|'),'^','\"'),'/','\')||'",' ||chr(10)||
	'      "Description": "'|| replace(replace(translate(agtyp_desc,'"\'||chr(10),'^/|'),'^','\"'),'/','\')||'",' ||chr(10)||
	'      "LastChangeDate": "'||to_char(cast(agtyp_last_change_date as timestamp with LOCAL time zone), 'YYYY-MM-DD"T"HH24:MI:SS TZR') ||'"'||chr(10)|| 
	'      }' 
 as elementrow  
from wqx.activity_group_type 
order by agtyp_name
) 
) 
--START WITH rn = 1 
--CONNECT BY rn = PRIOR rn + 1 
/ 
--footerinfo: 
select 
'            ]' ||chr(10)||
'	}' ||chr(10)||
'}' 
from dual;
--	'            ],' ||chr(10)||
--	'       "_domainEntity": "Activity Group Type", ' ||chr(10)||
--	'       "schemaElement": "ActivityGroupType/Name", ' ||chr(10)||
--	'       "rowCount": "'||(select count(*) from wqx.activity_group_type) ||'", '||chr(10)||
--	'       "maxLastChangeDate": "'||to_char(cast((select max(agtyp_last_change_date) ||'"'
--from wqx.activity_group_type) as timestamp with LOCAL time zone), 'YYYY-MM-DD"T"HH24:MI:SSTZR')||'" --'||chr(10)||
--	'    }' ||chr(10)||
--	'  },' ||chr(10)||
--	'      "_xmlns": "http://www.exchangenetwork.net/schema/wqx/3", "_xmlns:xsi": "http://www.w3.org/2001/XMLSchema-instance",' ||chr(10)||
--    ' "_xsi:schemaLocation": "http://www.exchangenetwork.net/schema/wqx/3 http://www.exchangenetwork.net/schema/wqx/3/index.xsd"' ||chr(10)||
spool off

--
spool ActivityMedia.json
--headerinfo: 
select	
    '{' ||chr(10)||
	'    "root": {' ||chr(10)||
	'      "Activity Media": [' 
--	'  "WQXEntity": "' ||chr(10)||
--	'    "WQXElement": " domainEntity="Activity Media" schemaElement="ActivityMediaName/Name" 
--rowCount="'||(select count(*) from wqx.activity_media)||'" maxLastChangeDate="'||to_char(cast((select 
--max(acmed_last_change_date) from wqx.activity_media) as timestamp with LOCAL time zone), 
--'YYYY-MM-DD"T"HH24:MI:SSTZR')||'" }'
from dual;
SELECT 
--SELECT replace( LTRIM(MAX(SYS_CONNECT_BY_PATH(elementrow, '|')), '|'), '|', ','||chr(10)) as elementrecord
	case when rn=1 then '' else ',' end as rowdelimiter, 
	elementrow 
from ( SELECT rownum rn, elementrow
FROM (SELECT 
	'      {' ||chr(10)|| 
	'      "Domain": "Activity Media(ActivityMediaName)",' ||chr(10)||
	'      "UID": "'||acmed_uid||'",' ||chr(10)||
	'      "Name": "'|| replace(replace(translate(acmed_name,'"\'||chr(10),'^/|'),'^','\"'),'/','\')||'",' ||chr(10)||
	'      "Description": "'|| replace(replace(translate(acmed_desc,'"\'||chr(10),'^/|'),'^','\"'),'/','\')||'",' ||chr(10)||
	'      "LastChangeDate": "'||to_char(cast(acmed_last_change_date as timestamp with LOCAL time zone), 'YYYY-MM-DD"T"HH24:MI:SS TZR') ||'"'||chr(10)|| 
	'      }' 
 as elementrow 
from wqx.activity_media 
order by acmed_name
) 
) 
--START WITH rn = 1 
--CONNECT BY rn = PRIOR rn + 1 
/ 
--footerinfo: 
select 
'            ]' ||chr(10)||
'	}' ||chr(10)||
'}' 
from dual;
spool off

--
spool ActivityMediaSubdivision.json
--headerinfo: 
select	
    '{' ||chr(10)||
	'    "root": {' ||chr(10)||
	'      "Activity Media Subdivision": [' 
--	'  "WQXEntity": "' ||chr(10)||
--	'    "WQXElement": " domainEntity="Activity Media Subdivision" 
--schemaElement="ActivityMediaSubdivisionName/Name" rowCount="'||(select count(*) 
--from wqx.activity_media_subdivision)||'" maxLastChangeDate="'||to_char(cast((select max(amsub_last_change_date) 
--wqx.activity_media_subdivision) as timestamp with LOCAL time zone), 'YYYY-MM-DD"T"HH24:MI:SSTZR')||'" }'
from dual;
SELECT 
--SELECT replace( LTRIM(MAX(SYS_CONNECT_BY_PATH(elementrow, '|')), '|'), '|', ','||chr(10)) as elementrecord
	case when rn=1 then '' else ',' end as rowdelimiter, 
	elementrow 
from ( SELECT rownum rn, elementrow
FROM (SELECT 
	'      {' ||chr(10)|| 
	'      "Domain": "Activity Media Subdivision(ActivityMediaSubdivisionName)",' ||chr(10)||
	'      "UID": "'||amsub_uid||'",' ||chr(10)||
	'      "Name": "'|| replace(replace(translate(amsub_name,'"\'||chr(10),'^/|'),'^','\"'),'/','\')||'",' ||chr(10)||
	'      "Description": "'|| replace(replace(translate(amsub_desc,'"\'||chr(10),'^/|'),'^','\"'),'/','\')||'",' ||chr(10)||
	'      "LastChangeDate": "'||to_char(cast(amsub_last_change_date as timestamp with LOCAL time zone), 'YYYY-MM-DD"T"HH24:MI:SS TZR')||'"'||chr(10)|| 
	'      }' 
 as elementrow 
from wqx.activity_media_subdivision 
order by amsub_name
) 
) 
--START WITH rn = 1 
--CONNECT BY rn = PRIOR rn + 1 
/ 
--footerinfo: 
select 
'            ]' ||chr(10)||
'	}' ||chr(10)||
'}' 
from dual;
spool off

--
spool ActivityType.json
--headerinfo: 
select	
    '{' ||chr(10)||
	'    "root": {' ||chr(10)||
	'      "Activity Type": [' 
--	'  "WQXEntity": "' ||chr(10)||
--	'    "WQXElement": " domainEntity="Activity Type" schemaElement="ActivityTypeName/Name" 
--rowCount="'||(select count(*) from wqx.activity_type)||'" maxLastChangeDate="'||to_char(cast((select 
--max(actyp_last_change_date) from wqx.activity_type) as timestamp with LOCAL time zone), 
--'YYYY-MM-DD"T"HH24:MI:SSTZR')||'" }'
from dual;
SELECT 
--SELECT replace( LTRIM(MAX(SYS_CONNECT_BY_PATH(elementrow, '|')), '|'), '|', ','||chr(10)) as elementrecord
	case when rn=1 then '' else ',' end as rowdelimiter, 
	elementrow 
from ( SELECT rownum rn, elementrow
FROM (SELECT 
	'      {' ||chr(10)|| 
	'      "Domain": "Activity Type(ActivityTypeCode)",' ||chr(10)||
	'      "UID": "'||actyp_uid||'",' ||chr(10)||
	'      "Name": "'|| replace(replace(translate(actyp_cd,'"\'||chr(10),'^/|'),'^','\"'),'/','\')||'",' ||chr(10)||
	'      "Description": "'|| replace(replace(translate(actyp_desc,'"\'||chr(10),'^/|'),'^','\"'),'/','\')||'",' ||chr(10)||
	'      "AnalyticalMethodRequiredYN": "'||actyp_anl_mth_req_yn||'",' ||chr(10)||
	'      "MonitoringLocationRequiredYN": "'||actyp_mon_loc_req_yn||'",' ||chr(10)||
	'      "Abbreviation": "'||actyp_abbrev||'",' ||chr(10)||
	'      "LastChangeDate": "'||to_char(cast(actyp_last_change_date as timestamp with LOCAL time zone), 'YYYY-MM-DD"T"HH24:MI:SS TZR') ||'"'||chr(10)|| 
	'      }' 
 as elementrow 
from wqx.activity_type 
order by actyp_cd
) 
) 
--START WITH rn = 1 
--CONNECT BY rn = PRIOR rn + 1 
/ 
--footerinfo: 
select 
'            ]' ||chr(10)||
'	}' ||chr(10)||
'}' 
from dual;
spool off

--
spool AddressType.json
--headerinfo: 
select	
    '{' ||chr(10)||
	'    "root": {' ||chr(10)||
	'      "Address Type": [' 
--	'  "WQXEntity": "' ||chr(10)||
--	'    "WQXElement": " domainEntity="Address Type" schemaElement="AddressTypeName/Name" 
--rowCount="'||(select count(*) from wqx.address_type)||'" maxLastChangeDate="'||to_char(cast((select 
--max(acmed_last_change_date) from wqx.address_type) as timestamp with LOCAL time zone), 
--'YYYY-MM-DD"T"HH24:MI:SSTZR')||'" }'
from dual;
SELECT 
--SELECT replace( LTRIM(MAX(SYS_CONNECT_BY_PATH(elementrow, '|')), '|'), '|', ','||chr(10)) as elementrecord
	case when rn=1 then '' else ',' end as rowdelimiter, 
	elementrow 
from ( SELECT rownum rn, elementrow
FROM (SELECT 
	'      {' ||chr(10)|| 
	'      "Domain": "Address Type(AddressTypeName)",' ||chr(10)||
	'      "UID": "'||addtyp_uid||'",' ||chr(10)||
	'      "Name": "'|| replace(replace(translate(addtyp_name,'"\'||chr(10),'^/|'),'^','\"'),'/','\')||'",' ||chr(10)||
	'      "Description": "'|| replace(replace(translate(addtyp_desc,'"\'||chr(10),'^/|'),'^','\"'),'/','\')||'",' ||chr(10)||
	'      "LastChangeDate": "'||to_char(cast(addtyp_last_change_date as timestamp with LOCAL time zone), 'YYYY-MM-DD"T"HH24:MI:SS TZR') ||'"'||chr(10)|| 
	'      }' 
 as elementrow 
from wqx.address_type 
order by addtyp_name
) 
) 
--START WITH rn = 1 
--CONNECT BY rn = PRIOR rn + 1 
/ 
--footerinfo: 
select 
'            ]' ||chr(10)||
'	}' ||chr(10)||
'}' 
from dual;
spool off

--
spool AliasType.json
--headerinfo: 
select	
    '{' ||chr(10)||
	'    "root": {' ||chr(10)||
	'      "Alias Type": [' 
--	'  "WQXEntity": "' ||chr(10)||
--	'    "WQXElement": " domainEntity="Alias Type" schemaElement="AliasTypeName/Name" rowCount="'||(select 
--count(*) from wqx.alias_type)||'" maxLastChangeDate="'||to_char(cast((select max(alstyp_last_change_date) 
--from wqx.alias_type) as timestamp with LOCAL time zone), 'YYYY-MM-DD"T"HH24:MI:SSTZR')||'" }'
from dual;
SELECT 
--SELECT replace( LTRIM(MAX(SYS_CONNECT_BY_PATH(elementrow, '|')), '|'), '|', ','||chr(10)) as elementrecord
	case when rn=1 then '' else ',' end as rowdelimiter, 
	elementrow 
from ( SELECT rownum rn, elementrow
FROM (SELECT 
	'      {' ||chr(10)|| 
	'      "Domain": "Alias Type(AliasTypeName)",' ||chr(10)||
	'      "UID": "'||alstyp_uid||'",' ||chr(10)||
	'      "Name": "'|| replace(replace(translate(alstyp_name,'"\'||chr(10),'^/|'),'^','\"'),'/','\')||'",' ||chr(10)||
--	'      "Description": "'|| replace(replace(translate(alstyp_desc||'",' ||chr(10)||
	'      "LastChangeDate": "'||to_char(cast(alstyp_last_change_date as timestamp with LOCAL time zone), 'YYYY-MM-DD"T"HH24:MI:SS TZR') ||'"'||chr(10)|| 
	'      }' 
 as elementrow 
from wqx.alias_type 
order by alstyp_name
) 
) 
--START WITH rn = 1 
--CONNECT BY rn = PRIOR rn + 1 
/ 
--footerinfo: 
select 
'            ]' ||chr(10)||
'	}' ||chr(10)||
'}' 
from dual;
spool off

--
spool AnalyticalMethod.json
--headerinfo: 
select	
    '{' ||chr(10)||
	'    "root": {' ||chr(10)||
	'      "Analytical Method": [' 
--	'  "WQXEntity": "' ||chr(10)||
--	'    "WQXElement": " domainEntity="Analytical Method" schemaElement="AnalyticalMethodName/Name" 
--rowCount="'||(select count(*) from wqx.analytical_method)||'" maxLastChangeDate="'||to_char(cast((select 
--max(anlmth_last_change_date) from wqx.analytical_method) as timestamp with LOCAL time zone), 
--'YYYY-MM-DD"T"HH24:MI:SSTZR')||'" }'
from dual;
SELECT 
--SELECT replace( LTRIM(MAX(SYS_CONNECT_BY_PATH(elementrow, '|')), '|'), '|', ','||chr(10)) as elementrecord
	case when rn=1 then '' else ',' end as rowdelimiter, 
	elementrow 
from ( SELECT rownum rn, elementrow
FROM (SELECT 
	'      {' ||chr(10)|| 
	'      "Domain": "Analytical Method(MethodIdentifier)",' ||chr(10)||
	'      "UID": "'||anlmth_uid||'",' ||chr(10)||
	'      "Identifier": "'|| replace(replace(translate(anlmth_id,'"\'||chr(10),'^/|'),'^','\"'),'/','\')||'",' ||chr(10)||
	'      "Context": "'|| replace(replace(translate(amctx_cd,'"\'||chr(10),'^/|'),'^','\"'),'/','\')||'",' ||chr(10)||
	'      "Name": "'|| replace(replace(translate(anlmth_name,'"\'||chr(10),'^/|'),'^','\"'),'/','\')||'",' ||chr(10)||
	'      "Description": "'|| replace(replace(translate(anlmth_desc,'"\'||chr(10),'^/|'),'^','\"'),'/','\')||'",' ||chr(10)||
	'      "QualifierType": "'|| replace(replace(translate(anlmth_qual_type,'"\'||chr(10),'^/|'),'^','\"'),'/','\')||'",' ||chr(10)||
	'      "URL": "'|| replace(replace(translate(anlmth_url,'"\'||chr(10),'^/|'),'^','\"'),'/','\')||'",' ||chr(10)||
	'      "LastChangeDate": "'||to_char(cast(anlmth_last_change_date as timestamp with LOCAL time zone), 'YYYY-MM-DD"T"HH24:MI:SS TZR') ||'"'||chr(10)|| 
	'      }' 
 as elementrow 
from wqx.analytical_method am, wqx.analytical_method_context amc 
where am.amctx_uid=amc.amctx_uid and am.org_uid is null
order by anlmth_id
) 
) 
--START WITH rn = 1 
--CONNECT BY rn = PRIOR rn + 1 
/ 
--footerinfo: 
select 
'            ]' ||chr(10)||
'	}' ||chr(10)||
'}' 
from dual;
spool off

--
spool AnalyticalMethodContext.json
--headerinfo: 
select	
    '{' ||chr(10)||
	'    "root": {' ||chr(10)||
	'      "Analytical Method Context": [' 
--	'  "WQXEntity": "' ||chr(10)||
--	'    "WQXElement": " domainEntity="Analytical Method Context" 
--schemaElement="AnalyticalMethodContextName/Name" rowCount="'||(select count(*) from 
--wqx.analytical_method_context)||'" maxLastChangeDate="'||to_char(cast((select max(amctx_last_change_date) 
--from wqx.analytical_method_context) as timestamp with LOCAL time zone), 'YYYY-MM-DD"T"HH24:MI:SSTZR')||'" }'
from dual;
SELECT 
--SELECT replace( LTRIM(MAX(SYS_CONNECT_BY_PATH(elementrow, '|')), '|'), '|', ','||chr(10)) as elementrecord
	case when rn=1 then '' else ',' end as rowdelimiter, 
	elementrow 
from ( SELECT rownum rn, elementrow
FROM (SELECT 
	'      {' ||chr(10)|| 
	'      "Domain": "Analytical Method Context(MethodIdentifierContext)",' ||chr(10)||
	'      "UID": "'||amctx_uid||'",' ||chr(10)||
	'      "Context": "'|| replace(replace(translate(amctx_cd,'"\'||chr(10),'^/|'),'^','\"'),'/','\')||'",' ||chr(10)||
	'      "Name": "'|| replace(replace(translate(amctx_name,'"\'||chr(10),'^/|'),'^','\"'),'/','\')||'",' ||chr(10)||
	'      "Description": "'|| replace(replace(translate(amctx_desc,'"\'||chr(10),'^/|'),'^','\"'),'/','\')||'",' ||chr(10)||
	'      "LastChangeDate": "'||to_char(cast(amctx_last_change_date as timestamp with LOCAL time zone), 'YYYY-MM-DD"T"HH24:MI:SS TZR') ||'"'||chr(10)|| 
	'      }' 
 as elementrow 
from wqx.analytical_method_context 
where org_uid is null 
order by amctx_cd
) 
) 
--START WITH rn = 1 
--CONNECT BY rn = PRIOR rn + 1 
/ 
--footerinfo: 
select 
'            ]' ||chr(10)||
'	}' ||chr(10)||
'}' 
from dual;
spool off

--
spool AquiferType.json
--headerinfo: 
select	
    '{' ||chr(10)||
	'    "root": {' ||chr(10)||
	'      "Aquifer Type": [' 
--	'  "WQXEntity": "' ||chr(10)||
--	'    "WQXElement": " domainEntity="Aquifer Type" schemaElement="AquiferTypeName" 
--rowCount="'||(select count(*) from wqx.aquifer_type)||'" maxLastChangeDate="'||to_char(cast((select 
--max(aqfrtyp_last_change_date) from wqx.aquifer_type) as timestamp with LOCAL time zone), 
--'YYYY-MM-DD"T"HH24:MI:SSTZR')||'" }'
from dual;
SELECT 
--SELECT replace( LTRIM(MAX(SYS_CONNECT_BY_PATH(elementrow, '|')), '|'), '|', ','||chr(10)) as elementrecord
	case when rn=1 then '' else ',' end as rowdelimiter, 
	elementrow 
from ( SELECT rownum rn, elementrow
FROM (SELECT 
	'      {' ||chr(10)|| 
	'      "Domain": "Aquifer Type(AquiferTypeName)",' ||chr(10)||
	'      "UID": "'||aqfrtyp_uid||'",' ||chr(10)||
	'      "Name": "'|| replace(replace(translate(aqfrtyp_name,'"\'||chr(10),'^/|'),'^','\"'),'/','\')||'",' ||chr(10)||
	'      "Description": "'|| replace(replace(translate(aqfrtyp_desc,'"\'||chr(10),'^/|'),'^','\"'),'/','\')||'",' ||chr(10)||
	'      "LastChangeDate": "'||to_char(cast(aqfrtyp_last_change_date as timestamp with LOCAL time zone), 'YYYY-MM-DD"T"HH24:MI:SS TZR') ||'"'||chr(10)|| 
	'      }' 
 as elementrow 
from wqx.aquifer_type 
order by aqfrtyp_name
) 
) 
--START WITH rn = 1 
--CONNECT BY rn = PRIOR rn + 1 
/ 
--footerinfo: 
select 
'            ]' ||chr(10)||
'	}' ||chr(10)||
'}' 
from dual;
spool off

--
spool Aquifer.json
--headerinfo: 
select	
    '{' ||chr(10)||
	'    "root": {' ||chr(10)||
	'      "Aquifer": [' 
--	'  "WQXEntity": "' ||chr(10)||
--	'    "WQXElement": " domainEntity="Aquifer" schemaElement="LocalAquiferCode" 
--rowCount="'||(select count(*) from wqx.aquifer)||'" maxLastChangeDate="'||to_char(cast((select 
--max(aqfr_last_change_date) from wqx.aquifer) as timestamp with LOCAL time zone), 
--'YYYY-MM-DD"T"HH24:MI:SSTZR')||'" }'
from dual;
SELECT 
--SELECT replace( LTRIM(MAX(SYS_CONNECT_BY_PATH(elementrow, '|')), '|'), '|', ','||chr(10)) as elementrecord
	case when rn=1 then '' else ',' end as rowdelimiter, 
	elementrow 
from ( SELECT rownum rn, elementrow
FROM (SELECT 
	'      {' ||chr(10)|| 
	'      "Domain": "Aquifer(LocalAquiferCode)",' ||chr(10)||
	'      "UID": "'||aqfr_uid||'",' ||chr(10)||
	'      "Code": "'||aqfr_cd||'",' ||chr(10)||
	'      "CodeContext": "'||aqfrctx_cd||'",' ||chr(10)||
	'      "Name": "'|| replace(replace(translate(aqfr_name,'"\'||chr(10),'^/|'),'^','\"'),'/','\')||'",' ||chr(10)||
	'      "Description": "'|| replace(replace(translate(aqfr_desc,'"\'||chr(10),'^/|'),'^','\"'),'/','\')||'",' ||chr(10)||
	'      "LastChangeDate": "'||to_char(cast(aqfr_last_change_date as timestamp with LOCAL time zone), 'YYYY-MM-DD"T"HH24:MI:SS TZR') ||'"'||chr(10)|| 
	'      }' 
 as elementrow 
from wqx.Aquifer aqfr, wqx.Aquifer_context aqfrc 
where aqfr.aqfrctx_uid=aqfrc.aqfrctx_uid and aqfr.org_uid is null
order by aqfr_cd
) 
) 
--START WITH rn = 1 
--CONNECT BY rn = PRIOR rn + 1 
/ 
--footerinfo: 
select 
'            ]' ||chr(10)||
'	}' ||chr(10)||
'}' 
from dual;
spool off

--
spool AquiferContext.json
--headerinfo: 
select	
    '{' ||chr(10)||
	'    "root": {' ||chr(10)||
	'      "Aquifer Context": [' 
--	'  "WQXEntity": "' ||chr(10)||
--	'    "WQXElement": " domainEntity="Aquifer Context" schemaElement="AquiferCodeContext" 
--rowCount="'||(select count(*) from wqx.Aquifer_context)||'" maxLastChangeDate="'||to_char(cast((select 
--max(aqfrctx_last_change_date) from wqx.Aquifer_context) as timestamp with LOCAL time zone), 
--'YYYY-MM-DD"T"HH24:MI:SSTZR')||'" }'
from dual;
SELECT 
--SELECT replace( LTRIM(MAX(SYS_CONNECT_BY_PATH(elementrow, '|')), '|'), '|', ','||chr(10)) as elementrecord
	case when rn=1 then '' else ',' end as rowdelimiter, 
	elementrow 
from ( SELECT rownum rn, elementrow
FROM (SELECT 
	'      {' ||chr(10)|| 
	'      "Domain": "Aquifer Context(AquiferCodeContext)",' ||chr(10)||
	'      "UID": "'||aqfrctx_uid||'",' ||chr(10)||
	'      "Context": "'|| replace(replace(translate(aqfrctx_cd,'"\'||chr(10),'^/|'),'^','\"'),'/','\')||'",' ||chr(10)||
	'      "Name": "'|| replace(replace(translate(aqfrctx_name,'"\'||chr(10),'^/|'),'^','\"'),'/','\')||'",' ||chr(10)||
	'      "Description": "'|| replace(replace(translate(aqfrctx_desc,'"\'||chr(10),'^/|'),'^','\"'),'/','\')||'",' ||chr(10)||
	'      "LastChangeDate": "'||to_char(cast(aqfrctx_last_change_date as timestamp with LOCAL time zone), 'YYYY-MM-DD"T"HH24:MI:SS TZR') ||'"'||chr(10)|| 
	'      }' 
 as elementrow 
from wqx.Aquifer_context 
where org_uid is null 
) 
) 
--START WITH rn = 1 
--CONNECT BY rn = PRIOR rn + 1 
/ 
--footerinfo: 
select 
'            ]' ||chr(10)||
'	}' ||chr(10)||
'}' 
from dual;
spool off

--
spool NationalAquifer.json
--headerinfo: 
select	
    '{' ||chr(10)||
	'    "root": {' ||chr(10)||
	'      "Aquifer Context": [' 
--	'  "WQXEntity": "' ||chr(10)||
--	'    "WQXElement": " domainEntity="National Aquifer" schemaElement="NationalAquifer" 
--rowCount="'||(select count(*) from wqx.National_Aquifer)||'" maxLastChangeDate="'||to_char(cast((select 
--max(nataqfr_last_change_date) from wqx.National_Aquifer) as timestamp with LOCAL time zone), 
--'YYYY-MM-DD"T"HH24:MI:SSTZR')||'" }'
from dual;
SELECT 
--SELECT replace( LTRIM(MAX(SYS_CONNECT_BY_PATH(elementrow, '|')), '|'), '|', ','||chr(10)) as elementrecord
	case when rn=1 then '' else ',' end as rowdelimiter, 
	elementrow 
from ( SELECT rownum rn, elementrow
FROM (SELECT 
	'      {' ||chr(10)|| 
	'      "Domain": "National Aquifer(NationalAquiferCode)",' ||chr(10)||
	'      "UID": "'||nataqfr_uid||'",' ||chr(10)||
	'      "Code": "'||nataqfr_cd||'",' ||chr(10)||
	'      "Name": "'|| replace(replace(translate(nataqfr_name,'"\'||chr(10),'^/|'),'^','\"'),'/','\')||'",' ||chr(10)||
	'      "Description": "'|| replace(replace(translate(nataqfr_desc,'"\'||chr(10),'^/|'),'^','\"'),'/','\')||'",' ||chr(10)||
	'      "LastChangeDate": "'||to_char(cast(nataqfr_last_change_date as timestamp with LOCAL time zone), 'YYYY-MM-DD"T"HH24:MI:SS TZR') ||'"'||chr(10)|| 
	'      }' 
 as elementrow 
from wqx.National_Aquifer 
order by nataqfr_cd
) 
) 
--START WITH rn = 1 
--CONNECT BY rn = PRIOR rn + 1 
/ 
--footerinfo: 
select 
'            ]' ||chr(10)||
'	}' ||chr(10)||
'}' 
from dual;
spool off

--
spool Assemblage.json
--headerinfo: 
select	
    '{' ||chr(10)||
	'    "root": {' ||chr(10)||
	'      "Assemblage": [' 
--	'  "WQXEntity": "' ||chr(10)||
--	'    "WQXElement": " domainEntity="Assemblage" schemaElement="AssemblageSampledName" rowCount="'||(select 
--count(*) from wqx.assemblage)||'" maxLastChangeDate="'||to_char(cast((select max(asmblg_last_change_date) 
--from wqx.assemblage) as timestamp with LOCAL time zone), 'YYYY-MM-DD"T"HH24:MI:SSTZR')||'" }'
from dual;
SELECT 
--SELECT replace( LTRIM(MAX(SYS_CONNECT_BY_PATH(elementrow, '|')), '|'), '|', ','||chr(10)) as elementrecord
	case when rn=1 then '' else ',' end as rowdelimiter, 
	elementrow 
from ( SELECT rownum rn, elementrow
FROM (SELECT 
	'      {' ||chr(10)|| 
	'      "Domain": "Assemblage(AssemblageSampledName)",' ||chr(10)||
	'      "UID": "'||asmblg_uid||'",' ||chr(10)||
	'      "Name": "'|| replace(replace(translate(asmblg_name,'"\'||chr(10),'^/|'),'^','\"'),'/','\')||'",' ||chr(10)||
	'      "Description": "'|| replace(replace(translate(asmblg_desc,'"\'||chr(10),'^/|'),'^','\"'),'/','\')||'",' ||chr(10)||
	'      "LastChangeDate": "'||to_char(cast(asmblg_last_change_date as timestamp with LOCAL time zone), 'YYYY-MM-DD"T"HH24:MI:SS TZR') ||'"'||chr(10)|| 
	'      }' 
 as elementrow 
from wqx.assemblage 
order by asmblg_name
) 
) 
--START WITH rn = 1 
--CONNECT BY rn = PRIOR rn + 1 
/ 
--footerinfo: 
select 
'            ]' ||chr(10)||
'	}' ||chr(10)||
'}' 
from dual;
spool off

--
spool BiologicalIntent.json
--headerinfo: 
select	
    '{' ||chr(10)||
	'    "root": {' ||chr(10)||
	'      "Biological Intent": [' 
--	'  "WQXEntity": "' ||chr(10)||
--	'    "WQXElement": " domainEntity="Biological Intent" schemaElement="BiologicalIntentName" 
--rowCount="'||(select count(*) from wqx.biological_intent)||'" maxLastChangeDate="'||to_char(cast((select 
--max(bioint_last_change_date) from wqx.biological_intent) as timestamp with LOCAL time zone), 
--'YYYY-MM-DD"T"HH24:MI:SSTZR')||'" }'
from dual;
SELECT 
--SELECT replace( LTRIM(MAX(SYS_CONNECT_BY_PATH(elementrow, '|')), '|'), '|', ','||chr(10)) as elementrecord
	case when rn=1 then '' else ',' end as rowdelimiter, 
	elementrow 
from ( SELECT rownum rn, elementrow
FROM (SELECT 
	'      {' ||chr(10)|| 
	'      "Domain": "Biological Intent(BiologicalIntentName)",' ||chr(10)||
	'      "UID": "'||bioint_uid||'",' ||chr(10)||
	'      "Name": "'|| replace(replace(translate(bioint_name,'"\'||chr(10),'^/|'),'^','\"'),'/','\')||'",' ||chr(10)||
	'      "Description": "'|| replace(replace(translate(bioint_desc,'"\'||chr(10),'^/|'),'^','\"'),'/','\')||'",' ||chr(10)||
	'      "LastChangeDate": "'||to_char(cast(bioint_last_change_date as timestamp with LOCAL time zone), 'YYYY-MM-DD"T"HH24:MI:SS TZR') ||'"'||chr(10)|| 
	'      }' 
 as elementrow 
from wqx.biological_intent 
order by bioint_name
) 
) 
--START WITH rn = 1 
--CONNECT BY rn = PRIOR rn + 1 
/ 
--footerinfo: 
select 
'            ]' ||chr(10)||
'	}' ||chr(10)||
'}' 
from dual;
spool off

--
spool CellForm.json
--headerinfo: 
select	
    '{' ||chr(10)||
	'    "root": {' ||chr(10)||
	'      "Cell Form": [' 
--	'  "WQXEntity": "' ||chr(10)||
--	'    "WQXElement": " domainEntity="Cell Form" schemaElement="CellFormName" rowCount="'||(select 
--count(*) from wqx.cell_form)||'" maxLastChangeDate="'||to_char(cast((select max(celfrm_last_change_date) 
--from wqx.cell_form) as timestamp with LOCAL time zone), 'YYYY-MM-DD"T"HH24:MI:SSTZR')||'" }'
from dual;
SELECT 
--SELECT replace( LTRIM(MAX(SYS_CONNECT_BY_PATH(elementrow, '|')), '|'), '|', ','||chr(10)) as elementrecord
	case when rn=1 then '' else ',' end as rowdelimiter, 
	elementrow 
from ( SELECT rownum rn, elementrow
FROM (SELECT 
	'      {' ||chr(10)|| 
	'      "Domain": "Cell Form(CellFormName)",' ||chr(10)||
	'      "UID": "'||celfrm_uid||'",' ||chr(10)||
	'      "Name": "'|| replace(replace(translate(celfrm_name,'"\'||chr(10),'^/|'),'^','\"'),'/','\')||'",' ||chr(10)||
	'      "Description": "'|| replace(replace(translate(celfrm_desc,'"\'||chr(10),'^/|'),'^','\"'),'/','\')||'",' ||chr(10)||
	'      "LastChangeDate": "'||to_char(cast(celfrm_last_change_date as timestamp with LOCAL time zone), 'YYYY-MM-DD"T"HH24:MI:SS TZR') ||'"'||chr(10)|| 
	'      }' 
 as elementrow 
from wqx.cell_form 
order by celfrm_name
) 
) 
--START WITH rn = 1 
--CONNECT BY rn = PRIOR rn + 1 
/ 
--footerinfo: 
select 
'            ]' ||chr(10)||
'	}' ||chr(10)||
'}' 
from dual;
spool off

--
spool CellShape.json
--headerinfo: 
select	
    '{' ||chr(10)||
	'    "root": {' ||chr(10)||
	'      "Cell Shape": [' 
--	'  "WQXEntity": "' ||chr(10)||
--	'    "WQXElement": " domainEntity="Cell Shape" schemaElement="CellShapeName" rowCount="'||(select 
--count(*) from wqx.cell_shape)||'" maxLastChangeDate="'||to_char(cast((select max(celshp_last_change_date) 
--from wqx.cell_shape) as timestamp with LOCAL time zone), 'YYYY-MM-DD"T"HH24:MI:SSTZR')||'" }'
from dual;
SELECT 
--SELECT replace( LTRIM(MAX(SYS_CONNECT_BY_PATH(elementrow, '|')), '|'), '|', ','||chr(10)) as elementrecord
	case when rn=1 then '' else ',' end as rowdelimiter, 
	elementrow 
from ( SELECT rownum rn, elementrow
FROM (SELECT 
	'      {' ||chr(10)|| 
	'      "Domain": "Cell Shape(CellShapeName)",' ||chr(10)||
	'      "UID": "'||celshp_uid||'",' ||chr(10)||
	'      "Name": "'|| replace(replace(translate(celshp_name,'"\'||chr(10),'^/|'),'^','\"'),'/','\')||'",' ||chr(10)||
	'      "Description": "'|| replace(replace(translate(celshp_desc,'"\'||chr(10),'^/|'),'^','\"'),'/','\')||'",' ||chr(10)||
	'      "LastChangeDate": "'||to_char(cast(celshp_last_change_date as timestamp with LOCAL time zone), 'YYYY-MM-DD"T"HH24:MI:SS TZR') ||'"'||chr(10)|| 
	'      }' 
 as elementrow 
from wqx.cell_shape 
order by celshp_name
) 
) 
--START WITH rn = 1 
--CONNECT BY rn = PRIOR rn + 1 
/ 
--footerinfo: 
select 
'            ]' ||chr(10)||
'	}' ||chr(10)||
'}' 
from dual;
spool off

--
spool Characteristic.json
--headerinfo: 
select	
    '{' ||chr(10)||
	'    "root": {' ||chr(10)||
	'      "Characteristic": [' 
--	'  "WQXEntity": "' ||chr(10)||
--	'    "WQXElement": " domainEntity="Characteristic" schemaElement="CharacteristicName/Name" 
--rowCount="'||(select count(*) from wqx.characteristic)||'" maxLastChangeDate="'||to_char(cast((select 
--max(chr_last_change_date) from wqx.characteristic) as timestamp with LOCAL time zone), 
--'YYYY-MM-DD"T"HH24:MI:SSTZR')||'" }'
from dual;
SELECT 
--SELECT replace( LTRIM(MAX(SYS_CONNECT_BY_PATH(elementrow, '|')), '|'), '|', ','||chr(10)) as elementrecord
	case when rn=1 then '' else ',' end as rowdelimiter, 
	elementrow 
from ( SELECT rownum rn, elementrow
FROM (SELECT 
	'      {' ||chr(10)|| 
	'      "Domain": "Characteristic(CharacteristicName)",' ||chr(10)||
	'      "UID": "'||chr_uid||'",' ||chr(10)||
	'      "Name": "'|| replace(replace(translate(chr_name,'"\'||chr(10),'^/|'),'^','\"'),'/','\')||'",' ||chr(10)||
	'      "SRS_ID": "'||chr_srs_id||'",' ||chr(10)||
	'      "STORET_ID": "'||chr_storet_id||'",' ||chr(10)||
	'      "CAS_NUMBER": "'||chr_cas_number||'",' ||chr(10)||
	'      "SampleFractionRequiredYN": "'||chr_sample_fraction_req_yn||'",' ||chr(10)||
	'      "PickListYN": "'||chr_pick_list_yn||'",' ||chr(10)||
	'      "SpeciationRequiredYN": "'||chr_method_speciation_req_yn||'",' ||chr(10)||
	'      "AnalyticalMethodRequiredYN": "'||chr_analytical_method_req_yn||'",' ||chr(10)||
	'      "GroupName": "'|| replace(replace(translate(chrgrp_name,'"\'||chr(10),'^/|'),'^','\"'),'/','\')||'",' ||chr(10)||
	'      "ComparableName": "'|| replace(replace(translate(chr_name_comparable,'"\'||chr(10),'^/|'),'^','\"'),'/','\')||'",' ||chr(10)||
	'      "Status": "'||dvsta_name||'",' ||chr(10)||
	'      "Description": "'|| replace(replace(translate(chr_description,'"\'||chr(10),'^/|'),'^','\"'),'/','\')||'",' ||chr(10)||
	'      "LastChangeDate": "'||to_char(cast(chr_last_change_date as timestamp with LOCAL time zone), 'YYYY-MM-DD"T"HH24:MI:SS TZR') ||'"'||chr(10)|| 
	'      }' 
 as elementrow 
from wqx.characteristic c, wqx.characteristic_group cg, wqx.domain_value_status dvs
where c.chrgrp_uid=cg.chrgrp_uid and c.dvsta_uid=dvs.dvsta_uid
order by chr_name
) 
) 
--START WITH rn = 1 
--CONNECT BY rn = PRIOR rn + 1 
/ 
--footerinfo: 
select 
'            ]' ||chr(10)||
'	}' ||chr(10)||
'}' 
from dual;
spool off

--
spool CharacteristicAlias.json
--headerinfo: 
select	
    '{' ||chr(10)||
	'    "root": {' ||chr(10)||
	'      "Characteristic Alias": [' 
--	'  "WQXEntity": "' ||chr(10)||
--	'    "WQXElement": " domainEntity="Characteristic Alias" schemaElement="CharacteristicAliasName/Name" 
--rowCount="'||(select count(*) from wqx.characteristic_alias)||'" maxLastChangeDate="'||to_char(cast((select 
--max(chrals_last_change_date) from wqx.characteristic_alias) as timestamp with LOCAL time zone), 
--'YYYY-MM-DD"T"HH24:MI:SSTZR')||'" }'
from dual;
SELECT 
--SELECT replace( LTRIM(MAX(SYS_CONNECT_BY_PATH(elementrow, '|')), '|'), '|', ','||chr(10)) as elementrecord
	case when rn=1 then '' else ',' end as rowdelimiter, 
	elementrow 
from ( SELECT rownum rn, elementrow
FROM (SELECT 
	'      {' ||chr(10)|| 
	'      "Domain": "Characteristic Alias(CharacteristicAliasName)",' ||chr(10)||
	'      "UID": "'||chrals_uid||'",' ||chr(10)||
	'      "AliasType": "'|| replace(replace(translate(alstyp_name,'"\'||chr(10),'^/|'),'^','\"'),'/','\')||'",' ||chr(10)||
	'      "AliasName": "'|| replace(replace(translate(chrals_name,'"\'||chr(10),'^/|'),'^','\"'),'/','\')||'",' ||chr(10)||
	'      "CharacteristicName": "'|| replace(replace(translate(chr_name,'"\'||chr(10),'^/|'),'^','\"'),'/','\')||'",' ||chr(10)||
	'      "Description": "'|| replace(replace(translate(chrals_description,'"\'||chr(10),'^/|'),'^','\"'),'/','\')||'",' ||chr(10)||
	'      "LastChangeDate": "'||to_char(cast(chrals_last_change_date as timestamp with LOCAL time zone), 'YYYY-MM-DD"T"HH24:MI:SS TZR') ||'"'||chr(10)|| 
	'      }' 
 as elementrow 
from wqx.characteristic_alias ca, wqx.characteristic c, wqx.alias_type at
where ca.alstyp_uid=at.alstyp_uid and ca.chr_uid=c.chr_uid
order by alstyp_name, chrals_name
) 
) 
--START WITH rn = 1 
--CONNECT BY rn = PRIOR rn + 1 
/ 
--footerinfo: 
select 
'            ]' ||chr(10)||
'	}' ||chr(10)||
'}' 
from dual;
spool off

--
spool CharacteristicGroup.json
--headerinfo: 
select	
    '{' ||chr(10)||
	'    "root": {' ||chr(10)||
	'      "Characteristic Group": [' 
--	'  "WQXEntity": "' ||chr(10)||
--	'    "WQXElement": " domainEntity="Characteristic Group" schemaElement="CharacteristicGroupName/Name" 
--rowCount="'||(select count(*) from wqx.characteristic_group)||'" maxLastChangeDate="'||to_char(cast((select 
--max(chrgrp_last_change_date) from wqx.characteristic_group) as timestamp with LOCAL time zone), 
--'YYYY-MM-DD"T"HH24:MI:SSTZR')||'" }'
from dual;
SELECT 
--SELECT replace( LTRIM(MAX(SYS_CONNECT_BY_PATH(elementrow, '|')), '|'), '|', ','||chr(10)) as elementrecord
	case when rn=1 then '' else ',' end as rowdelimiter, 
	elementrow 
from ( SELECT rownum rn, elementrow
FROM (SELECT 
	'      {' ||chr(10)|| 
	'      "Domain": "Characteristic Group(CharacteristicGroupName)",' ||chr(10)||
	'      "UID": "'||chrgrp_uid||'",' ||chr(10)||
	'      "Name": "'|| replace(replace(translate(chrgrp_name,'"\'||chr(10),'^/|'),'^','\"'),'/','\')||'",' ||chr(10)||
	'      "Description": "'|| replace(replace(translate(chrgrp_desc,'"\'||chr(10),'^/|'),'^','\"'),'/','\')||'",' ||chr(10)||
	'      "LastChangeDate": "'||to_char(cast(chrgrp_last_change_date as timestamp with LOCAL time zone), 'YYYY-MM-DD"T"HH24:MI:SS TZR') ||'"'||chr(10)|| 
	'      }' 
 as elementrow 
from wqx.characteristic_group 
order by chrgrp_name
) 
) 
--START WITH rn = 1 
--CONNECT BY rn = PRIOR rn + 1 
/ 
--footerinfo: 
select 
'            ]' ||chr(10)||
'	}' ||chr(10)||
'}' 
from dual;
spool off

--
spool CharacteristicPickList.json
--headerinfo: 
select	
    '{' ||chr(10)||
	'    "root": {' ||chr(10)||
	'      "Characteristic Pick List": [' 
--	'  "WQXEntity": "' ||chr(10)||
--	'    "WQXElement": " domainEntity="Characteristic Pick List" 
--schemaElement="CharacteristicName" rowCount="'||(select count(*) from 
--wqx.characteristic_pick_list)||'" maxLastChangeDate="'||to_char(cast((select max(cplval_last_change_date) 
--from wqx.characteristic_pick_list) as timestamp with LOCAL time zone), 'YYYY-MM-DD"T"HH24:MI:SSTZR')||'" }'
from dual;
SELECT 
--SELECT replace( LTRIM(MAX(SYS_CONNECT_BY_PATH(elementrow, '|')), '|'), '|', ','||chr(10)) as elementrecord
	case when rn=1 then '' else ',' end as rowdelimiter, 
	elementrow 
from ( SELECT rownum rn, elementrow
FROM (SELECT 
	'      {' ||chr(10)|| 
	'      "Domain": "Characteristic PickList(CharacteristicName)",' ||chr(10)||
	'      "UID": "'||cplval_uid||'",' ||chr(10)||
	'      "Name": "'|| replace(replace(translate(chr_name,'"\'||chr(10),'^/|'),'^','\"'),'/','\')||'",' ||chr(10)||
	'      "Code": "'|| replace(replace(translate(cplval_cd,'"\'||chr(10),'^/|'),'^','\"'),'/','\')||'",' ||chr(10)||
	'      "Description": "'|| replace(replace(translate(cplval_desc,'"\'||chr(10),'^/|'),'^','\"'),'/','\')||'",' ||chr(10)||
	'      "LastChangeDate": "'||to_char(cast(cplval_last_change_date as timestamp with LOCAL time zone), 'YYYY-MM-DD"T"HH24:MI:SS TZR') ||'"'||chr(10)|| 
	'      }' 
 as elementrow 
from wqx.characteristic_pick_list_value cpl, wqx.characteristic c
where c.chr_uid=cpl.chr_uid
order by chr_name, cplval_cd
) 
) 
--START WITH rn = 1 
--CONNECT BY rn = PRIOR rn + 1 
/ 
--footerinfo: 
select 
'            ]' ||chr(10)||
'	}' ||chr(10)||
'}' 
from dual;
spool off

--
spool Citation.json
--headerinfo: 
select	
    '{' ||chr(10)||
	'    "root": {' ||chr(10)||
	'      "Citation": [' 
--	'  "WQXEntity": "' ||chr(10)||
--	'    "WQXElement": " domainEntity="Citation" schemaElement="TaxonomicDetailsCitation/ResourceIdentifier" rowCount="'||(select 
--count(*) from wqx.citation)||'" maxLastChangeDate="'||to_char(cast((select max(citatn_last_change_date) 
--from wqx.citation) as timestamp with LOCAL time zone), 'YYYY-MM-DD"T"HH24:MI:SSTZR')||'" }'
from dual;
SELECT 
--SELECT replace( LTRIM(MAX(SYS_CONNECT_BY_PATH(elementrow, '|')), '|'), '|', ','||chr(10)) as elementrecord
	case when rn=1 then '' else ',' end as rowdelimiter, 
	elementrow 
from ( SELECT rownum rn, elementrow
FROM (SELECT 
	'      {' ||chr(10)|| 
	'      "Domain": "Citation(ResourceIdentifier)",' ||chr(10)||
	'      "UID": "'||citatn_uid||'",' ||chr(10)||
	'      "Identifier": "'|| replace(replace(translate(citatn_id,'"\'||chr(10),'^/|'),'^','\"'),'/','\')||'",' ||chr(10)||
	'      "Creator": "'|| replace(replace(translate(citatn_creator,'"\'||chr(10),'^/|'),'^','\"'),'/','\')||'",' ||chr(10)||
	'      "Date": "'||citatn_date||'",' ||chr(10)||
	'      "Publisher": "'|| replace(replace(translate(citatn_publisher,'"\'||chr(10),'^/|'),'^','\"'),'/','\')||'",' ||chr(10)||
	'      "Subject": "'||substr(replace(replace(translate(citatn_subject,'"\'||chr(10),'^/|'),'^','\"'),'/','\'),1,3600)||'",' ||chr(10)||
	'      "Title": "'|| replace(replace(translate(citatn_title,'"\'||chr(10),'^/|'),'^','\"'),'/','\')||'",' ||chr(10)||
	'      "LastChangeDate": "'||to_char(cast(citatn_last_change_date as timestamp with LOCAL time zone), 'YYYY-MM-DD"T"HH24:MI:SS TZR') ||'"'||chr(10)|| 
	'      }' 
 as elementrow 
from wqx.citation 
--where org_uid is null
order by citatn_id
) 
) 
--START WITH rn = 1 
--CONNECT BY rn = PRIOR rn + 1 
/ 
--footerinfo: 
select 
'            ]' ||chr(10)||
'	}' ||chr(10)||
'}' 
from dual;
spool off

--
spool ContainerColor.json
--headerinfo: 
select	
    '{' ||chr(10)||
	'    "root": {' ||chr(10)||
	'      "Container Color": [' 
--	'  "WQXEntity": "' ||chr(10)||
--	'    "WQXElement": " domainEntity="Container Color" schemaElement="SampleContainerColorName" 
--rowCount="'||(select count(*) from wqx.container_color)||'" maxLastChangeDate="'||to_char(cast((select 
--max(concol_last_change_date) from wqx.container_color) as timestamp with LOCAL time zone), 
--'YYYY-MM-DD"T"HH24:MI:SSTZR')||'" }'
from dual;
SELECT 
--SELECT replace( LTRIM(MAX(SYS_CONNECT_BY_PATH(elementrow, '|')), '|'), '|', ','||chr(10)) as elementrecord
	case when rn=1 then '' else ',' end as rowdelimiter, 
	elementrow 
from ( SELECT rownum rn, elementrow
FROM (SELECT 
	'      {' ||chr(10)|| 
	'      "Domain": "Container Color(SampleContainerColorName)",' ||chr(10)||
	'      "UID": "'||concol_uid||'",' ||chr(10)||
	'      "Name": "'|| replace(replace(translate(concol_name,'"\'||chr(10),'^/|'),'^','\"'),'/','\')||'",' ||chr(10)||
	'      "Description": "'|| replace(replace(translate(concol_desc,'"\'||chr(10),'^/|'),'^','\"'),'/','\')||'",' ||chr(10)||
	'      "LastChangeDate": "'||to_char(cast(concol_last_change_date as timestamp with LOCAL time zone), 'YYYY-MM-DD"T"HH24:MI:SS TZR') ||'"'||chr(10)|| 
	'      }' 
 as elementrow 
from wqx.container_color 
order by concol_name
) 
) 
--START WITH rn = 1 
--CONNECT BY rn = PRIOR rn + 1 
/ 
--footerinfo: 
select 
'            ]' ||chr(10)||
'	}' ||chr(10)||
'}' 
from dual;
spool off

--
spool ContainerType.json
--headerinfo: 
select	
    '{' ||chr(10)||
	'    "root": {' ||chr(10)||
	'      "Container Type": [' 
--	'  "WQXEntity": "' ||chr(10)||
--	'    "WQXElement": " domainEntity="Container Type" schemaElement="SampleContainerTypeName" 
--rowCount="'||(select count(*) from wqx.container_type)||'" maxLastChangeDate="'||to_char(cast((select 
--max(contyp_last_change_date) from wqx.container_type) as timestamp with LOCAL time zone), 
--'YYYY-MM-DD"T"HH24:MI:SSTZR')||'" }'
from dual;
SELECT 
--SELECT replace( LTRIM(MAX(SYS_CONNECT_BY_PATH(elementrow, '|')), '|'), '|', ','||chr(10)) as elementrecord
	case when rn=1 then '' else ',' end as rowdelimiter, 
	elementrow 
from ( SELECT rownum rn, elementrow
FROM (SELECT 
	'      {' ||chr(10)|| 
	'      "Domain": "Container Type(SampleContainerTypeName)",' ||chr(10)||
	'      "UID": "'||contyp_uid||'",' ||chr(10)||
	'      "Name": "'|| replace(replace(translate(contyp_name,'"\'||chr(10),'^/|'),'^','\"'),'/','\')||'",' ||chr(10)||
	'      "Description": "'|| replace(replace(translate(contyp_desc,'"\'||chr(10),'^/|'),'^','\"'),'/','\')||'",' ||chr(10)||
	'      "LastChangeDate": "'||to_char(cast(contyp_last_change_date as timestamp with LOCAL time zone), 'YYYY-MM-DD"T"HH24:MI:SS TZR') ||'"'||chr(10)|| 
	'      }' 
 as elementrow 
from wqx.container_type 
order by contyp_name
) 
) 
--START WITH rn = 1 
--CONNECT BY rn = PRIOR rn + 1 
/ 
--footerinfo: 
select 
'            ]' ||chr(10)||
'	}' ||chr(10)||
'}' 
from dual;
spool off

--
spool Country.json
--headerinfo: 
select	
    '{' ||chr(10)||
	'    "root": {' ||chr(10)||
	'      "Country": [' 
--	'  "WQXEntity": "' ||chr(10)||
--	'    "WQXElement": " domainEntity="Country" schemaElement="CountryCode" rowCount="'||(select 
--count(*) from wqx.country)||'" maxLastChangeDate="'||to_char(cast((select max(cntry_last_change_date) from 
--wqx.country) as timestamp with LOCAL time zone), 'YYYY-MM-DD"T"HH24:MI:SSTZR')||'" }'
from dual;
SELECT 
--SELECT replace( LTRIM(MAX(SYS_CONNECT_BY_PATH(elementrow, '|')), '|'), '|', ','||chr(10)) as elementrecord
	case when rn=1 then '' else ',' end as rowdelimiter, 
	elementrow 
from ( SELECT rownum rn, elementrow
FROM (SELECT 
	'      {' ||chr(10)|| 
	'      "Domain": "Country(CountryCode)",' ||chr(10)||
	'      "UID": "'||cntry_uid||'",' ||chr(10)||
	'      "Code": "'|| replace(replace(translate(cntry_cd,'"\'||chr(10),'^/|'),'^','\"'),'/','\')||'",' ||chr(10)||
	'      "Name": "'|| replace(replace(translate(cntry_name,'"\'||chr(10),'^/|'),'^','\"'),'/','\')||'",' ||chr(10)||
	'      "LastChangeDate": "'||to_char(cast(cntry_last_change_date as timestamp with LOCAL time zone), 'YYYY-MM-DD"T"HH24:MI:SS TZR') ||'"'||chr(10)|| 
	'      }' 
 as elementrow 
from wqx.country 
order by cntry_name
) 
) 
--START WITH rn = 1 
--CONNECT BY rn = PRIOR rn + 1 
/ 
--footerinfo: 
select 
'            ]' ||chr(10)||
'	}' ||chr(10)||
'}' 
from dual;
spool off

--
spool County.json
--headerinfo: 
select	
    '{' ||chr(10)||
	'    "root": {' ||chr(10)||
	'      "County": [' 
--	'  "WQXEntity": "' ||chr(10)||
--	'    "WQXElement": " domainEntity="County" schemaElement="CountyCode" rowCount="'||(select count(*) 
--from wqx.county)||'" maxLastChangeDate="'||to_char(cast((select max(cnty_last_change_date) from wqx.county) 
--as timestamp with LOCAL time zone), 'YYYY-MM-DD"T"HH24:MI:SSTZR')||'" }'
from dual;
SELECT 
--SELECT replace( LTRIM(MAX(SYS_CONNECT_BY_PATH(elementrow, '|')), '|'), '|', ','||chr(10)) as elementrecord
	case when rn=1 then '' else ',' end as rowdelimiter, 
	elementrow 
from ( SELECT rownum rn, elementrow
FROM (SELECT 
	'      {' ||chr(10)|| 
	'      "Domain": "County(CountyCode)",' ||chr(10)||
	'      "UID": "'||cnty_uid||'",' ||chr(10)||
	'      "State": "'|| replace(replace(translate(st_name,'"\'||chr(10),'^/|'),'^','\"'),'/','\')||'",' ||chr(10)||
	'      "Name": "'|| replace(replace(translate(cnty_name,'"\'||chr(10),'^/|'),'^','\"'),'/','\')||'",' ||chr(10)||
	'      "StateCode": "'|| replace(replace(translate(lpad(st_cd, 2, '0'),'"\'||chr(10),'^/|'),'^','\"'),'/','\')||'",' ||chr(10)||
	'      "FIPSCountyCode": "'|| replace(replace(translate(lpad(cnty_fips_cd, 3, '0'),'"\'||chr(10),'^/|'),'^','\"'),'/','\')||'",' ||chr(10)||
	'      "LastChangeDate": "'||to_char(cast(cnty_last_change_date as timestamp with LOCAL time zone), 'YYYY-MM-DD"T"HH24:MI:SS TZR') ||'"'||chr(10)|| 
	'      }' 
 as elementrow 
from wqx.county c, wqx.state s 
where c.st_uid=s.st_uid
order by st_name, cnty_name
) 
) 
--START WITH rn = 1 
--CONNECT BY rn = PRIOR rn + 1 
/ 
--footerinfo: 
select 
'            ]' ||chr(10)||
'	}' ||chr(10)||
'}' 
from dual;
spool off

--
spool DetectionQuantationLimit.json
--headerinfo: 
select	
    '{' ||chr(10)||
	'    "root": {' ||chr(10)||
	'      "Detection Quantation Limit": [' 
--	'  "WQXEntity": "' ||chr(10)||
--	'    "WQXElement": " domainEntity="Detection Quantation Limit" 
--schemaElement="ResultDetectionQuantitationLimit/DetectionQuantitationLimitTypeName" rowCount="'||(select count(*) from 
--wqx.detection_quant_limit_type)||'" maxLastChangeDate="'||to_char(cast((select max(dqltyp_last_change_date) 
--from wqx.detection_quant_limit_type) as timestamp with LOCAL time zone), 'YYYY-MM-DD"T"HH24:MI:SSTZR')||'" }'
from dual;
SELECT 
--SELECT replace( LTRIM(MAX(SYS_CONNECT_BY_PATH(elementrow, '|')), '|'), '|', ','||chr(10)) as elementrecord
	case when rn=1 then '' else ',' end as rowdelimiter, 
	elementrow 
from ( SELECT rownum rn, elementrow
FROM (SELECT 
	'      {' ||chr(10)|| 
	'      "Domain": "Detection Quantation Limit(DetectionQuantitationLimitTypeName)",' ||chr(10)||
	'      "UID": "'||dqltyp_uid||'",' ||chr(10)||
	'      "Name": "'|| replace(replace(translate(dqltyp_name,'"\'||chr(10),'^/|'),'^','\"'),'/','\')||'",' ||chr(10)||
	'      "Description": "'|| replace(replace(translate(dqltyp_desc,'"\'||chr(10),'^/|'),'^','\"'),'/','\')||'",' ||chr(10)||
	'      "LastChangeDate": "'||to_char(cast(dqltyp_last_change_date as timestamp with LOCAL time zone), 'YYYY-MM-DD"T"HH24:MI:SS TZR') ||'"'||chr(10)|| 
	'      "HierarchySortOrder": "'||dqltyp_hierarchy_sort_order||'"'||chr(10)|| 
	'      }' 
 as elementrow 
from wqx.detection_quant_limit_type 
order by dqltyp_name
) 
) 
--START WITH rn = 1 
--CONNECT BY rn = PRIOR rn + 1 
/ 
--footerinfo: 
select 
'            ]' ||chr(10)||
'	}' ||chr(10)||
'}' 
from dual;
spool off

--
spool ElectronicAddressType.json
--headerinfo: 
select	
    '{' ||chr(10)||
	'    "root": {' ||chr(10)||
	'      "Electronic Address Type": [' 
--	'  "WQXEntity": "' ||chr(10)||
--	'    "WQXElement": " domainEntity="Electronic Address Type" 
--schemaElement="ElectronicAddressTypeName/Name" rowCount="'||(select count(*) from 
--wqx.electronic_address_type)||'" maxLastChangeDate="'||to_char(cast((select max(eatyp_last_change_date) 
--from wqx.electronic_address_type) as timestamp with LOCAL time zone), 'YYYY-MM-DD"T"HH24:MI:SSTZR')||'" }'
from dual;
SELECT 
--SELECT replace( LTRIM(MAX(SYS_CONNECT_BY_PATH(elementrow, '|')), '|'), '|', ','||chr(10)) as elementrecord
	case when rn=1 then '' else ',' end as rowdelimiter, 
	elementrow 
from ( SELECT rownum rn, elementrow
FROM (SELECT 
	'      {' ||chr(10)|| 
	'      "Domain": "Electronic Address Type(ElectronicAddressTypeName)",' ||chr(10)||
	'      "UID": "'||eatyp_uid||'",' ||chr(10)||
	'      "Name": "'|| replace(replace(translate(eatyp_name,'"\'||chr(10),'^/|'),'^','\"'),'/','\')||'",' ||chr(10)||
	'      "Description": "'|| replace(replace(translate(eatyp_desc,'"\'||chr(10),'^/|'),'^','\"'),'/','\')||'",' ||chr(10)||
	'      "LastChangeDate": "'||to_char(cast(eatyp_last_change_date as timestamp with LOCAL time zone), 'YYYY-MM-DD"T"HH24:MI:SS TZR') ||'"'||chr(10)|| 
	'      }' 
 as elementrow 
from wqx.electronic_address_type 
order by eatyp_name
) 
) 
--START WITH rn = 1 
--CONNECT BY rn = PRIOR rn + 1 
/ 
--footerinfo: 
select 
'            ]' ||chr(10)||
'	}' ||chr(10)||
'}' 
from dual;
spool off

--
spool FrequencyClassDescriptor.json
--headerinfo: 
select	
    '{' ||chr(10)||
	'    "root": {' ||chr(10)||
	'      "Frequency Class Descriptor": [' 
--	'  "WQXEntity": "' ||chr(10)||
--	'    "WQXElement": " domainEntity="Frequency Class Descriptor" 
--schemaElement="FrequencyClassInformation/FrequencyClassDescriptorCode" rowCount="'||(select count(*) from 
--wqx.frequency_class_descriptor)||'" maxLastChangeDate="'||to_char(cast((select max(fcdsc_last_change_date) 
--from wqx.frequency_class_descriptor) as timestamp with LOCAL time zone), 'YYYY-MM-DD"T"HH24:MI:SSTZR')||'" }'
from dual;
SELECT 
--SELECT replace( LTRIM(MAX(SYS_CONNECT_BY_PATH(elementrow, '|')), '|'), '|', ','||chr(10)) as elementrecord
	case when rn=1 then '' else ',' end as rowdelimiter, 
	elementrow 
from ( SELECT rownum rn, elementrow
FROM (SELECT 
	'      {' ||chr(10)|| 
	'      "Domain": "Frequency Class Descriptor(FrequencyClassDescriptorCode)",' ||chr(10)||
	'      "UID": "'||fcdsc_uid||'",' ||chr(10)||
	'      "Type": "'|| replace(replace(translate(fctyp_name,'"\'||chr(10),'^/|'),'^','\"'),'/','\')||'",' ||chr(10)||
	'      "Name": "'|| replace(replace(translate(fcdsc_name,'"\'||chr(10),'^/|'),'^','\"'),'/','\')||'",' ||chr(10)||
	'      "Description": "'|| replace(replace(translate(fcdsc_desc,'"\'||chr(10),'^/|'),'^','\"'),'/','\')||'",' ||chr(10)||
	'      "LastChangeDate": "'||to_char(cast(fcdsc_last_change_date as timestamp with LOCAL time zone), 'YYYY-MM-DD"T"HH24:MI:SS TZR') ||'"'||chr(10)|| 
	'      }' 
 as elementrow 
from wqx.frequency_class_descriptor fcd, wqx.frequency_class_type fct 
where fcd.fctyp_uid=fct.fctyp_uid
order by fcdsc_name
) 
) 
--START WITH rn = 1 
--CONNECT BY rn = PRIOR rn + 1 
/ 
--footerinfo: 
select 
'            ]' ||chr(10)||
'	}' ||chr(10)||
'}' 
from dual;
spool off

--
spool FrequencyClassType.json
--headerinfo: 
select	
    '{' ||chr(10)||
	'    "root": {' ||chr(10)||
	'      "Frequency Class Type": [' 
--	'  "WQXEntity": "' ||chr(10)||
--	'    "WQXElement": " domainEntity="Frequency Class Type" schemaElement="FrequencyClassTypeName" 
--rowCount="'||(select count(*) from wqx.frequency_class_type)||'" maxLastChangeDate="'||to_char(cast((select 
--max(fctyp_last_change_date) from wqx.frequency_class_type) as timestamp with LOCAL time zone), 
--'YYYY-MM-DD"T"HH24:MI:SSTZR')||'" }'
from dual;
SELECT 
--SELECT replace( LTRIM(MAX(SYS_CONNECT_BY_PATH(elementrow, '|')), '|'), '|', ','||chr(10)) as elementrecord
	case when rn=1 then '' else ',' end as rowdelimiter, 
	elementrow 
from ( SELECT rownum rn, elementrow
FROM (SELECT 
	'      {' ||chr(10)|| 
	'      "Domain": "Frequency Class Type(FrequencyClassTypeName)",' ||chr(10)||
	'      "UID": "'||fctyp_uid||'",' ||chr(10)||
	'      "Name": "'|| replace(replace(translate(fctyp_name,'"\'||chr(10),'^/|'),'^','\"'),'/','\')||'",' ||chr(10)||
	'      "UnitRequiredYN": "'||fctyp_unit_required_yn||'",' ||chr(10)||
	'      "BoundsRequiredYN": "'||fctyp_bounds_required_yn||'",' ||chr(10)||
	'      "LastChangeDate": "'||to_char(cast(fctyp_last_change_date as timestamp with LOCAL time zone), 'YYYY-MM-DD"T"HH24:MI:SS TZR') ||'"'||chr(10)|| 
	'      }' 
 as elementrow 
from wqx.frequency_class_type 
order by fctyp_name
) 
) 
--START WITH rn = 1 
--CONNECT BY rn = PRIOR rn + 1 
/ 
--footerinfo: 
select 
'            ]' ||chr(10)||
'	}' ||chr(10)||
'}' 
from dual;
spool off

--
spool GearProcedureUnit.json
--headerinfo: 
select	
    '{' ||chr(10)||
	'    "root": {' ||chr(10)||
	'      "Gear Procedure Unit": [' 
--	'  "WQXEntity": "' ||chr(10)||
--	'    "WQXElement": " domainEntity="Gear Procedure Unit" schemaElement="CollectionEffort/GearProcedureUnitCode" 
--rowCount="'||(select count(*) from wqx.gear_procedure_unit)||'" maxLastChangeDate="'||to_char(cast((select 
--max(gpunt_last_change_date) from wqx.gear_procedure_unit) as timestamp with LOCAL time zone), 
--'YYYY-MM-DD"T"HH24:MI:SSTZR')||'" }'
from dual;
SELECT 
--SELECT replace( LTRIM(MAX(SYS_CONNECT_BY_PATH(elementrow, '|')), '|'), '|', ','||chr(10)) as elementrecord
	case when rn=1 then '' else ',' end as rowdelimiter, 
	elementrow 
from ( SELECT rownum rn, elementrow
FROM (SELECT 
	'      {' ||chr(10)|| 
	'      "Domain": "Gear Procedure Unit(GearProcedureUnitCode)",' ||chr(10)||
	'      "UID": "'||gpunt_uid||'",' ||chr(10)||
	'      "Name": "'|| replace(replace(translate(gpunt_name,'"\'||chr(10),'^/|'),'^','\"'),'/','\')||'",' ||chr(10)||
	'      "Description": "'|| replace(replace(translate(gpunt_desc,'"\'||chr(10),'^/|'),'^','\"'),'/','\')||'",' ||chr(10)||
	'      "LastChangeDate": "'||to_char(cast(gpunt_last_change_date as timestamp with LOCAL time zone), 'YYYY-MM-DD"T"HH24:MI:SS TZR') ||'"'||chr(10)|| 
	'      }' 
 as elementrow 
from wqx.gear_procedure_unit 
order by gpunt_name
) 
) 
--START WITH rn = 1 
--CONNECT BY rn = PRIOR rn + 1 
/ 
--footerinfo: 
select 
'            ]' ||chr(10)||
'	}' ||chr(10)||
'}' 
from dual;
spool off

--
spool Habit.json
--headerinfo: 
select	
    '{' ||chr(10)||
	'    "root": {' ||chr(10)||
	'      "Habit": [' 
--	'  "WQXEntity": "' ||chr(10)||
--	'    "WQXElement": " domainEntity="Habit" schemaElement="HabitName" rowCount="'||(select count(*) 
--from wqx.habit)||'" maxLastChangeDate="'||to_char(cast((select max(habit_last_change_date) from wqx.habit) 
--as timestamp with LOCAL time zone), 'YYYY-MM-DD"T"HH24:MI:SSTZR')||'" }'
from dual;
SELECT 
--SELECT replace( LTRIM(MAX(SYS_CONNECT_BY_PATH(elementrow, '|')), '|'), '|', ','||chr(10)) as elementrecord
	case when rn=1 then '' else ',' end as rowdelimiter, 
	elementrow 
from ( SELECT rownum rn, elementrow
FROM (SELECT 
	'      {' ||chr(10)|| 
	'      "Domain": "Habit(HabitName)",' ||chr(10)||
	'      "UID": "'||habit_uid||'",' ||chr(10)||
	'      "Name": "'|| replace(replace(translate(habit_name,'"\'||chr(10),'^/|'),'^','\"'),'/','\')||'",' ||chr(10)||
	'      "Description": "'|| replace(replace(translate(habit_desc,'"\'||chr(10),'^/|'),'^','\"'),'/','\')||'",' ||chr(10)||
	'      "LastChangeDate": "'||to_char(cast(habit_last_change_date as timestamp with LOCAL time zone), 'YYYY-MM-DD"T"HH24:MI:SS TZR') ||'"'||chr(10)|| 
	'      }' 
 as elementrow 
from wqx.habit 
order by habit_name
) 
) 
--START WITH rn = 1 
--CONNECT BY rn = PRIOR rn + 1 
/ 
--footerinfo: 
select 
'            ]' ||chr(10)||
'	}' ||chr(10)||
'}' 
from dual;
spool off

--
spool HabitatSelectionMethod.json
--headerinfo: 
select	
    '{' ||chr(10)||
	'    "root": {' ||chr(10)||
	'      "Habitat Selection Method": [' 
--	'  "WQXEntity": "' ||chr(10)||
--	'    "WQXElement": " domainEntity="Habitat Selection Method" 
--schemaElement="HabitatSelectionMethodName" rowCount="'||(select count(*) from 
--wqx.habitat_selection_method)||'" maxLastChangeDate="'||to_char(cast((select max(hsm_last_change_date) from 
--wqx.habitat_selection_method) as timestamp with LOCAL time zone), 'YYYY-MM-DD"T"HH24:MI:SSTZR')||'" }'
from dual;
SELECT 
--SELECT replace( LTRIM(MAX(SYS_CONNECT_BY_PATH(elementrow, '|')), '|'), '|', ','||chr(10)) as elementrecord
	case when rn=1 then '' else ',' end as rowdelimiter, 
	elementrow 
from ( SELECT rownum rn, elementrow
FROM (SELECT 
	'      {' ||chr(10)|| 
	'      "Domain": "Habitat Selection Method(HabitatSelectionMethodName)",' ||chr(10)||
	'      "UID": "'||hsm_uid||'",' ||chr(10)||
	'      "Name": "'|| replace(replace(translate(hsm_name,'"\'||chr(10),'^/|'),'^','\"'),'/','\')||'",' ||chr(10)||
	'      "Description": "'|| replace(replace(translate(hsm_desc,'"\'||chr(10),'^/|'),'^','\"'),'/','\')||'",' ||chr(10)||
	'      "LastChangeDate": "'||to_char(cast(hsm_last_change_date as timestamp with LOCAL time zone), 'YYYY-MM-DD"T"HH24:MI:SS TZR') ||'"'||chr(10)|| 
	'      }' 
 as elementrow 
from wqx.habitat_selection_method 
order by hsm_name
) 
) 
--START WITH rn = 1 
--CONNECT BY rn = PRIOR rn + 1 
/ 
--footerinfo: 
select 
'            ]' ||chr(10)||
'	}' ||chr(10)||
'}' 
from dual;
spool off

--
spool HorizontalCollectionMethod.json
--headerinfo: 
select	
    '{' ||chr(10)||
	'    "root": {' ||chr(10)||
	'      "Horizontal Collection Method": [' 
--	'  "WQXEntity": "' ||chr(10)||
--	'    "WQXElement": " domainEntity="Horizontal Collection Method" 
--schemaElement="HorizontalCollectionMethodName" rowCount="'||(select count(*) from 
--wqx.horizontal_collection_method)||'" maxLastChangeDate="'||to_char(cast((select 
--max(hcmth_last_change_date) from wqx.horizontal_collection_method) as timestamp with LOCAL time zone), 
--'YYYY-MM-DD"T"HH24:MI:SSTZR')||'" }'
from dual;
SELECT 
--SELECT replace( LTRIM(MAX(SYS_CONNECT_BY_PATH(elementrow, '|')), '|'), '|', ','||chr(10)) as elementrecord
	case when rn=1 then '' else ',' end as rowdelimiter, 
	elementrow 
from ( SELECT rownum rn, elementrow
FROM (SELECT 
	'      {' ||chr(10)|| 
	'      "Domain": "Horizontal Collection Method(HorizontalCollectionMethodName)",' ||chr(10)||
	'      "UID": "'||hcmth_uid||'",' ||chr(10)||
	'      "Name": "'|| replace(replace(translate(hcmth_name,'"\'||chr(10),'^/|'),'^','\"'),'/','\')||'",' ||chr(10)||
	'      "Description": "'|| replace(replace(translate(hcmth_desc,'"\'||chr(10),'^/|'),'^','\"'),'/','\')||'",' ||chr(10)||
	'      "LastChangeDate": "'||to_char(cast(hcmth_last_change_date as timestamp with LOCAL time zone), 'YYYY-MM-DD"T"HH24:MI:SS TZR') ||'"'||chr(10)|| 
	'      }' 
 as elementrow 
from wqx.horizontal_collection_method 
order by hcmth_name
) 
) 
--START WITH rn = 1 
--CONNECT BY rn = PRIOR rn + 1 
/ 
--footerinfo: 
select 
'            ]' ||chr(10)||
'	}' ||chr(10)||
'}' 
from dual;
spool off

--
spool HorizontalReferenceDatum.json
--headerinfo: 
select	
    '{' ||chr(10)||
	'    "root": {' ||chr(10)||
	'      "Horizontal Reference Datum": [' 
--	'  "WQXEntity": "' ||chr(10)||
--	'    "WQXElement": " domainEntity="Horizontal Reference Datum" 
--schemaElement="HorizontalReferenceDatumName" rowCount="'||(select count(*) from 
--wqx.horizontal_reference_datum)||'" maxLastChangeDate="'||to_char(cast((select max(hrdat_last_change_date) 
--from wqx.horizontal_reference_datum) as timestamp with LOCAL time zone), 'YYYY-MM-DD"T"HH24:MI:SSTZR')||'" }'
from dual;
SELECT 
--SELECT replace( LTRIM(MAX(SYS_CONNECT_BY_PATH(elementrow, '|')), '|'), '|', ','||chr(10)) as elementrecord
	case when rn=1 then '' else ',' end as rowdelimiter, 
	elementrow 
from ( SELECT rownum rn, elementrow
FROM (SELECT 
	'      {' ||chr(10)|| 
	'      "Domain": "Horizontal Reference Datum(HorizontalReferenceDatumName)",' ||chr(10)||
	'      "UID": "'||hrdat_uid||'",' ||chr(10)||
	'      "Name": "'|| replace(replace(translate(hrdat_name,'"\'||chr(10),'^/|'),'^','\"'),'/','\')||'",' ||chr(10)||
	'      "Description": "'|| replace(replace(translate(hrdat_desc,'"\'||chr(10),'^/|'),'^','\"'),'/','\')||'",' ||chr(10)||
	'      "LastChangeDate": "'||to_char(cast(hrdat_last_change_date as timestamp with LOCAL time zone), 'YYYY-MM-DD"T"HH24:MI:SS TZR') ||'"'||chr(10)|| 
	'      }' 
 as elementrow 
from wqx.horizontal_reference_datum 
order by hrdat_name
) 
) 
--START WITH rn = 1 
--CONNECT BY rn = PRIOR rn + 1 
/ 
--footerinfo: 
select 
'            ]' ||chr(10)||
'	}' ||chr(10)||
'}' 
from dual;
spool off

--
spool HydrologicCondition.json
--headerinfo: 
select	
    '{' ||chr(10)||
	'    "root": {' ||chr(10)||
	'      "Hydrologic Condition": [' 
--	'  "WQXEntity": "' ||chr(10)||
--	'    "WQXElement": " domainEntity="Hydrologic Condition" schemaElement="HydrologicCondition" 
--rowCount="'||(select count(*) from wqx.hydrologic_condition)||'" maxLastChangeDate="'||to_char(cast((select 
--max(hydcnd_last_change_date) from wqx.hydrologic_condition) as timestamp with LOCAL time zone), 
--'YYYY-MM-DD"T"HH24:MI:SSTZR')||'" }'
from dual;
SELECT 
--SELECT replace( LTRIM(MAX(SYS_CONNECT_BY_PATH(elementrow, '|')), '|'), '|', ','||chr(10)) as elementrecord
	case when rn=1 then '' else ',' end as rowdelimiter, 
	elementrow 
from ( SELECT rownum rn, elementrow
FROM (SELECT 
	'      {' ||chr(10)|| 
	'      "Domain": "Hydrologic Condition(HydrologicCondition)",' ||chr(10)||
	'      "UID": "'||hydcnd_uid||'",' ||chr(10)||
	'      "Name": "'|| replace(replace(translate(hydcnd_name,'"\'||chr(10),'^/|'),'^','\"'),'/','\')||'",' ||chr(10)||
	'      "Description": "'|| replace(replace(translate(hydcnd_desc,'"\'||chr(10),'^/|'),'^','\"'),'/','\')||'",' ||chr(10)||
	'      "LastChangeDate": "'||to_char(cast(hydcnd_last_change_date as timestamp with LOCAL time zone), 'YYYY-MM-DD"T"HH24:MI:SS TZR') ||'"'||chr(10)|| 
	'      }' 
 as elementrow 
from wqx.hydrologic_condition 
order by hydcnd_name
) 
) 
--START WITH rn = 1 
--CONNECT BY rn = PRIOR rn + 1 
/ 
--footerinfo: 
select 
'            ]' ||chr(10)||
'	}' ||chr(10)||
'}' 
from dual;
spool off

--
spool HydrologicEvent.json
--headerinfo: 
select	
    '{' ||chr(10)||
	'    "root": {' ||chr(10)||
	'      "Hydrologic Event": [' 
--	'  "WQXEntity": "' ||chr(10)||
--	'    "WQXElement": " domainEntity="Hydrologic Event" schemaElement="HydrologicEventName/Name" 
--rowCount="'||(select count(*) from wqx.hydrologic_event)||'" maxLastChangeDate="'||to_char(cast((select 
--max(hydev_last_change_date) from wqx.hydrologic_event) as timestamp with LOCAL time zone), 
--'YYYY-MM-DD"T"HH24:MI:SSTZR')||'" }'
from dual;
SELECT 
--SELECT replace( LTRIM(MAX(SYS_CONNECT_BY_PATH(elementrow, '|')), '|'), '|', ','||chr(10)) as elementrecord
	case when rn=1 then '' else ',' end as rowdelimiter, 
	elementrow 
from ( SELECT rownum rn, elementrow
FROM (SELECT 
	'      {' ||chr(10)|| 
	'      "Domain": "Hydrologic Event(HydrologicEventName)",' ||chr(10)||
	'      "UID": "'||hydev_uid||'",' ||chr(10)||
	'      "Name": "'|| replace(replace(translate(hydev_name,'"\'||chr(10),'^/|'),'^','\"'),'/','\')||'",' ||chr(10)||
	'      "Description": "'|| replace(replace(translate(hydev_desc,'"\'||chr(10),'^/|'),'^','\"'),'/','\')||'",' ||chr(10)||
	'      "LastChangeDate": "'||to_char(cast(hydev_last_change_date as timestamp with LOCAL time zone), 'YYYY-MM-DD"T"HH24:MI:SS TZR') ||'"'||chr(10)|| 
	'      }' 
 as elementrow 
from wqx.hydrologic_event 
order by hydev_name
) 
) 
--START WITH rn = 1 
--CONNECT BY rn = PRIOR rn + 1 
/ 
--footerinfo: 
select 
'            ]' ||chr(10)||
'	}' ||chr(10)||
'}' 
from dual;
spool off

--
spool IndexType.json
--headerinfo: 
select	
    '{' ||chr(10)||
	'    "root": {' ||chr(10)||
	'      "Index Type": [' 
--	'  "WQXEntity": "' ||chr(10)||
--	'    "WQXElement": " domainEntity="Index Type" schemaElement="IndexTypeName/Name" rowCount="'||(select 
--count(*) from wqx.Index_type)||'" maxLastChangeDate="'||to_char(cast((select max(idxtyp_last_change_date) 
--from wqx.Index_type) as timestamp with LOCAL time zone), 'YYYY-MM-DD"T"HH24:MI:SSTZR')||'" }'
from dual;
SELECT 
--SELECT replace( LTRIM(MAX(SYS_CONNECT_BY_PATH(elementrow, '|')), '|'), '|', ','||chr(10)) as elementrecord
	case when rn=1 then '' else ',' end as rowdelimiter, 
	elementrow 
from ( SELECT rownum rn, elementrow
FROM (SELECT 
	'      {' ||chr(10)|| 
	'      "Domain": "Index Type(IndexTypeName)",' ||chr(10)||
	'      "UID": "'||idxtyp_uid||'",' ||chr(10)||
	'      "Identifier": "'|| replace(replace(translate(idxtyp_id,'"\'||chr(10),'^/|'),'^','\"'),'/','\')||'",' ||chr(10)||
	'      "Context": "'|| replace(replace(translate(idxtyp_context,'"\'||chr(10),'^/|'),'^','\"'),'/','\')||'",' ||chr(10)||
	'      "Name": "'|| replace(replace(translate(idxtyp_name,'"\'||chr(10),'^/|'),'^','\"'),'/','\')||'",' ||chr(10)||
	'      "Scale": "'|| replace(replace(translate(idxtyp_scale,'"\'||chr(10),'^/|'),'^','\"'),'/','\')||'",' ||chr(10)||
	'      "LastChangeDate": "'||to_char(cast(idxtyp_last_change_date as timestamp with LOCAL time zone), 'YYYY-MM-DD"T"HH24:MI:SS TZR') ||'"'||chr(10)|| 
	'      }' 
 as elementrow 
from wqx.Index_type 
order by idxtyp_id
) 
) 
--START WITH rn = 1 
--CONNECT BY rn = PRIOR rn + 1 
/ 
--footerinfo: 
select 
'            ]' ||chr(10)||
'	}' ||chr(10)||
'}' 
from dual;
spool off

--
spool LabSamplePreparationMethod.json
--headerinfo: 
select	
    '{' ||chr(10)||
	'    "root": {' ||chr(10)||
	'      "LabSample Preparation Method": [' 
--	'  "WQXEntity": "' ||chr(10)||
--	'    "WQXElement": " domainEntity="LabSample Preparation Method" 
--schemaElement="LabSamplePreparationMethod/MethodIdentifier" rowCount="'||(select count(*) from 
--wqx.lab_sample_preparation_method)||'" maxLastChangeDate="'||to_char(cast((select 
--max(lspmth_last_change_date) from wqx.lab_sample_preparation_method) as timestamp with LOCAL time zone), 
--'YYYY-MM-DD"T"HH24:MI:SSTZR')||'" }'
from dual;
SELECT 
--SELECT replace( LTRIM(MAX(SYS_CONNECT_BY_PATH(elementrow, '|')), '|'), '|', ','||chr(10)) as elementrecord
	case when rn=1 then '' else ',' end as rowdelimiter, 
	elementrow 
from ( SELECT rownum rn, elementrow
FROM (SELECT 
	'      {' ||chr(10)|| 
	'      "Domain": "Lab Sample Preparation Method(LabSamplePreparationMethod/MethodIdentifier)",' ||chr(10)||
	'      "UID": "'||lspmth_uid||'",' ||chr(10)||
	'      "Identifier": "'|| replace(replace(translate(lspmth_id,'"\'||chr(10),'^/|'),'^','\"'),'/','\')||'",' ||chr(10)||
	'      "Context": "'|| replace(replace(translate(lspmth_context,'"\'||chr(10),'^/|'),'^','\"'),'/','\')||'",' ||chr(10)||
	'      "Name": "'|| replace(replace(translate(lspmth_name,'"\'||chr(10),'^/|'),'^','\"'),'/','\')||'",' ||chr(10)||
	'      "QualifierType": "'|| replace(replace(translate(lspmth_qual_type,'"\'||chr(10),'^/|'),'^','\"'),'/','\')||'",' ||chr(10)||
	'      "Description": "'|| replace(replace(translate(lspmth_desc,'"\'||chr(10),'^/|'),'^','\"'),'/','\')||'",' ||chr(10)||
	'      "LastChangeDate": "'||to_char(cast(lspmth_last_change_date as timestamp with LOCAL time zone), 'YYYY-MM-DD"T"HH24:MI:SS TZR') ||'"'||chr(10)|| 
	'      }' 
 as elementrow 
from wqx.lab_sample_preparation_method 
order by lspmth_name
) 
) 
--START WITH rn = 1 
--CONNECT BY rn = PRIOR rn + 1 
/ 
--footerinfo: 
select 
'            ]' ||chr(10)||
'	}' ||chr(10)||
'}' 
from dual;
spool off

--
spool MeasureQualifier.json
--headerinfo: 
select	
    '{' ||chr(10)||
	'    "root": {' ||chr(10)||
	'      "Measure Qualifier": [' 
--	'  "WQXEntity": "' ||chr(10)||
--	'    "WQXElement": " domainEntity="Measure Qualifier" schemaElement="MeasureQualifierCode" 
--rowCount="'||(select count(*) from wqx.measure_qualifier)||'" maxLastChangeDate="'||to_char(cast((select 
--max(msrqlf_last_change_date) from wqx.measure_qualifier) as timestamp with LOCAL time zone), 
--'YYYY-MM-DD"T"HH24:MI:SSTZR')||'" }'
from dual;
SELECT 
--SELECT replace( LTRIM(MAX(SYS_CONNECT_BY_PATH(elementrow, '|')), '|'), '|', ','||chr(10)) as elementrecord
	case when rn=1 then '' else ',' end as rowdelimiter, 
	elementrow 
from ( SELECT rownum rn, elementrow
FROM (SELECT 
	'      {' ||chr(10)|| 
	'      "Domain": "Measure Qualifier(MeasureQualifierCode)",' ||chr(10)||
	'      "UID": "'||msrqlf_uid||'",' ||chr(10)||
	'      "Code": "'|| replace(replace(translate(msrqlf_cd,'"\'||chr(10),'^/|'),'^','\"'),'/','\')||'",' ||chr(10)||
	'      "Description": "'|| replace(replace(translate(msrqlf_desc,'"\'||chr(10),'^/|'),'^','\"'),'/','\')||'",' ||chr(10)||
	'      "LastChangeDate": "'||to_char(cast(msrqlf_last_change_date as timestamp with LOCAL time zone), 'YYYY-MM-DD"T"HH24:MI:SS TZR') ||'"'||chr(10)|| 
	'      }' 
 as elementrow 
from wqx.measure_qualifier 
--where msrqlf_deprecated is null
order by msrqlf_cd
) 
) 
--START WITH rn = 1 
--CONNECT BY rn = PRIOR rn + 1 
/ 
--footerinfo: 
select 
'            ]' ||chr(10)||
'	}' ||chr(10)||
'}' 
from dual;
spool off

--
spool MeasureQualifierDEPRECATED.json
--headerinfo: 
select	
    '{' ||chr(10)||
	'    "root": {' ||chr(10)||
	'      "Measure Qualifier DEPRECATED": [' 
--	'  "WQXEntity": "' ||chr(10)||
--	'    "WQXElement": " domainEntity="Measure Qualifier DEPRECATED" 
--schemaElement="MeasureQualifierCode" rowCount="'||(select count(*) from 
--wqx.measure_qualifier_legacy)||'" maxLastChangeDate="'||to_char(cast((select max(rmqlf_last_change_date) 
--from wqx.measure_qualifier_legacy) as timestamp with LOCAL time zone), 'YYYY-MM-DD"T"HH24:MI:SSTZR')||'" }'
from dual;
SELECT 
--SELECT replace( LTRIM(MAX(SYS_CONNECT_BY_PATH(elementrow, '|')), '|'), '|', ','||chr(10)) as elementrecord
	case when rn=1 then '' else ',' end as rowdelimiter, 
	elementrow 
from ( SELECT rownum rn, elementrow
FROM (SELECT 
	'      {' ||chr(10)|| 
	'      "Domain": "Measure Qualifier DEPRECATED(MeasureQualifierCode)",' ||chr(10)||
	'      "UID": "'||rmqlf_uid||'",' ||chr(10)||
	'      "Code": "'|| replace(replace(translate(rmqlf_cd_old,'"\'||chr(10),'^/|'),'^','\"'),'/','\')||'",' ||chr(10)||
	'      "NewCode1": "'|| replace(replace(translate(msrqlf_cd1,'"\'||chr(10),'^/|'),'^','\"'),'/','\')||'",' ||chr(10)||
	'      "NewCode2": "'|| replace(replace(translate(msrqlf_cd2,'"\'||chr(10),'^/|'),'^','\"'),'/','\')||'",' ||chr(10)||
	'      "NewCode3": "'|| replace(replace(translate(msrqlf_cd3,'"\'||chr(10),'^/|'),'^','\"'),'/','\')||'",' ||chr(10)||
	'      "NewCode4": "'|| replace(replace(translate(msrqlf_cd4,'"\'||chr(10),'^/|'),'^','\"'),'/','\')||'",' ||chr(10)||
	'      "NewCode5": "'|| replace(replace(translate(msrqlf_cd5,'"\'||chr(10),'^/|'),'^','\"'),'/','\')||'",' ||chr(10)||
	'      "NewCode6": "'|| replace(replace(translate(msrqlf_cd6,'"\'||chr(10),'^/|'),'^','\"'),'/','\')||'",' ||chr(10)||
	'      "Description": "'|| replace(replace(translate(rmqlf_desc,'"\'||chr(10),'^/|'),'^','\"'),'/','\')||'",' ||chr(10)||
	'      "LastChangeDate": "'||to_char(cast(rmqlf_last_change_date as timestamp with LOCAL time zone), 'YYYY-MM-DD"T"HH24:MI:SS TZR') ||'"'||chr(10)|| 
	'      }' 
 as elementrow 
from wqx.measure_qualifier_legacy 
order by rmqlf_cd_old
) 
) 
--START WITH rn = 1 
--CONNECT BY rn = PRIOR rn + 1 
/ 
--footerinfo: 
select 
'            ]' ||chr(10)||
'	}' ||chr(10)||
'}' 
from dual;
spool off

--
spool MeasurementUnit.json
--headerinfo: 
select	
    '{' ||chr(10)||
	'    "root": {' ||chr(10)||
	'      "Measurement Unit": [' 
--	'  "WQXEntity": "' ||chr(10)||
--	'    "WQXElement": " domainEntity="Measurement Unit" schemaElement="MeasurementUnitCode" 
--rowCount="'||(select count(*) from wqx.measurement_unit)||'" maxLastChangeDate="'||to_char(cast((select 
--max(msunt_last_change_date) from wqx.measurement_unit) as timestamp with LOCAL time zone), 	
from dual;
SELECT 
--SELECT replace( LTRIM(MAX(SYS_CONNECT_BY_PATH(elementrow, '|')), '|'), '|', ','||chr(10)) as elementrecord
	case when rn=1 then '' else ',' end as rowdelimiter, 
	elementrow 
from ( SELECT rownum rn, elementrow
FROM (SELECT 
	'      {' ||chr(10)|| 
	'      "Domain": "Measurement Unit(MeasurementUnitCode)",' ||chr(10)||
	'      "UID": "'||msunt_uid||'",' ||chr(10)||
	'      "Code": "'|| replace(replace(translate(msunt_cd,'"\'||chr(10),'^/|'),'^','\"'),'/','\')||'",' ||chr(10)||
	'      "Description": "'|| replace(replace(translate(msunt_desc,'"\'||chr(10),'^/|'),'^','\"'),'/','\')||'",' ||chr(10)||
	'      "ConvertedUnitCode": "'|| replace(replace(translate(msunt_target_cd,'"\'||chr(10),'^/|'),'^','\"'),'/','\')||'",' ||chr(10)||
	'      "ConversionFactor": "'||msunt_conversion_factor||'",' ||chr(10)||
	'      "ConversionCoefficient": "'||msunt_conversion_coefficient||'",' ||chr(10)||
	'      "LastChangeDate": "'||to_char(cast(msunt_last_change_date as timestamp with LOCAL time zone), 'YYYY-MM-DD"T"HH24:MI:SS TZR') ||'"'||chr(10)|| 
	'      }' 
 as elementrow 
from wqx.measurement_unit 
order by msunt_cd
) 
) 
--START WITH rn = 1 
--CONNECT BY rn = PRIOR rn + 1 
/ 
--footerinfo: 
select 
'            ]' ||chr(10)||
'	}' ||chr(10)||
'}' 
from dual;
spool off

--
spool MethodSpeciation.json
--headerinfo: 
select	
    '{' ||chr(10)||
	'    "root": {' ||chr(10)||
	'      "Method Speciation": [' 
--	'  "WQXEntity": "' ||chr(10)||
--	'    "WQXElement": " domainEntity="Method Speciation" schemaElement="MethodSpeciationName" 
--rowCount="'||(select count(*) from wqx.method_speciation)||'" maxLastChangeDate="'||to_char(cast((select 
--max(mthspc_last_change_date) from wqx.method_speciation) as timestamp with LOCAL time zone), 
--'YYYY-MM-DD"T"HH24:MI:SSTZR')||'" }'
from dual;
SELECT 
--SELECT replace( LTRIM(MAX(SYS_CONNECT_BY_PATH(elementrow, '|')), '|'), '|', ','||chr(10)) as elementrecord
	case when rn=1 then '' else ',' end as rowdelimiter, 
	elementrow 
from ( SELECT rownum rn, elementrow
FROM (SELECT 
	'      {' ||chr(10)|| 
	'      "Domain": "Method Speciation(MethodSpeciationName)",' ||chr(10)||
	'      "UID": "'||mthspc_uid||'",' ||chr(10)||
	'      "Name": "'|| replace(replace(translate(mthspc_name,'"\'||chr(10),'^/|'),'^','\"'),'/','\')||'",' ||chr(10)||
	'      "Description": "'|| replace(replace(translate(mthspc_desc,'"\'||chr(10),'^/|'),'^','\"'),'/','\')||'",' ||chr(10)||
	'      "LastChangeDate": "'||to_char(cast(mthspc_last_change_date as timestamp with LOCAL time zone), 'YYYY-MM-DD"T"HH24:MI:SS TZR') ||'"'||chr(10)|| 
	'      }' 
 as elementrow 
from wqx.method_speciation 
order by mthspc_name
) 
) 
--START WITH rn = 1 
--CONNECT BY rn = PRIOR rn + 1 
/ 
--footerinfo: 
select 
'            ]' ||chr(10)||
'	}' ||chr(10)||
'}' 
from dual;
spool off

--
spool MetricType.json
--headerinfo: 
select	
    '{' ||chr(10)||
	'    "root": {' ||chr(10)||
	'      "Metric Type": [' 
--	'  "WQXEntity": "' ||chr(10)||
--	'    "WQXElement": " domainEntity="Metric Type" schemaElement="MetricTypeName/Name" rowCount="'||(select 
--count(*) from wqx.metric_type)||'" maxLastChangeDate="'||to_char(cast((select max(mettyp_last_change_date) 
--from wqx.metric_type) as timestamp with LOCAL time zone), 'YYYY-MM-DD"T"HH24:MI:SSTZR')||'" }'
from dual;
SELECT 
--SELECT replace( LTRIM(MAX(SYS_CONNECT_BY_PATH(elementrow, '|')), '|'), '|', ','||chr(10)) as elementrecord
	case when rn=1 then '' else ',' end as rowdelimiter, 
	elementrow 
from ( SELECT rownum rn, elementrow
FROM (SELECT 
	'      {' ||chr(10)|| 
	'      "Domain": "Metric Type(MetricTypeName)",' ||chr(10)||
	'      "UID": "'||mettyp_uid||'",' ||chr(10)||
	'      "Identifier": "'|| replace(replace(translate(mettyp_id,'"\'||chr(10),'^/|'),'^','\"'),'/','\')||'",' ||chr(10)||
	'      "Context": "'|| replace(replace(translate(mettyp_context,'"\'||chr(10),'^/|'),'^','\"'),'/','\')||'",' ||chr(10)||
	'      "Name": "'|| replace(replace(translate(mettyp_name,'"\'||chr(10),'^/|'),'^','\"'),'/','\')||'",' ||chr(10)||
	'      "Scale": "'|| replace(replace(translate(mettyp_scale,'"\'||chr(10),'^/|'),'^','\"'),'/','\')||'",' ||chr(10)||
	'      "FormulaDescription": "'||
	substr(replace(replace(translate(mettyp_formula_desc,'"\'||chr(10),'^/|'),'^','\"'),'/','\'), 1, 3500)||'",' ||chr(10)||
	'      "LastChangeDate": "'||to_char(cast(mettyp_last_change_date as timestamp with LOCAL time zone), 'YYYY-MM-DD"T"HH24:MI:SS TZR') ||'"'||chr(10) || 
	'      }' 
 as elementrow 
from wqx.metric_type 
order by mettyp_id 
)
) 
--START WITH rn = 1 
--CONNECT BY rn = PRIOR rn + 1 
/ 
--footerinfo: 
select 
'            ]' ||chr(10)||
'	}' ||chr(10)||
'}' 
from dual;
spool off


--
spool MetricTypeContext.json
--headerinfo: 
select	
    '{' ||chr(10)||
	'    "root": {' ||chr(10)||
	'      "Metric Type Context": [' 
--	'  "WQXEntity": "' ||chr(10)||
--	'    "WQXElement": " domainEntity="Metric Type Context" schemaElement="MetricTypeIdentifierContext" 
--rowCount="'||(select count(*) from wqx.metric_type_context)||'" maxLastChangeDate="'||to_char(cast((select 
--max(mtctx_last_change_date) from wqx.metric_type_context) as timestamp with LOCAL time zone), 
--'YYYY-MM-DD"T"HH24:MI:SSTZR')||'" }'
from dual;
SELECT 
--SELECT replace( LTRIM(MAX(SYS_CONNECT_BY_PATH(elementrow, '|')), '|'), '|', ','||chr(10)) as elementrecord
	case when rn=1 then '' else ',' end as rowdelimiter, 
	elementrow 
from ( SELECT rownum rn, elementrow
FROM (SELECT 
	'      {' ||chr(10)|| 
	'      "Domain": "Metric Type Context(MetricTypeIdentifierContext)",' ||chr(10)||
	'      "UID": "'||mtctx_uid||'",' ||chr(10)||
	'      "Code": "'|| replace(replace(translate(mtctx_cd,'"\'||chr(10),'^/|'),'^','\"'),'/','\')||'",' ||chr(10)||
	'      "Name": "'|| replace(replace(translate(mtctx_name,'"\'||chr(10),'^/|'),'^','\"'),'/','\')||'",' ||chr(10)||
	'      "Description": "'|| replace(replace(translate(mtctx_desc,'"\'||chr(10),'^/|'),'^','\"'),'/','\')||'",' ||chr(10)||
	'      "LastChangeDate": "'||to_char(cast(mtctx_last_change_date as timestamp with LOCAL time zone), 'YYYY-MM-DD"T"HH24:MI:SS TZR') ||'"'||chr(10)|| 
	'      }' 
 as elementrow 
from wqx.metric_type_context 
order by mtctx_cd
) 
) 
--START WITH rn = 1 
--CONNECT BY rn = PRIOR rn + 1 
/ 
--footerinfo: 
select 
'            ]' ||chr(10)||
'	}' ||chr(10)||
'}' 
from dual;
spool off

--
spool MonitoringLocationType.json
--headerinfo: 
select	
    '{' ||chr(10)||
	'    "root": {' ||chr(10)||
	'      "Monitoring Location Type": [' 
--	'  "WQXEntity": "' ||chr(10)||
--	'    "WQXElement": " domainEntity="Monitoring Location Type" 
--schemaElement="MonitoringLocationTypeName" rowCount="'||(select count(*) from 
--wqx.monitoring_location_type)||'" maxLastChangeDate="'||to_char(cast((select max(mltyp_last_change_date) 
--from wqx.monitoring_location_type) as timestamp with LOCAL time zone), 'YYYY-MM-DD"T"HH24:MI:SSTZR')||'" }'
from dual;
SELECT 
--SELECT replace( LTRIM(MAX(SYS_CONNECT_BY_PATH(elementrow, '|')), '|'), '|', ','||chr(10)) as elementrecord
	case when rn=1 then '' else ',' end as rowdelimiter, 
	elementrow 
from ( SELECT rownum rn, elementrow
FROM (SELECT 
	'      {' ||chr(10)|| 
	'      "Domain": "Monitoring Location Type(MonitoringLocationTypeName)",' ||chr(10)||
	'      "UID": "'||mltyp_uid||'",' ||chr(10)||
	'      "Name": "'|| replace(replace(translate(mltyp_name,'"\'||chr(10),'^/|'),'^','\"'),'/','\')||'",' ||chr(10)||
	'      "Description": "'|| replace(replace(translate(mltyp_desc,'"\'||chr(10),'^/|'),'^','\"'),'/','\')||'",' ||chr(10)||
	'      "LastChangeDate": "'||to_char(cast(mltyp_last_change_date as timestamp with LOCAL time zone), 'YYYY-MM-DD"T"HH24:MI:SS TZR') ||'"'||chr(10)|| 
	'      "WQPSiteType": "'|| replace(replace(translate(mltyp_wqp_site_type,'"\'||chr(10),'^/|'),'^','\"'),'/','\')||'",' ||chr(10)||
	'      }' 
 as elementrow 
from wqx.monitoring_location_type 
order by mltyp_name
) 
) 
--START WITH rn = 1 
--CONNECT BY rn = PRIOR rn + 1 
/ 
--footerinfo: 
select 
'            ]' ||chr(10)||
'	}' ||chr(10)||
'}' 
from dual;
spool off

--
spool NetType.json
--headerinfo: 
select	
    '{' ||chr(10)||
	'    "root": {' ||chr(10)||
	'      "Net Type": [' 
--	'  "WQXEntity": "' ||chr(10)||
--	'    "WQXElement": " domainEntity="Net Type" schemaElement="NetTypeName" rowCount="'||(select 
--count(*) from wqx.net_type)||'" maxLastChangeDate="'||to_char(cast((select max(nettyp_last_change_date) 
--from wqx.net_type) as timestamp with LOCAL time zone), 'YYYY-MM-DD"T"HH24:MI:SSTZR')||'" }'
from dual;
SELECT 
--SELECT replace( LTRIM(MAX(SYS_CONNECT_BY_PATH(elementrow, '|')), '|'), '|', ','||chr(10)) as elementrecord
	case when rn=1 then '' else ',' end as rowdelimiter, 
	elementrow 
from ( SELECT rownum rn, elementrow
FROM (SELECT 
	'      {' ||chr(10)|| 
	'      "Domain": "Net Type(NetTypeName)",' ||chr(10)||
	'      "UID": "'||nettyp_uid||'",' ||chr(10)||
	'      "EquipmentType": "'|| replace(replace(translate(scetyp_name,'"\'||chr(10),'^/|'),'^','\"'),'/','\')||'",' ||chr(10)||
	'      "Name": "'|| replace(replace(translate(nettyp_name,'"\'||chr(10),'^/|'),'^','\"'),'/','\')||'",' ||chr(10)||
	'      "Description": "'|| replace(replace(translate(nettyp_desc,'"\'||chr(10),'^/|'),'^','\"'),'/','\')||'",' ||chr(10)||
	'      "Abbrevation": "'||nettyp_abbrev||'",' ||chr(10)||
	'      "LastChangeDate": "'||to_char(cast(nettyp_last_change_date as timestamp with LOCAL time zone), 'YYYY-MM-DD"T"HH24:MI:SS TZR') ||'"'||chr(10)|| 
	'      }' 
 as elementrow 
from wqx.net_type nt, wqx.sample_collection_equip_type scet
where nt.scetyp_uid=scet.scetyp_uid
order by nettyp_name
) 
) 
--START WITH rn = 1 
--CONNECT BY rn = PRIOR rn + 1 
/ 
--footerinfo: 
select 
'            ]' ||chr(10)||
'	}' ||chr(10)||
'}' 
from dual;
spool off

--
spool Organization.json
--headerinfo: 
select	
    '{' ||chr(10)||
	'    "root": {' ||chr(10)||
	'      "Organization": [' 
--	'  "WQXEntity": "' ||chr(10)||
--	'    "WQXElement": " domainEntity="Organization" schemaElement="OrganizationIdentifier" 
--rowCount="'||(select count(*) from wqx.organization)||'" maxLastChangeDate="'||to_char(cast((select 
--max(org_last_change_date) from wqx.organization) as timestamp with LOCAL time zone), 
--'YYYY-MM-DD"T"HH24:MI:SSTZR')||'" }'
from dual;
SELECT 
--SELECT replace( LTRIM(MAX(SYS_CONNECT_BY_PATH(elementrow, '|')), '|'), '|', ','||chr(10)) as elementrecord
	case when rn=1 then '' else ',' end as rowdelimiter, 
	elementrow 
from ( SELECT rownum rn, elementrow
FROM (SELECT 
	'      {' ||chr(10)|| 
	'      "Domain": "Organization(OrganizationIdentifier)",' ||chr(10)||
	'      "UID": "'||o.org_uid||'",' ||chr(10)||
	'      "Identifier": "'|| replace(replace(translate(org_id,'"\'||chr(10),'^/|'),'^','\"'),'/','\')||'",' ||chr(10)||
	'      "Name": "'|| replace(replace(translate(org_name,'"\'||chr(10),'^/|'),'^','\"'),'/','\')||'",' ||chr(10)||
	'      "Type": "'|| replace(replace(translate(org_type,'"\'||chr(10),'^/|'),'^','\"'),'/','\')||'",' ||chr(10)||
	'      "TribalCode": "'|| replace(replace(translate(TRB_CD,'"\'||chr(10),'^/|'),'^','\"'),'/','\')||'",' ||chr(10)||
	'      "ElectronicAddress": "'|| replace(replace(translate(oea.eAddress,'"\'||chr(10),'^/|'),'^','\"'),'/','\')||'",' ||chr(10)||
	'      "ElectronicAddressType": "'|| replace(replace(translate(regexp_substr(oea.eAddress, '[^|]+', 1, 1),'"\'||chr(10),'^/|'),'^','\"'),'/','\')||'",' ||chr(10)||
	'      "ElectronicAddressText": "'|| replace(replace(translate(regexp_substr(oea.eAddress, '[^|]+', 1, 2),'"\'||chr(10),'^/|'),'^','\"'),'/','\')||'",' ||chr(10)||
	'      "TelephoneText": "'|| replace(replace(translate(op.ePhone,'"\'||chr(10),'^/|'),'^','\"'),'/','\')||'",' ||chr(10)||
	'      "TelephoneType": "'|| replace(replace(translate(regexp_substr(op.ePhone, '[^|]+', 1, 1),'"\'||chr(10),'^/|'),'^','\"'),'/','\')||'",' ||chr(10)||
	'      "TelephoneNumberText": "'|| replace(replace(translate(regexp_substr(op.ePhone, '[^|]+', 1, 2),'"\'||chr(10),'^/|'),'^','\"'),'/','\')||'",' ||chr(10)||
	'      "TelephoneExtensionNumberText": "'|| replace(replace(translate(regexp_substr(op.ePhone, '[^|]+', 1, 3),'"\'||chr(10),'^/|'),'^','\"'),'/','\')||'",' ||chr(10)||
	'      "PhysicalAddress": "'|| replace(replace(translate(oadd.address,'"\'||chr(10),'^/|'),'^','\"'),'/','\')||'",' ||chr(10)||
	'      "AddressType": "'|| replace(replace(translate(regexp_substr(oadd.address, '[^|]+', 1, 1),'"\'||chr(10),'^/|'),'^','\"'),'/','\')||'",' ||chr(10)||
	'      "AddressText": "'|| replace(replace(translate(regexp_substr(oadd.address, '[^|]+', 1, 2),'"\'||chr(10),'^/|'),'^','\"'),'/','\')||'",' ||chr(10)||
	'      "SupplementalAddressText": "'|| replace(replace(translate(regexp_substr(oadd.address, '[^|]+', 1, 3),'"\'||chr(10),'^/|'),'^','\"'),'/','\')||'",' ||chr(10)||
	'      "Locality": "'|| replace(replace(translate(regexp_substr(oadd.address, '[^|]+', 1, 4),'"\'||chr(10),'^/|'),'^','\"'),'/','\')||'",' ||chr(10)||
	'      "StateCode": "'|| replace(replace(translate(regexp_substr(oadd.address, '[^|]+', 1, 5),'"\'||chr(10),'^/|'),'^','\"'),'/','\')||'",' ||chr(10)||
	'      "PostalCode": "'|| replace(replace(translate(regexp_substr(oadd.address, '[^|]+', 1, 6),'"\'||chr(10),'^/|'),'^','\"'),'/','\')||'",' ||chr(10)||
	'      "Description": "'|| replace(replace(translate(org_desc,'"\'||chr(10),'^/|'),'^','\"'),'/','\')||'",' ||chr(10)||
	'      "LastChangeDate": "'||to_char(cast(org_last_change_date as timestamp with LOCAL time zone), 'YYYY-MM-DD"T"HH24:MI:SS TZR') ||'"'||chr(10)|| 
	'      }' 
 as elementrow 
from wqx.organization O, 
--WQX.ORG_ELECTRONIC_ADDRESS P, WQX.ORG_PHONE Q, WQX.ORG_ADDRESS R, WQX.STATE S, 
WQX.TRIBE T ,
(select org_uid,replace(listagg(phtyp_name||' | '||orgph_num||nullif(' ext: '||orgph_ext,' ext: '),'| ; '),'| ;',' ;') ePhone 
           from (select distinct orgph.org_uid,orgph.orgph_uid,phtyp.phtyp_name,orgph.orgph_num,orgph.orgph_ext from wqx.org_phone orgph, wqx.phone_type phtyp where orgph.org_uid in (select org_uid from kcs.org@owstg) and phtyp.phtyp_uid = orgph.phtyp_uid) 
           where 1=1 group by org_uid) op,
(select org_uid, replace(listagg(addtyp_name||' | '||orgadd_address||' | '||orgadd_address_supplemental||' | '||orgadd_locality_name||' | '||st_cd||' | '||orgadd_postal_cd||' | '||cntry_cd||' | '||lpad(cnty_fips_cd, 3, '0'),'| ; '),'| ;',' ;') address
                 from (select distinct orgadd.org_uid,orgadd.orgadd_uid,orgadd.orgadd_address,orgadd.orgadd_address_supplemental,orgadd.orgadd_locality_name,addtyp.addtyp_name,st.st_cd,orgadd.orgadd_postal_cd,cntry.cntry_cd,cnty.cnty_fips_cd from wqx.org_address orgadd, wqx.address_type addtyp, wqx.state st, wqx.country cntry, wqx.county cnty where orgadd.org_uid in (select org_uid from wqx.organization where org_id NOT in (' ')) and addtyp.addtyp_uid = orgadd.addtyp_uid and cnty.cnty_uid (+) = orgadd.cnty_uid and cntry.cntry_uid (+) = orgadd.cntry_uid and st.st_uid (+) = orgadd.st_uid) 
                 where 1=1 group by org_uid) oadd,
(select org_uid, replace(listagg(eatyp_name||' | '||orgea_text,'| ; '),'| ;',' ;') eAddress
                 from (select distinct orgea.org_uid,orgea.orgea_uid,orgea.orgea_text,eatyp.eatyp_name from wqx.org_electronic_address orgea, wqx.electronic_address_type eatyp where orgea.org_uid in (select org_uid from wqx.organization where org_id NOT in (' ')) and eatyp.eatyp_uid = orgea.eatyp_uid) 
                 where 1=1 group by org_uid) oea 
--WHERE O."ORG_UID" = P."ORG_UID" (+) AND O."ORG_UID" = Q."ORG_UID" (+) AND O."ORG_UID" = R."ORG_UID" (+) AND R."ST_UID" = S."ST_UID" AND 
WHERE O."TRB_UID" = T."TRB_UID" (+) AND o.org_uid = oadd.org_uid (+) AND o.org_uid = oea.org_uid (+) AND o.org_uid = op.org_uid (+)
order by org_id
) 
) 
--START WITH rn = 1 
--CONNECT BY rn = PRIOR rn + 1 
/ 
--footerinfo: 
select 
'            ]' ||chr(10)||
'	}' ||chr(10)||
'}' 
from dual;
spool off

--
spool OrganizationType.json
--headerinfo: 
select	
    '{' ||chr(10)||
	'    "root": {' ||chr(10)||
	'      "Organization Type": [' 
--	'  "WQXEntity": "' ||chr(10)||
--	'    "WQXElement": " domainEntity="Organization Type" schemaElement="OrganizationTypeName" 
--rowCount="'||(select count(*) from wqx.organization_type)||'" maxLastChangeDate="'||to_char(cast((select 
--max(orgtyp_last_change_date) from wqx.organization_type) as timestamp with LOCAL time zone), 
--'YYYY-MM-DD"T"HH24:MI:SSTZR')||'" }'
from dual;
SELECT 
--SELECT replace( LTRIM(MAX(SYS_CONNECT_BY_PATH(elementrow, '|')), '|'), '|', ','||chr(10)) as elementrecord
	case when rn=1 then '' else ',' end as rowdelimiter, 
	elementrow 
from ( SELECT rownum rn, elementrow
FROM (SELECT 
	'      {' ||chr(10)|| 
	'      "Domain": "Organization Type(OrganizationTypeName)",' ||chr(10)||
--	'      "UID": "'||orgtyp_uid||'",' ||chr(10)||
	'      "Name": "'|| replace(replace(translate(org_type,'"\'||chr(10),'^/|'),'^','\"'),'/','\')||'",' ||chr(10)||
--	'      "Name": "'||orgtyp_name||'",' ||chr(10)||
	'      "Description": "'|| replace(replace(translate(orgtyp_desc,'"\'||chr(10),'^/|'),'^','\"'),'/','\')||'",' ||chr(10)||
	'      "LastChangeDate": "'||to_char(cast(orgtyp_last_change_date as timestamp with LOCAL time zone), 'YYYY-MM-DD"T"HH24:MI:SS TZR') ||'"'||chr(10)|| 
	'      }' 
 as elementrow 
from wqx.organization_type 
order by org_type
) 
) 
--START WITH rn = 1 
--CONNECT BY rn = PRIOR rn + 1 
/ 
--footerinfo: 
select 
'            ]' ||chr(10)||
'	}' ||chr(10)||
'}' 
from dual;
spool off

--
spool PhoneType.json
--headerinfo: 
select	
    '{' ||chr(10)||
	'    "root": {' ||chr(10)||
	'      "Phone Type": [' 
--	'  "WQXEntity": "' ||chr(10)||
--	'    "WQXElement": " domainEntity="Phone Type" schemaElement="Telephonic/TelephoneNumberTypeName" rowCount="'||(select 
--count(*) from wqx.phone_type)||'" maxLastChangeDate="'||to_char(cast((select max(phtyp_last_change_date) 
--from wqx.phone_type) as timestamp with LOCAL time zone), 'YYYY-MM-DD"T"HH24:MI:SSTZR')||'" }'
from dual;
SELECT 
--SELECT replace( LTRIM(MAX(SYS_CONNECT_BY_PATH(elementrow, '|')), '|'), '|', ','||chr(10)) as elementrecord
	case when rn=1 then '' else ',' end as rowdelimiter, 
	elementrow 
from ( SELECT rownum rn, elementrow
FROM (SELECT 
	'      {' ||chr(10)|| 
	'      "Domain": "Phone Type(TelephoneNumberTypeName)",' ||chr(10)||
	'      "UID": "'||phtyp_uid||'",' ||chr(10)||
	'      "Name": "'|| replace(replace(translate(phtyp_name,'"\'||chr(10),'^/|'),'^','\"'),'/','\')||'",' ||chr(10)||
	'      "Description": "'|| replace(replace(translate(phtyp_desc,'"\'||chr(10),'^/|'),'^','\"'),'/','\')||'",' ||chr(10)||
	'      "LastChangeDate": "'||to_char(cast(phtyp_last_change_date as timestamp with LOCAL time zone), 'YYYY-MM-DD"T"HH24:MI:SS TZR') ||'"'||chr(10)|| 
	'      }' 
 as elementrow 
from wqx.phone_type 
order by phtyp_name
) 
) 
--START WITH rn = 1 
--CONNECT BY rn = PRIOR rn + 1 
/ 
--footerinfo: 
select 
'            ]' ||chr(10)||
'	}' ||chr(10)||
'}' 
from dual;
spool off

--
spool QAQCCharacteristicValidation.json
--headerinfo: 
select	
    '{' ||chr(10)||
	'    "root": {' ||chr(10)||
	'      "QAQC Characteristic Validation": [' 
--	'  "WQXEntity": "' ||chr(10)||
--	'    "WQXElement": " domainEntity="QAQC Characteristic Validation" 
--schemaElement="CharacteristicName" rowCount="'||(select count(*) from 
--wqx.qaqc_characteristic_validation)||'" maxLastChangeDate="'||to_char(cast((select 
--max(qcv_last_change_date) from wqx.qaqc_characteristic_validation) as timestamp with LOCAL time zone), 
--'YYYY-MM-DD"T"HH24:MI:SSTZR')||'" }'
from dual;
SELECT 
--SELECT replace( LTRIM(MAX(SYS_CONNECT_BY_PATH(elementrow, '|')), '|'), '|', ','||chr(10)) as elementrecord
	case when rn=1 then '' else ',' end as rowdelimiter, 
	elementrow 
from ( SELECT rownum rn, elementrow
FROM (SELECT 
	'      {' ||chr(10)|| 
	'      "Domain": "QAQC Characteristic Validation(CharacteristicName)",' ||chr(10)||
	'      "UID": "'||qcv_uid||'",' ||chr(10)||
	'      "CharacteristicDomain": "'|| replace(replace(translate(qcv_domain,'"\'||chr(10),'^/|'),'^','\"'),'/','\')||'",' ||chr(10)||
	'      "CharacteristicSearch": "'|| replace(replace(translate(qcv_chr_name_search,'"\'||chr(10),'^/|'),'^','\"'),'/','\')||'",' ||chr(10)||
	'      "ValueSearch": "'|| replace(replace(translate(qcv_value_search,'"\'||chr(10),'^/|'),'^','\"'),'/','\')||'",' ||chr(10)||
	'      "SourceSearch": "'|| replace(replace(translate(qcv_source_search,'"\'||chr(10),'^/|'),'^','\"'),'/','\')||'",' ||chr(10)||
	'      "Status": "'||qcv_domain_status||'",' ||chr(10)||
	'      "MeasureUnit": "'|| replace(replace(translate(qcv_value_unit,'"\'||chr(10),'^/|'),'^','\"'),'/','\')||'",' ||chr(10)||
	'      "MeasureMIN": "'||qcv_min_value||'",' ||chr(10)||
	'      "MeasureMAX": "'||qcv_max_value||'",' ||chr(10)||
	'      "MeasureConversionFactor": "'||qcv_conversion_factor||'",' ||chr(10)||
	'      "MeasureConversionCoefficient": "'||qcv_conversion_coefficient||'",' ||chr(10)||
	'      "NotesRecommendations": "'|| replace(replace(translate(qcv_notes_recommendation,'"\'||chr(10),'^/|'),'^','\"'),'/','\')||'",' ||chr(10)||
	'      "LastChangeDate": "'||to_char(cast(qcv_last_change_date as timestamp with LOCAL time zone), 'YYYY-MM-DD"T"HH24:MI:SS TZR') ||'"'||chr(10)|| 
	'      }' 
 as elementrow 
from wqx.qaqc_characteristic_validation 
order by qcv_domain, qcv_chr_name_search
) 
) 
--START WITH rn = 1 
--CONNECT BY rn = PRIOR rn + 1 
/ 
--footerinfo: 
select 
'            ]' ||chr(10)||
'	}' ||chr(10)||
'}' 
from dual;
spool off

--
spool ReferenceLocationType.json
--headerinfo: 
select	
    '{' ||chr(10)||
	'    "root": {' ||chr(10)||
	'      "Reference Location Type": [' 
--	'  "WQXEntity": "' ||chr(10)||
--	'    "WQXElement": " domainEntity="Reference Location Type" 
--schemaElement="ReferenceLocationTypeCode" rowCount="'||(select count(*) from 
--wqx.reference_location_type)||'" maxLastChangeDate="'||to_char(cast((select max(rltyp_last_change_date) 
--from wqx.reference_location_type) as timestamp with LOCAL time zone), 'YYYY-MM-DD"T"HH24:MI:SSTZR')||'" }'
from dual;
SELECT 
--SELECT replace( LTRIM(MAX(SYS_CONNECT_BY_PATH(elementrow, '|')), '|'), '|', ','||chr(10)) as elementrecord
	case when rn=1 then '' else ',' end as rowdelimiter, 
	elementrow 
from ( SELECT rownum rn, elementrow
FROM (SELECT 
	'      {' ||chr(10)|| 
	'      "Domain": "Reference Location Type(ReferenceLocationTypeCode)",' ||chr(10)||
	'      "UID": "'||rltyp_uid||'",' ||chr(10)||
	'      "Code": "'|| replace(replace(translate(rltyp_cd,'"\'||chr(10),'^/|'),'^','\"'),'/','\')||'",' ||chr(10)||
	'      "Description": "'|| replace(replace(translate(rltyp_desc,'"\'||chr(10),'^/|'),'^','\"'),'/','\')||'",' ||chr(10)||
	'      "LastChangeDate": "'||to_char(cast(rltyp_last_change_date as timestamp with LOCAL time zone), 'YYYY-MM-DD"T"HH24:MI:SS TZR') ||'"'||chr(10)|| 
	'      }' 
 as elementrow 
from wqx.reference_location_type 
order by rltyp_cd
) 
) 
--START WITH rn = 1 
--CONNECT BY rn = PRIOR rn + 1 
/ 
--footerinfo: 
select 
'            ]' ||chr(10)||
'	}' ||chr(10)||
'}' 
from dual;
spool off

--
spool RelativeDepth.json
--headerinfo: 
select	
    '{' ||chr(10)||
	'    "root": {' ||chr(10)||
	'      "Relative Depth": [' 
--	'  "WQXEntity": "' ||chr(10)||
--	'    "WQXElement": " domainEntity="Relative Depth" schemaElement="ActivityRelativeDepthName" 
--rowCount="'||(select count(*) from wqx.relative_depth)||'" maxLastChangeDate="'||to_char(cast((select 
--max(reldpth_last_change_date) from wqx.relative_depth) as timestamp with LOCAL time zone), 
--'YYYY-MM-DD"T"HH24:MI:SSTZR')||'" }'
from dual;
SELECT 
--SELECT replace( LTRIM(MAX(SYS_CONNECT_BY_PATH(elementrow, '|')), '|'), '|', ','||chr(10)) as elementrecord
	case when rn=1 then '' else ',' end as rowdelimiter, 
	elementrow 
from ( SELECT rownum rn, elementrow
FROM (SELECT 
	'      {' ||chr(10)|| 
	'      "Domain": "Relative Depth(ActivityRelativeDepthName)",' ||chr(10)||
	'      "UID": "'||reldpth_uid||'",' ||chr(10)||
	'      "Name": "'|| replace(replace(translate(reldpth_name,'"\'||chr(10),'^/|'),'^','\"'),'/','\')||'",' ||chr(10)||
	'      "Description": "'|| replace(replace(translate(reldpth_desc,'"\'||chr(10),'^/|'),'^','\"'),'/','\')||'",' ||chr(10)||
	'      "Abbreviation": "'||reldpth_abbrev||'",' ||chr(10)||
	'      "LastChangeDate": "'||to_char(cast(reldpth_last_change_date as timestamp with LOCAL time zone), 'YYYY-MM-DD"T"HH24:MI:SS TZR') ||'"'||chr(10)|| 
	'      }' 
 as elementrow 
from wqx.relative_depth 
order by reldpth_name
) 
) 
--START WITH rn = 1 
--CONNECT BY rn = PRIOR rn + 1 
/ 
select 

	'               }' ||chr(10)||
'            ]' ||chr(10)||
'	}' ||chr(10)||
'}' 
from dual;
spool off

--
spool ResultDetectionCondition.json
--headerinfo: 
select	
    '{' ||chr(10)||
	'    "root": {' ||chr(10)||
	'      "Result Detection Condition": [' 
--	'  "WQXEntity": "' ||chr(10)||
--	'    "WQXElement": " domainEntity="Result Detection Condition" 
--schemaElement="ResultDetectionConditionText" rowCount="'||(select count(*) from 
--wqx.result_detection_condition)||'" maxLastChangeDate="'||to_char(cast((select max(rdcnd_last_change_date) 
--from wqx.result_detection_condition) as timestamp with LOCAL time zone), 'YYYY-MM-DD"T"HH24:MI:SSTZR')||'" 
--}'
from dual;
SELECT 
--SELECT replace( LTRIM(MAX(SYS_CONNECT_BY_PATH(elementrow, '|')), '|'), '|', ','||chr(10)) as elementrecord
	case when rn=1 then '' else ',' end as rowdelimiter, 
	elementrow 
from ( SELECT rownum rn, elementrow
FROM (SELECT 
	'      {' ||chr(10)|| 
	'      "Domain": "Result Detection Condition(ResultDetectionConditionText)",' ||chr(10)||
	'      "UID": "'||rdcnd_uid||'",' ||chr(10)||
	'      "Name": "'|| replace(replace(translate(rdcnd_name,'"\'||chr(10),'^/|'),'^','\"'),'/','\')||'",' ||chr(10)||
	'      "Description": "'|| replace(replace(translate(rdcnd_desc,'"\'||chr(10),'^/|'),'^','\"'),'/','\')||'",' ||chr(10)||
	'      "LastChangeDate": "'||to_char(cast(rdcnd_last_change_date as timestamp with LOCAL time zone), 'YYYY-MM-DD"T"HH24:MI:SS TZR') ||'"'||chr(10)|| 
	'      }' 
 as elementrow 
from wqx.result_detection_condition 
order by rdcnd_name
) 
) 
--START WITH rn = 1 
--CONNECT BY rn = PRIOR rn + 1 
/ 
--footerinfo: 
select 
'            ]' ||chr(10)||
'	}' ||chr(10)||
'}' 
from dual;
spool off

--
spool ResultLabComment.json
--headerinfo: 
select	
    '{' ||chr(10)||
	'    "root": {' ||chr(10)||
	'      "Result Lab Comment": [' 
--	'  "WQXEntity": "' ||chr(10)||
--	'    "WQXElement": " domainEntity="Result Lab Comment" schemaElement="ResultLabInformation/LaboratoryCommentText" 
--rowCount="'||(select count(*) from wqx.result_lab_comment)||'" maxLastChangeDate="'||to_char(cast((select 
--max(rlcom_last_change_date) from wqx.result_lab_comment) as timestamp with LOCAL time zone), 
--'YYYY-MM-DD"T"HH24:MI:SSTZR')||'" }'
from dual;
SELECT 
--SELECT replace( LTRIM(MAX(SYS_CONNECT_BY_PATH(elementrow, '|')), '|'), '|', ','||chr(10)) as elementrecord
	case when rn=1 then '' else ',' end as rowdelimiter, 
	elementrow 
from ( SELECT rownum rn, elementrow
FROM (SELECT 
	'      {' ||chr(10)|| 
	'      "Domain": "Result Lab Comment(LaboratoryCommentText)",' ||chr(10)||
	'      "UID": "'||rlcom_uid||'",' ||chr(10)||
	'      "Code": "'|| replace(replace(translate(rlcom_cd,'"\'||chr(10),'^/|'),'^','\"'),'/','\')||'",' ||chr(10)||
	'      "Description": "'|| replace(replace(translate(rlcom_desc,'"\'||chr(10),'^/|'),'^','\"'),'/','\')||'",' ||chr(10)||
	'      "LastChangeDate": "'||to_char(cast(rlcom_last_change_date as timestamp with LOCAL time zone), 'YYYY-MM-DD"T"HH24:MI:SS TZR') ||'"'||chr(10)|| 
	'      }' 
 as elementrow 
from wqx.result_lab_comment 
order by rlcom_cd
) 
) 
--START WITH rn = 1 
--CONNECT BY rn = PRIOR rn + 1 
/ 
--footerinfo: 
select 
'            ]' ||chr(10)||
'	}' ||chr(10)||
'}' 
from dual;
spool off

--
spool ResultSamplingPointType.json
--headerinfo: 
select	
    '{' ||chr(10)||
	'    "root": {' ||chr(10)||
	'      "Result Sampling Point Type": [' 
--	'  "WQXEntity": "' ||chr(10)||
--	'    "WQXElement": " domainEntity="Result Sampling Point Type" 
--schemaElement="ResultSamplingPointType" rowCount="'||(select count(*) from 
--wqx.result_sampling_point_type)||'" maxLastChangeDate="'||to_char(cast((select 
--max(rsptyp_last_change_date) from wqx.result_sampling_point_type) as timestamp with LOCAL time zone), 
--'YYYY-MM-DD"T"HH24:MI:SSTZR')||'" }'
from dual;
SELECT 
--SELECT replace( LTRIM(MAX(SYS_CONNECT_BY_PATH(elementrow, '|')), '|'), '|', ','||chr(10)) as elementrecord
	case when rn=1 then '' else ',' end as rowdelimiter, 
	elementrow 
from ( SELECT rownum rn, elementrow
FROM (SELECT 
	'      {' ||chr(10)|| 
	'      "Domain": "Result Sampling Point Type(ResultSamplingPointType)",' ||chr(10)||
	'      "UID": "'||rsptyp_uid||'",' ||chr(10)||
	'      "Name": "'|| replace(replace(translate(rsptyp_name,'"\'||chr(10),'^/|'),'^','\"'),'/','\')||'",' ||chr(10)||
	'      "Description": "'|| replace(replace(translate(rsptyp_desc,'"\'||chr(10),'^/|'),'^','\"'),'/','\')||'",' ||chr(10)||
	'      "LastChangeDate": "'||to_char(cast(rsptyp_last_change_date as timestamp with LOCAL time zone), 'YYYY-MM-DD"T"HH24:MI:SS TZR') ||'"'||chr(10)|| 
	'      }' 
 as elementrow 
from wqx.result_sampling_point_type 
order by rsptyp_name
) 
) 
--START WITH rn = 1 
--CONNECT BY rn = PRIOR rn + 1 
/ 
--footerinfo: 
select 
'            ]' ||chr(10)||
'	}' ||chr(10)||
'}' 
from dual;
spool off

--
spool StatisticalBase.json
--headerinfo: 
select	
    '{' ||chr(10)||
	'    "root": {' ||chr(10)||
	'      "Statistical Base": [' 
--	'  "WQXEntity": "' ||chr(10)||
--	'    "WQXElement": " domainEntity="Statistical Base" schemaElement="StatisticalBaseCode" 
--rowCount="'||(select count(*) from wqx.result_statistical_base)||'" 
--maxLastChangeDate="'||to_char(cast((select max(rsbas_last_change_date) from wqx.result_statistical_base) 
--as timestamp with LOCAL time zone), 'YYYY-MM-DD"T"HH24:MI:SSTZR')||'" }'
from dual;
SELECT 
--SELECT replace( LTRIM(MAX(SYS_CONNECT_BY_PATH(elementrow, '|')), '|'), '|', ','||chr(10)) as elementrecord
	case when rn=1 then '' else ',' end as rowdelimiter, 
	elementrow 
from ( SELECT rownum rn, elementrow
FROM (SELECT 
	'      {' ||chr(10)|| 
	'      "Domain": "Statistical Base(StatisticalBaseCode)",' ||chr(10)||
	'      "UID": "'||rsbas_uid||'",' ||chr(10)||
	'      "Code": "'|| replace(replace(translate(rsbas_cd,'"\'||chr(10),'^/|'),'^','\"'),'/','\')||'",' ||chr(10)||
	'      "Description": "'|| replace(replace(translate(rsbas_desc,'"\'||chr(10),'^/|'),'^','\"'),'/','\')||'",' ||chr(10)||
	'      "LastChangeDate": "'||to_char(cast(rsbas_last_change_date as timestamp with LOCAL time zone), 'YYYY-MM-DD"T"HH24:MI:SS TZR') ||'"'||chr(10)|| 
	'      }' 
 as elementrow 
from wqx.result_statistical_base 
order by rsbas_cd
) 
) 
--START WITH rn = 1 
--CONNECT BY rn = PRIOR rn + 1 
/ 
select 
'            ]' ||chr(10)|| 
'	}' ||chr(10)|| 
'}' 
from dual;
spool off

--
spool ResultStatus.json
--headerinfo: 
select	
    '{' ||chr(10)||
	'    "root": {' ||chr(10)||
	'      "Result Status": [' 
--	'  "WQXEntity": "' ||chr(10)||
--	'    "WQXElement": " domainEntity="Result Status" schemaElement="ResultStatusIdentifier" 
--rowCount="'||(select count(*) from wqx.result_status)||'" maxLastChangeDate="'||to_char(cast((select 
--max(ressta_last_change_date) from wqx.result_status) as timestamp with LOCAL time zone), 
--'YYYY-MM-DD"T"HH24:MI:SSTZR')||'" }'
from dual;
SELECT 
--SELECT replace( LTRIM(MAX(SYS_CONNECT_BY_PATH(elementrow, '|')), '|'), '|', ','||chr(10)) as elementrecord
	case when rn=1 then '' else ',' end as rowdelimiter, 
	elementrow 
from ( SELECT rownum rn, elementrow
FROM (SELECT 
	'      {' ||chr(10)|| 
	'      "Domain": "Result Status(ResultStatusIdentifier)",' ||chr(10)||
	'      "UID": "'||ressta_uid||'",' ||chr(10)||
	'      "Name": "'|| replace(replace(translate(ressta_name,'"\'||chr(10),'^/|'),'^','\"'),'/','\')||'",' ||chr(10)||
	'      "Description": "'|| replace(replace(translate(ressta_desc,'"\'||chr(10),'^/|'),'^','\"'),'/','\')||'",' ||chr(10)||
	'      "LastChangeDate": "'||to_char(cast(ressta_last_change_date as timestamp with LOCAL time zone), 'YYYY-MM-DD"T"HH24:MI:SS TZR') ||'"'||chr(10)|| 
	'      }' 
 as elementrow 
from wqx.result_status 
order by ressta_name
) 
) 
--START WITH rn = 1 
--CONNECT BY rn = PRIOR rn + 1 
/ 
--footerinfo: 
select 
'            ]' ||chr(10)||
'	}' ||chr(10)||
'}' 
from dual;
spool off

--
spool TargetCount.json
--headerinfo: 
select	
    '{' ||chr(10)||
	'    "root": {' ||chr(10)||
	'      "Target Count": [' 
--	'  "WQXEntity": "' ||chr(10)||
--	'    "WQXElement": " domainEntity="Target Count" schemaElement="TargetCount" 
--rowCount="'||(select count(*) from wqx.result_target_count)||'" maxLastChangeDate="'||to_char(cast((select 
--max(rtc_last_change_date) from wqx.result_target_count) as timestamp with LOCAL time zone), 
--'YYYY-MM-DD"T"HH24:MI:SSTZR')||'" }'
from dual;
SELECT 
--SELECT replace( LTRIM(MAX(SYS_CONNECT_BY_PATH(elementrow, '|')), '|'), '|', ','||chr(10)) as elementrecord
	case when rn=1 then '' else ',' end as rowdelimiter, 
	elementrow 
from ( SELECT rownum rn, elementrow
FROM (SELECT 
	'      {' ||chr(10)|| 
	'      "Domain": "Target Count(TargetCount)",' ||chr(10)||
	'      "UID": "'||rtc_uid||'",' ||chr(10)||
	'      "Name": "'|| replace(replace(translate(rtc_name,'"\'||chr(10),'^/|'),'^','\"'),'/','\')||'",' ||chr(10)||
	'      "Description": "'|| replace(replace(translate(rtc_desc,'"\'||chr(10),'^/|'),'^','\"'),'/','\')||'",' ||chr(10)||
	'      "LastChangeDate": "'||to_char(cast(rtc_last_change_date as timestamp with LOCAL time zone), 'YYYY-MM-DD"T"HH24:MI:SS TZR') ||'"'||chr(10)|| 
	'      }' 
 as elementrow 
from wqx.result_target_count 
order by rtc_name
) 
) 
--START WITH rn = 1 
--CONNECT BY rn = PRIOR rn + 1 
/ 
select 
'            ]' ||chr(10)|| 
'	}' ||chr(10)|| 
'}' 
from dual;
spool off

--
spool ResultTemperatureBasis.json
--headerinfo: 
select	
    '{' ||chr(10)||
	'    "root": {' ||chr(10)||
	'      "Result Temperature Basis": [' 
--	'  "WQXEntity": "' ||chr(10)||
--	'    "WQXElement": " domainEntity="Result Temperature Basis" 
--schemaElement="ResultTemperatureBasisText" rowCount="'||(select count(*) from 
--wqx.result_temperature_basis)||'" maxLastChangeDate="'||to_char(cast((select max(rtmpb_last_change_date) 
--from wqx.result_temperature_basis) as timestamp with LOCAL time zone), 'YYYY-MM-DD"T"HH24:MI:SSTZR')||'" }'
from dual;
SELECT 
--SELECT replace( LTRIM(MAX(SYS_CONNECT_BY_PATH(elementrow, '|')), '|'), '|', ','||chr(10)) as elementrecord
	case when rn=1 then '' else ',' end as rowdelimiter, 
	elementrow 
from ( SELECT rownum rn, elementrow
FROM (SELECT 
	'      {' ||chr(10)|| 
	'      "Domain": "Result Temperature Basis(ResultTemperatureBasisText)",' ||chr(10)||
	'      "UID": "'||rtmpb_uid||'",' ||chr(10)||
	'      "Name": "'|| replace(replace(translate(rtmpb_name,'"\'||chr(10),'^/|'),'^','\"'),'/','\')||'",' ||chr(10)||
	'      "Description": "'|| replace(replace(translate(rtmpb_desc,'"\'||chr(10),'^/|'),'^','\"'),'/','\')||'",' ||chr(10)||
	'      "LastChangeDate": "'||to_char(cast(rtmpb_last_change_date as timestamp with LOCAL time zone), 'YYYY-MM-DD"T"HH24:MI:SS TZR') ||'"'||chr(10)|| 
	'      }' 
 as elementrow 
from wqx.result_temperature_basis 
order by rtmpb_name
) 
) 
--START WITH rn = 1 
--CONNECT BY rn = PRIOR rn + 1 
/ 
--footerinfo: 
select 
'            ]' ||chr(10)||
'	}' ||chr(10)||
'}' 
from dual;
spool off

--
spool ResultTimeBasis.json
--headerinfo: 
select	
    '{' ||chr(10)||
	'    "root": {' ||chr(10)||
	'      "Result Time Basis": [' 
--	'  "WQXEntity": "' ||chr(10)||
--	'    "WQXElement": " domainEntity="Result Time Basis" schemaElement="ResultTimeBasisText" 
--rowCount="'||(select count(*) from wqx.result_time_basis)||'" maxLastChangeDate="'||to_char(cast((select 
--max(rtimb_last_change_date) from wqx.result_time_basis) as timestamp with LOCAL time zone), 
--'YYYY-MM-DD"T"HH24:MI:SSTZR')||'" }'
from dual;
SELECT 
--SELECT replace( LTRIM(MAX(SYS_CONNECT_BY_PATH(elementrow, '|')), '|'), '|', ','||chr(10)) as elementrecord
	case when rn=1 then '' else ',' end as rowdelimiter, 
	elementrow 
from ( SELECT rownum rn, elementrow
FROM (SELECT 
	'      {' ||chr(10)|| 
	'      "Domain": "Result Time Basis(ResultTimeBasisText)",' ||chr(10)||
	'      "UID": "'||rtimb_uid||'",' ||chr(10)||
	'      "Name": "'|| replace(replace(translate(rtimb_name,'"\'||chr(10),'^/|'),'^','\"'),'/','\')||'",' ||chr(10)||
	'      "Description": "'|| replace(replace(translate(rtimb_desc,'"\'||chr(10),'^/|'),'^','\"'),'/','\')||'",' ||chr(10)||
	'      "LastChangeDate": "'||to_char(cast(rtimb_last_change_date as timestamp with LOCAL time zone), 'YYYY-MM-DD"T"HH24:MI:SS TZR') ||'"'||chr(10)|| 
	'      }' 
 as elementrow 
from wqx.result_time_basis 
order by rtimb_name
) 
) 
--START WITH rn = 1 
--CONNECT BY rn = PRIOR rn + 1 
/ 
--footerinfo: 
select 
'            ]' ||chr(10)||
'	}' ||chr(10)||
'}' 
from dual;
spool off

--
spool ResultValueType.json
--headerinfo: 
select	
    '{' ||chr(10)||
	'    "root": {' ||chr(10)||
	'      "Result Value Type": [' 
--	'  "WQXEntity": "' ||chr(10)||
--	'    "WQXElement": " domainEntity="Result Value Type" schemaElement="ResultValueTypeName" 
--rowCount="'||(select count(*) from wqx.result_value_type)||'" maxLastChangeDate="'||to_char(cast((select 
--max(rvtyp_last_change_date) from wqx.result_value_type) as timestamp with LOCAL time zone), 
--'YYYY-MM-DD"T"HH24:MI:SSTZR')||'" }'
from dual;
SELECT 
--SELECT replace( LTRIM(MAX(SYS_CONNECT_BY_PATH(elementrow, '|')), '|'), '|', ','||chr(10)) as elementrecord
	case when rn=1 then '' else ',' end as rowdelimiter, 
	elementrow 
from ( SELECT rownum rn, elementrow
FROM (SELECT 
	'      {' ||chr(10)|| 
	'      "Domain": "Result Value Type(ResultValueTypeName)",' ||chr(10)||
	'      "UID": "'||rvtyp_uid||'",' ||chr(10)||
	'      "Name": "'|| replace(replace(translate(rvtyp_name,'"\'||chr(10),'^/|'),'^','\"'),'/','\')||'",' ||chr(10)||
	'      "Description": "'|| replace(replace(translate(rvtyp_desc,'"\'||chr(10),'^/|'),'^','\"'),'/','\')||'",' ||chr(10)||
	'      "LastChangeDate": "'||to_char(cast(rvtyp_last_change_date as timestamp with LOCAL time zone), 'YYYY-MM-DD"T"HH24:MI:SS TZR') ||'"'||chr(10)|| 
	'      }' 
 as elementrow 
from wqx.result_value_type 
order by rvtyp_name
) 
) 
--START WITH rn = 1 
--CONNECT BY rn = PRIOR rn + 1 
/ 
--footerinfo: 
select 
'            ]' ||chr(10)||
'	}' ||chr(10)||
'}' 
from dual;
spool off

--
spool ResultWeightBasis.json
--headerinfo: 
select	
    '{' ||chr(10)||
	'    "root": {' ||chr(10)||
	'      "Result Weight Basis": [' 
--	'  "WQXEntity": "' ||chr(10)||
--	'    "WQXElement": " domainEntity="Result Weight Basis" schemaElement="ResultWeightBasisText" 
--rowCount="'||(select count(*) from wqx.result_weight_basis)||'" maxLastChangeDate="'||to_char(cast((select 
--max(rwbas_last_change_date) from wqx.result_weight_basis) as timestamp with LOCAL time zone), 
--'YYYY-MM-DD"T"HH24:MI:SSTZR')||'" }'
from dual;
SELECT 
--SELECT replace( LTRIM(MAX(SYS_CONNECT_BY_PATH(elementrow, '|')), '|'), '|', ','||chr(10)) as elementrecord
	case when rn=1 then '' else ',' end as rowdelimiter, 
	elementrow 
from ( SELECT rownum rn, elementrow
FROM (SELECT 
	'      {' ||chr(10)|| 
	'      "Domain": "Result Weight Basis(ResultWeightBasisText)",' ||chr(10)||
	'      "UID": "'||rwbas_uid||'",' ||chr(10)||
	'      "Name": "'|| replace(replace(translate(rwbas_name,'"\'||chr(10),'^/|'),'^','\"'),'/','\')||'",' ||chr(10)||
	'      "Description": "'|| replace(replace(translate(rwbas_desc,'"\'||chr(10),'^/|'),'^','\"'),'/','\')||'",' ||chr(10)||
	'      "LastChangeDate": "'||to_char(cast(rwbas_last_change_date as timestamp with LOCAL time zone), 'YYYY-MM-DD"T"HH24:MI:SS TZR') ||'"'||chr(10)|| 
	'      }' 
 as elementrow 
from wqx.result_weight_basis 
order by rwbas_name
) 
) 
--START WITH rn = 1 
--CONNECT BY rn = PRIOR rn + 1 
/ 
--footerinfo: 
select 
'            ]' ||chr(10)||
'	}' ||chr(10)||
'}' 
from dual;
spool off

--
spool SampleCollectionMethodContext.json
--headerinfo: 
select	
    '{' ||chr(10)||
	'    "root": {' ||chr(10)||
	'      "Sample Collection Method Context": [' 
--	'  "WQXEntity": "' ||chr(10)||
--	'    "WQXElement": " domainEntity="Sample Collection Method Context" 
--schemaElement="SampleCollectionMethod/MethodIdentifierContext" rowCount="'||(select count(*) from 
--wqx.sample_collect_method_context)||'" maxLastChangeDate="'||to_char(cast((select 
--max(scmctx_last_change_date) from wqx.sample_collect_method_context) as timestamp with LOCAL time zone), 
--'YYYY-MM-DD"T"HH24:MI:SSTZR')||'" }'
from dual;
SELECT 
--SELECT replace( LTRIM(MAX(SYS_CONNECT_BY_PATH(elementrow, '|')), '|'), '|', ','||chr(10)) as elementrecord
	case when rn=1 then '' else ',' end as rowdelimiter, 
	elementrow 
from ( SELECT rownum rn, elementrow
FROM (SELECT 
	'      {' ||chr(10)|| 
	'      "Domain": "Sample Collection Method Context(MethodIdentifierContext)",' ||chr(10)||
	'      "UID": "'||scmctx_uid||'",' ||chr(10)||
	'      "Context": "'|| replace(replace(translate(scmctx_cd,'"\'||chr(10),'^/|'),'^','\"'),'/','\')||'",' ||chr(10)||
	'      "Name": "'|| replace(replace(translate(scmctx_name,'"\'||chr(10),'^/|'),'^','\"'),'/','\')||'",' ||chr(10)||
	'      "Description": "'|| replace(replace(translate(scmctx_desc,'"\'||chr(10),'^/|'),'^','\"'),'/','\')||'",' ||chr(10)||
	'      "LastChangeDate": "'||to_char(cast(scmctx_last_change_date as timestamp with LOCAL time zone), 'YYYY-MM-DD"T"HH24:MI:SS TZR') ||'"'||chr(10)|| 
	'      }' 
 as elementrow 
from wqx.sample_collect_method_context 
order by scmctx_name
) 
) 
--START WITH rn = 1 
--CONNECT BY rn = PRIOR rn + 1 
/ 
--footerinfo: 
select 
'            ]' ||chr(10)||
'	}' ||chr(10)||
'}' 
from dual;
spool off

--
spool SampleCollectionEquipment.json
--headerinfo: 
select	
    '{' ||chr(10)||
	'    "root": {' ||chr(10)||
	'      "Sample Collection Equipment": [' 
--	'  "WQXEntity": "' ||chr(10)||
--	'    "WQXElement": " domainEntity="Sample Collection Equipment" 
--schemaElement="SampleCollectionEquipmentName" rowCount="'||(select count(*) from 
--wqx.sample_collection_equip)||'" maxLastChangeDate="'||to_char(cast((select max(sceqp_last_change_date) 
--from wqx.sample_collection_equip) as timestamp with LOCAL time zone), 'YYYY-MM-DD"T"HH24:MI:SSTZR')||'" }'
from dual;
SELECT 
--SELECT replace( LTRIM(MAX(SYS_CONNECT_BY_PATH(elementrow, '|')), '|'), '|', ','||chr(10)) as elementrecord
	case when rn=1 then '' else ',' end as rowdelimiter, 
	elementrow 
from ( SELECT rownum rn, elementrow
FROM (SELECT 
	'      {' ||chr(10)|| 
	'      "Domain": "Sample Collection Equipment(SampleCollectionEquipmentName)",' ||chr(10)||
	'      "UID": "'||sceqp_uid||'",' ||chr(10)||
	'      "Name": "'|| replace(replace(translate(sceqp_name,'"\'||chr(10),'^/|'),'^','\"'),'/','\')||'",' ||chr(10)||
	'      "Description": "'|| replace(replace(translate(sceqp_desc,'"\'||chr(10),'^/|'),'^','\"'),'/','\')||'",' ||chr(10)||
	'      "Abbreviation": "'||sceqp_abbrev||'",' ||chr(10)||
	'      "LastChangeDate": "'||to_char(cast(sceqp_last_change_date as timestamp with LOCAL time zone), 'YYYY-MM-DD"T"HH24:MI:SS TZR') ||'"'||chr(10)|| 
	'      }' 
 as elementrow 
from wqx.sample_collection_equip 
order by sceqp_name
) 
) 
--START WITH rn = 1 
--CONNECT BY rn = PRIOR rn + 1 
/ 
--footerinfo: 
select 
'            ]' ||chr(10)||
'	}' ||chr(10)||
'}' 
from dual;
spool off

--
spool SampleCollectionEquipmentType.json
--headerinfo: 
select	
    '{' ||chr(10)||
	'    "root": {' ||chr(10)||
	'      "Sample Collection Equipment Type": [' 
--	'  "WQXEntity": "' ||chr(10)||
--	'    "WQXElement": " domainEntity="Sample Collection Equipment Type" 
--schemaElement="SampleCollectionEquipmentTypeName" rowCount="'||(select count(*) from 
--wqx.sample_collection_equip_type)||'" maxLastChangeDate="'||to_char(cast((select 
--max(scetyp_last_change_date) from wqx.sample_collection_equip_type) as timestamp with LOCAL time zone), 
--'YYYY-MM-DD"T"HH24:MI:SSTZR')||'" }'
from dual;
SELECT 
--SELECT replace( LTRIM(MAX(SYS_CONNECT_BY_PATH(elementrow, '|')), '|'), '|', ','||chr(10)) as elementrecord
	case when rn=1 then '' else ',' end as rowdelimiter, 
	elementrow 
from ( SELECT rownum rn, elementrow
FROM (SELECT 
	'      {' ||chr(10)|| 
	'      "Domain": "Sample Collection Equipment Type(SampleCollectionEquipmentTypeName)",' ||chr(10)||
	'      "UID": "'||scetyp_uid||'",' ||chr(10)||
	'      "Name": "'|| replace(replace(translate(scetyp_name,'"\'||chr(10),'^/|'),'^','\"'),'/','\')||'",' ||chr(10)||
	'      "Description": "'|| replace(replace(translate(scetyp_desc,'"\'||chr(10),'^/|'),'^','\"'),'/','\')||'",' ||chr(10)||
	'      "LastChangeDate": "'||to_char(cast(scetyp_last_change_date as timestamp with LOCAL time zone), 'YYYY-MM-DD"T"HH24:MI:SS TZR') ||'"'||chr(10)|| 
	'      }' 
 as elementrow 
from wqx.sample_collection_equip_type 
order by scetyp_name
) 
) 
--START WITH rn = 1 
--CONNECT BY rn = PRIOR rn + 1 
/ 
--footerinfo: 
select 
'            ]' ||chr(10)||
'	}' ||chr(10)||
'}' 
from dual;
spool off

--
spool SampleCollectionMethod.json
--headerinfo: 
select	
    '{' ||chr(10)||
	'    "root": {' ||chr(10)||
	'      "Sample Collection Method": [' 
--	'  "WQXEntity": "' ||chr(10)||
--	'    "WQXElement": " domainEntity="Sample Collection Method" 
--schemaElement="SampleCollectionMethod/MethodIdentifier" rowCount="'||(select count(*) from 
--wqx.sample_collection_method where org_uid is null)||'" maxLastChangeDate="'||to_char(cast((select max(scmth_last_change_date) 
--from wqx.sample_collection_method where org_uid is null) as timestamp with LOCAL time zone), 'YYYY-MM-DD"T"HH24:MI:SSTZR')||'" }'
from dual;
SELECT 
--SELECT replace( LTRIM(MAX(SYS_CONNECT_BY_PATH(elementrow, '|')), '|'), '|', ','||chr(10)) as elementrecord
	case when rn=1 then '' else ',' end as rowdelimiter, 
	elementrow 
from ( SELECT rownum rn, elementrow
FROM (SELECT 
	'      {' ||chr(10)|| 
	'      "Domain": "Sample Collection Method(MethodIdentifier)",' ||chr(10)||
	'      "UID": "'||scmth_uid||'",' ||chr(10)||
	'      "Identifier": "'|| replace(replace(translate(scmth_id,'"\'||chr(10),'^/|'),'^','\"'),'/','\')||'",' ||chr(10)||
	'      "Context": "'|| replace(replace(translate(scmctx_cd,'"\'||chr(10),'^/|'),'^','\"'),'/','\')||'",' ||chr(10)||
	'      "Name": "'|| replace(replace(translate(scmth_name,'"\'||chr(10),'^/|'),'^','\"'),'/','\')||'",' ||chr(10)||
	'      "QualifierType": "'|| replace(replace(translate(scmth_qual_type,'"\'||chr(10),'^/|'),'^','\"'),'/','\')||'",' ||chr(10)||
	'      "Description": "'||
	substr(replace(replace(translate(scmth_desc,'"\'||chr(10),'^/|'),'^','\"'),'/','\'), 1, 3000) ||	'",' ||chr(10)|| 
	'      "LastChangeDate": "'||to_char(cast(scmth_last_change_date as timestamp with LOCAL time zone), 'YYYY-MM-DD"T"HH24:MI:SS TZR') ||'"'||chr(10)|| 
	'      }' 
 as elementrow 
from wqx.sample_collection_method scm, wqx.sample_collect_method_context scmc
where scm.scmctx_uid=scmc.scmctx_uid and scm.org_uid is null
order by scmth_name
) 
) 
--START WITH rn = 1 
--CONNECT BY rn = PRIOR rn + 1 
/ 
--footerinfo: 
select 
'            ]' ||chr(10)||
'	}' ||chr(10)||
'}' 
from dual;
spool off

--
spool SampleFraction.json
--headerinfo: 
select	
    '{' ||chr(10)||
	'    "root": {' ||chr(10)||
	'      "Sample Fraction": [' 
--	'  "WQXEntity": "' ||chr(10)||
--	'    "WQXElement": " domainEntity="Sample Fraction" schemaElement="ResultSampleFractionText" 
--rowCount="'||(select count(*) from wqx.sample_fraction)||'" maxLastChangeDate="'||to_char(cast((select 
--max(smfrc_last_change_date) from wqx.sample_fraction) as timestamp with LOCAL time zone), 
--'YYYY-MM-DD"T"HH24:MI:SSTZR')||'" }'
from dual;
SELECT 
--SELECT replace( LTRIM(MAX(SYS_CONNECT_BY_PATH(elementrow, '|')), '|'), '|', ','||chr(10)) as elementrecord
	case when rn=1 then '' else ',' end as rowdelimiter, 
	elementrow 
from ( SELECT rownum rn, elementrow
FROM (SELECT 
	'      {' ||chr(10)|| 
	'      "Domain": "Sample Fraction(ResultSampleFractionText)",' ||chr(10)||
	'      "UID": "'||smfrc_uid||'",' ||chr(10)||
	'      "Name": "'|| replace(replace(translate(smfrc_name,'"\'||chr(10),'^/|'),'^','\"'),'/','\')||'",' ||chr(10)||
	'      "Description": "'|| replace(replace(translate(smfrc_desc,'"\'||chr(10),'^/|'),'^','\"'),'/','\')||'",' ||chr(10)||
	'      "LastChangeDate": "'||to_char(cast(smfrc_last_change_date as timestamp with LOCAL time zone), 'YYYY-MM-DD"T"HH24:MI:SS TZR') ||'"'||chr(10)|| 
	'      }' 
 as elementrow 
from wqx.sample_fraction 
order by smfrc_name
) 
) 
--START WITH rn = 1 
--CONNECT BY rn = PRIOR rn + 1 
/ 
--footerinfo: 
select 
'            ]' ||chr(10)||
'	}' ||chr(10)||
'}' 
from dual;
spool off

--
spool SampleTissueAnatomy.json
--headerinfo: 
select	
    '{' ||chr(10)||
	'    "root": {' ||chr(10)||
	'      "Sample Tissue Anatomy": [' 
--	'  "WQXEntity": "' ||chr(10)||
--	'    "WQXElement": " domainEntity="Sample Tissue Anatomy" schemaElement="SampleTissueAnatomyName" 
--rowCount="'||(select count(*) from wqx.sample_tissue_anatomy)||'" 
--maxLastChangeDate="'||to_char(cast((select max(stant_last_change_date) from wqx.sample_tissue_anatomy) as 
--timestamp with LOCAL time zone), 'YYYY-MM-DD"T"HH24:MI:SSTZR')||'" }'
from dual;
SELECT 
--SELECT replace( LTRIM(MAX(SYS_CONNECT_BY_PATH(elementrow, '|')), '|'), '|', ','||chr(10)) as elementrecord
	case when rn=1 then '' else ',' end as rowdelimiter, 
	elementrow 
from ( SELECT rownum rn, elementrow
FROM (SELECT 
	'      {' ||chr(10)|| 
	'      "Domain": "Sample Tissue Anatomy(SampleTissueAnatomyName)",' ||chr(10)||
	'      "UID": "'||stant_uid||'",' ||chr(10)||
	'      "Name": "'|| replace(replace(translate(stant_name,'"\'||chr(10),'^/|'),'^','\"'),'/','\')||'",' ||chr(10)||
	'      "Description": "'|| replace(replace(translate(stant_desc,'"\'||chr(10),'^/|'),'^','\"'),'/','\')||'",' ||chr(10)||
	'      "LastChangeDate": "'||to_char(cast(stant_last_change_date as timestamp with LOCAL time zone), 'YYYY-MM-DD"T"HH24:MI:SS TZR') ||'"'||chr(10)|| 
	'      }' 
 as elementrow 
from wqx.sample_tissue_anatomy 
order by stant_name
) 
) 
--START WITH rn = 1 
--CONNECT BY rn = PRIOR rn + 1 
/ 
--footerinfo: 
select 
'            ]' ||chr(10)||
'	}' ||chr(10)||
'}' 
from dual;
spool off

--
spool SampleDesignType.json
--headerinfo: 
select	
    '{' ||chr(10)||
	'    "root": {' ||chr(10)||
	'      "Sample Design Type": [' 
--	'  "WQXEntity": "' ||chr(10)||
--	'    "WQXElement": " domainEntity="Sample Design Type" schemaElement="SamplingDesignTypeCode" 
--rowCount="'||(select count(*) from wqx.sampling_design_type)||'" maxLastChangeDate="'||to_char(cast((select 
--max(sdtyp_last_change_date) from wqx.sampling_design_type) as timestamp with LOCAL time zone), 
--'YYYY-MM-DD"T"HH24:MI:SSTZR')||'" }'
from dual;
SELECT 
--SELECT replace( LTRIM(MAX(SYS_CONNECT_BY_PATH(elementrow, '|')), '|'), '|', ','||chr(10)) as elementrecord
	case when rn=1 then '' else ',' end as rowdelimiter, 
	elementrow 
from ( SELECT rownum rn, elementrow
FROM (SELECT 
	'      {' ||chr(10)|| 
	'      "Domain": "Sample Design Type(SamplingDesignTypeCode)",' ||chr(10)||
	'      "UID": "'||sdtyp_uid||'",' ||chr(10)||
	'      "Code": "'|| replace(replace(translate(sdtyp_cd,'"\'||chr(10),'^/|'),'^','\"'),'/','\')||'",' ||chr(10)||
	'      "Description": "'|| replace(replace(translate(sdtyp_desc,'"\'||chr(10),'^/|'),'^','\"'),'/','\')||'",' ||chr(10)||
	'      "LastChangeDate": "'||to_char(cast(sdtyp_last_change_date as timestamp with LOCAL time zone), 'YYYY-MM-DD"T"HH24:MI:SS TZR') ||'"'||chr(10)|| 
	'      }' 
 as elementrow 
from wqx.sampling_design_type 
order by sdtyp_cd
) 
) 
--START WITH rn = 1 
--CONNECT BY rn = PRIOR rn + 1 
/ 
--footerinfo: 
select 
'            ]' ||chr(10)||
'	}' ||chr(10)||
'}' 
from dual;
spool off

--
spool State.json
--headerinfo: 
select	
    '{' ||chr(10)||
	'    "root": {' ||chr(10)||
	'      "State": [' 
--	'  "WQXEntity": "' ||chr(10)||
--	'    "WQXElement": " domainEntity="State" schemaElement="StateCode" rowCount="'||(select count(*) 
--from wqx.state)||'" maxLastChangeDate="'||to_char(cast((select max(st_last_change_date) from wqx.state) as 
--timestamp with LOCAL time zone), 'YYYY-MM-DD"T"HH24:MI:SSTZR')||'" }'
from dual;
SELECT 
--SELECT replace( LTRIM(MAX(SYS_CONNECT_BY_PATH(elementrow, '|')), '|'), '|', ','||chr(10)) as elementrecord
	case when rn=1 then '' else ',' end as rowdelimiter, 
	elementrow 
from ( SELECT rownum rn, elementrow
FROM (SELECT 
	'      {' ||chr(10)|| 
	'      "Domain": "State(StateCode)",' ||chr(10)||
	'      "UID": "'||st_uid||'",' ||chr(10)||
	'      "Country": "'|| replace(replace(translate(cntry_name,'"\'||chr(10),'^/|'),'^','\"'),'/','\')||'",' ||chr(10)||
	'      "Code": "'|| replace(replace(translate(st_cd,'"\'||chr(10),'^/|'),'^','\"'),'/','\')||'",' ||chr(10)||
	'      "Name": "'|| replace(replace(translate(st_name,'"\'||chr(10),'^/|'),'^','\"'),'/','\')||'",' ||chr(10)||
	'      "FIPSCode": "'||st_fips_cd||'",' ||chr(10)||
	'      "EPARegion": "'|| replace(replace(translate(er_name,'"\'||chr(10),'^/|'),'^','\"'),'/','\')||'",' ||chr(10)||
	'      "LastChangeDate": "'||to_char(cast(st_last_change_date as timestamp with LOCAL time zone), 'YYYY-MM-DD"T"HH24:MI:SS TZR') ||'"'||chr(10)|| 
	'      }' 
 as elementrow 
from wqx.state s, wqx.country c, wqx.epa_region er
where s.cntry_uid=c.cntry_uid and s.er_uid=er.er_uid
order by st_name
) 
) 
--START WITH rn = 1 
--CONNECT BY rn = PRIOR rn + 1 
/ 
--footerinfo: 
select 
'            ]' ||chr(10)||
'	}' ||chr(10)||
'}' 
from dual;
spool off

--
spool Taxon.json
--headerinfo: 
select	
    '{' ||chr(10)||
	'    "root": {' ||chr(10)||
	'      "Taxon": [' 
--	'  "WQXEntity": "' ||chr(10)||
--	'    "WQXElement": " domainEntity="Taxon" schemaElement="SubjectTaxonomicName" rowCount="'||(select count(*) 
--from wqx.taxon)||'" maxLastChangeDate="'||to_char(cast((select max(tax_last_change_date) from wqx.taxon) 
--as timestamp with LOCAL time zone), 'YYYY-MM-DD"T"HH24:MI:SSTZR')||'" }'
from dual;
SELECT 
--SELECT replace( LTRIM(MAX(SYS_CONNECT_BY_PATH(elementrow, '|')), '|'), '|', ','||chr(10)) as elementrecord
	case when rn=1 then '' else ',' end as rowdelimiter, 
	elementrow 
from ( SELECT rownum rn, elementrow
FROM (SELECT 
	'      {' ||chr(10)|| 
	'      "Domain": "Taxon(SubjectTaxonomicName)",' ||chr(10)||
	'      "UID": "'||t.tax_uid||'",' ||chr(10)||
	'      "Name": "'|| replace(replace(translate(t.tax_name,'"\'||chr(10),'^/|'),'^','\"'),'/','\')||'",' ||chr(10)||
	'      "RankName": "'|| replace(replace(translate(txrnk_name,'"\'||chr(10),'^/|'),'^','\"'),'/','\')||'",' ||chr(10)||
	'      "ExternalID": "'||t.tax_extrnl_id||'",' ||chr(10)||
	'      "STORET_ID": "'||t.tax_storet_id||'",' ||chr(10)||
	'      "SRS_ID": "'||t.tax_srs_id||'",' ||chr(10)||
	'      "KingdomName": "'|| replace(replace(translate(tk.tax_name,'"\'||chr(10),'^/|'),'^','\"'),'/','\')||'",' ||chr(10)||
	'      "GroupName": "'|| replace(replace(translate(taxgrp_name,'"\'||chr(10),'^/|'),'^','\"'),'/','\')||'",' ||chr(10)||
	'      "Status": "'||dvsta_name||'",' ||chr(10)||
	'      "Description": "'|| replace(replace(translate(t.tax_description,'"\'||chr(10),'^/|'),'^','\"'),'/','\')||'",' ||chr(10)||
	'      "LastChangeDate": "'||to_char(cast(t.tax_last_change_date as timestamp with LOCAL time zone), 'YYYY-MM-DD"T"HH24:MI:SS TZR') ||'"'||chr(10)|| 
	'      }' 
 as elementrow 
from wqx.taxon t, wqx.taxon tk, wqx.taxon_rank tr, wqx.taxon_group tg, wqx.domain_value_status dvs
where t.tax_uid_kingdom=tk.tax_uid and t.txrnk_uid=tr.txrnk_uid and t.taxgrp_uid=tg.taxgrp_uid and t.dvsta_uid=dvs.dvsta_uid
order by t.tax_name
) 
) 
--START WITH rn = 1 
--CONNECT BY rn = PRIOR rn + 1 
/ 
--footerinfo: 
select 
'            ]' ||chr(10)||
'	}' ||chr(10)||
'}' 
from dual;
spool off

--
spool TaxonAlias.json
--headerinfo: 
select	
    '{' ||chr(10)||
	'    "root": {' ||chr(10)||
	'      "Taxon Alias": [' 
--	'  "WQXEntity": "' ||chr(10)||
--	'    "WQXElement": " domainEntity="Taxon Alias" schemaElement="SubjectTaxonomicName" rowCount="'||(select 
--count(*) from wqx.taxon_alias)||'" maxLastChangeDate="'||to_char(cast((select max(taxals_last_change_date) 
--from wqx.taxon_alias) as timestamp with LOCAL time zone), 'YYYY-MM-DD"T"HH24:MI:SSTZR')||'" }'
from dual;
SELECT 
--SELECT replace( LTRIM(MAX(SYS_CONNECT_BY_PATH(elementrow, '|')), '|'), '|', ','||chr(10)) as elementrecord
	case when rn=1 then '' else ',' end as rowdelimiter, 
	elementrow 
from ( SELECT rownum rn, elementrow
FROM (SELECT 
	'      {' ||chr(10)|| 
	'      "Domain": "Taxon Alias(SubjectTaxonomicName)",' ||chr(10)||
	'      "UID": "'||taxals_uid||'",' ||chr(10)||
	'      "AliasType": "'|| replace(replace(translate(alstyp_name,'"\'||chr(10),'^/|'),'^','\"'),'/','\')||'",' ||chr(10)||
	'      "AliasName": "'|| replace(replace(translate(taxals_name,'"\'||chr(10),'^/|'),'^','\"'),'/','\')||'",' ||chr(10)||
	'      "TaxonName": "'|| replace(replace(translate(tax_name,'"\'||chr(10),'^/|'),'^','\"'),'/','\')||'",' ||chr(10)||
	'      "Description": "'|| replace(replace(translate(taxals_description,'"\'||chr(10),'^/|'),'^','\"'),'/','\')||'",' ||chr(10)||
	'      "LastChangeDate": "'||to_char(cast(taxals_last_change_date as timestamp with LOCAL time zone), 'YYYY-MM-DD"T"HH24:MI:SS TZR') ||'"'||chr(10)|| 
	'      }' 
 as elementrow 
from wqx.taxon_alias ta, wqx.taxon t, wqx.alias_type at
where ta.alstyp_uid=at.alstyp_uid and ta.tax_uid=t.tax_uid
order by alstyp_name, taxals_name
) 
) 
--START WITH rn = 1 
--CONNECT BY rn = PRIOR rn + 1 
/ 
--footerinfo: 
select 
'            ]' ||chr(10)||
'	}' ||chr(10)||
'}' 
from dual;
spool off

--
spool TaxonGroup.json
--headerinfo: 
select	
    '{' ||chr(10)||
	'    "root": {' ||chr(10)||
	'      "Taxon Group": [' 
--	'  "WQXEntity": "' ||chr(10)||
--	'    "WQXElement": " domainEntity="Taxon Group" schemaElement="TaxonGroupName" rowCount="'||(select 
--count(*) from wqx.taxon_group)||'" maxLastChangeDate="'||to_char(cast((select max(taxgrp_last_change_date) 
--from wqx.taxon_group) as timestamp with LOCAL time zone), 'YYYY-MM-DD"T"HH24:MI:SSTZR')||'" }'
from dual;
SELECT 
--SELECT replace( LTRIM(MAX(SYS_CONNECT_BY_PATH(elementrow, '|')), '|'), '|', ','||chr(10)) as elementrecord
	case when rn=1 then '' else ',' end as rowdelimiter, 
	elementrow 
from ( SELECT rownum rn, elementrow
FROM (SELECT 
	'      {' ||chr(10)|| 
	'      "Domain": "Taxon Group(TaxonGroupName)",' ||chr(10)||
	'      "UID": "'||taxgrp_uid||'",' ||chr(10)||
	'      "Name": "'|| replace(replace(translate(taxgrp_name,'"\'||chr(10),'^/|'),'^','\"'),'/','\')||'",' ||chr(10)||
	'      "Description": "'|| replace(replace(translate(taxgrp_desc,'"\'||chr(10),'^/|'),'^','\"'),'/','\')||'",' ||chr(10)||
	'      "LastChangeDate": "'||to_char(cast(taxgrp_last_change_date as timestamp with LOCAL time zone), 'YYYY-MM-DD"T"HH24:MI:SS TZR') ||'"'||chr(10)|| 
	'      }' 
 as elementrow 
from wqx.taxon_group 
order by taxgrp_name
) 
) 
--START WITH rn = 1 
--CONNECT BY rn = PRIOR rn + 1 
/ 
--footerinfo: 
select 
'            ]' ||chr(10)||
'	}' ||chr(10)||
'}' 
from dual;
spool off

--
spool TaxonRank.json
--headerinfo: 
select	
    '{' ||chr(10)||
	'    "root": {' ||chr(10)||
	'      "Taxon Rank": [' 
--	'  "WQXEntity": "' ||chr(10)||
--	'    "WQXElement": " domainEntity="Taxon Rank" schemaElement="TaxonRankName" rowCount="'||(select 
--count(*) from wqx.taxon_rank)||'" maxLastChangeDate="'||to_char(cast((select max(txrnk_last_change_date) 
--from wqx.taxon_rank) as timestamp with LOCAL time zone), 'YYYY-MM-DD"T"HH24:MI:SSTZR')||'" }'
from dual;
SELECT 
--SELECT replace( LTRIM(MAX(SYS_CONNECT_BY_PATH(elementrow, '|')), '|'), '|', ','||chr(10)) as elementrecord
	case when rn=1 then '' else ',' end as rowdelimiter, 
	elementrow 
from ( SELECT rownum rn, elementrow
FROM (SELECT 
	'      {' ||chr(10)|| 
	'      "Domain": "Taxon Rank(TaxonRankName)",' ||chr(10)||
	'      "UID": "'||txrnk_uid||'",' ||chr(10)||
	'      "Name": "'|| replace(replace(translate(txrnk_name,'"\'||chr(10),'^/|'),'^','\"'),'/','\')||'",' ||chr(10)||
--	'      "Description": "'||txrnk_desc||'",' ||chr(10)||
	'      "LastChangeDate": "'||to_char(cast(txrnk_last_change_date as timestamp with LOCAL time zone), 'YYYY-MM-DD"T"HH24:MI:SS TZR') ||'"'||chr(10)|| 
	'      }' 
 as elementrow 
from wqx.taxon_rank 
order by txrnk_name
) 
) 
--START WITH rn = 1 
--CONNECT BY rn = PRIOR rn + 1 
/ 
--footerinfo: 
select 
'            ]' ||chr(10)||
'	}' ||chr(10)||
'}' 
from dual;
spool off

--
spool TaxonParent.json
--headerinfo: 
select	
    '{' ||chr(10)||
	'    "root": {' ||chr(10)||
	'      "Taxon Parent": [' 
--	'  "WQXEntity": "' ||chr(10)||
--	'    "WQXElement": " domainEntity="Taxon Parent" schemaElement="TaxonParentName/Name" 
--rowCount="'||(select count(*) from wqx.taxon_rank)||'" maxLastChangeDate="'||to_char(cast((select 
--max(txrnk_last_change_date) from wqx.taxon_rank) as timestamp with LOCAL time zone), 
--'YYYY-MM-DD"T"HH24:MI:SSTZR')||'" }'
from dual;
SELECT 
--SELECT replace( LTRIM(MAX(SYS_CONNECT_BY_PATH(elementrow, '|')), '|'), '|', ','||chr(10)) as elementrecord
	case when rn=1 then '' else ',' end as rowdelimiter, 
	elementrow 
from ( SELECT rownum rn, elementrow
FROM (SELECT 
	'      {' ||chr(10)|| 
	'      "Domain": "Taxon Parent(SubjectTaxonomicName)",' ||chr(10)||
	'      "UID": "'||tax_uid||'",' ||chr(10)||
	'      "Rank": "'|| replace(replace(translate(rank_name,'"\'||chr(10),'^/|'),'^','\"'),'/','\')||'",' ||chr(10)||
	'      "TaxonName": "'|| replace(replace(translate(tax_name,'"\'||chr(10),'^/|'),'^','\"'),'/','\')||'",' ||chr(10)||
	'      "KingdomName": "'|| replace(replace(translate(kingdom_name,'"\'||chr(10),'^/|'),'^','\"'),'/','\')||'",' ||chr(10)||
	'      "HierarchyString": "'||hierarchy_string||'",' ||chr(10)||
	'      "HierarchyLevel": "'||hlevel||'",' ||chr(10)||
	case when "SUBKINGDOM" is not null then '      "SubKingdom'||'Name": "'|| replace(replace(translate("SUBKINGDOM",'"\'||chr(10),'^/|'),'^','\"'),'/','\')||'",' ||chr(10) else '' end 	||
	case when "INFRAKINGDOM" is not null then '      "InfraKingdom'||'Name": "'|| replace(replace(translate("INFRAKINGDOM",'"\'||chr(10),'^/|'),'^','\"'),'/','\')||'",' ||chr(10) else '' end 	||
	case when "SUPERPHYLUM" is not null then '      "SuperPhylum'||'Name": "'|| replace(replace(translate("SUPERPHYLUM",'"\'||chr(10),'^/|'),'^','\"'),'/','\')||'",' ||chr(10) else '' end 	||
	case when "PHYLUM" is not null then '      "Phylum'||'Name": "'|| replace(replace(translate("PHYLUM",'"\'||chr(10),'^/|'),'^','\"'),'/','\')||'",' ||chr(10) else '' end 	||
	case when "SUBPHYLUM" is not null then '      "SubPhylum'||'Name": "'|| replace(replace(translate("SUBPHYLUM",'"\'||chr(10),'^/|'),'^','\"'),'/','\')||'",' ||chr(10) else '' end 	||
	case when "INFRAPHYLUM" is not null then '      "InfraPhylum'||'Name": "'|| replace(replace(translate("INFRAPHYLUM",'"\'||chr(10),'^/|'),'^','\"'),'/','\')||'",' ||chr(10) else '' end 	||
	case when "SUPERDIVISION" is not null then '      "SuperDivision'||'Name": "'|| replace(replace(translate("SUPERDIVISION",'"\'||chr(10),'^/|'),'^','\"'),'/','\')||'",' ||chr(10) else '' end 	||
	case when "DIVISION" is not null then '      "Division'||'Name": "'|| replace(replace(translate("DIVISION",'"\'||chr(10),'^/|'),'^','\"'),'/','\')||'",' ||chr(10) else '' end 	||
	case when "SUBDIVISION" is not null then '      "SubDivision'||'Name": "'|| replace(replace(translate("SUBDIVISION",'"\'||chr(10),'^/|'),'^','\"'),'/','\')||'",' ||chr(10) else '' end 	||
	case when "INFRADIVISION" is not null then '      "InfraDivision'||'Name": "'|| replace(replace(translate("INFRADIVISION",'"\'||chr(10),'^/|'),'^','\"'),'/','\')||'",' ||chr(10) else '' end 	||
	case when "PARVDIVISION" is not null then '      "ParvDivision'||'Name": "'|| replace(replace(translate("PARVDIVISION",'"\'||chr(10),'^/|'),'^','\"'),'/','\')||'",' ||chr(10) else '' end 	||
	case when "SUPERCLASS" is not null then '      "SuperClass'||'Name": "'|| replace(replace(translate("SUPERCLASS",'"\'||chr(10),'^/|'),'^','\"'),'/','\')||'",' ||chr(10) else '' end 	||
	case when "CLASS" is not null then '      "Class'||'Name": "'|| replace(replace(translate("CLASS",'"\'||chr(10),'^/|'),'^','\"'),'/','\')||'",' ||chr(10) else '' end 	||
	case when "SUBCLASS" is not null then '      "SubClass'||'Name": "'|| replace(replace(translate("SUBCLASS",'"\'||chr(10),'^/|'),'^','\"'),'/','\')||'",' ||chr(10) else '' end 	||
	case when "INFRACLASS" is not null then '      "InfraClass'||'Name": "'|| replace(replace(translate("INFRACLASS",'"\'||chr(10),'^/|'),'^','\"'),'/','\')||'",' ||chr(10) else '' end 	||
	case when "SUPERORDER" is not null then '      "SuperOrder'||'Name": "'|| replace(replace(translate("SUPERORDER",'"\'||chr(10),'^/|'),'^','\"'),'/','\')||'",' ||chr(10) else '' end 	||
	case when "ORDERR" is not null then '      "Order'||'Name": "'|| replace(replace(translate("ORDERR",'"\'||chr(10),'^/|'),'^','\"'),'/','\')||'",' ||chr(10) else '' end 	||
	case when "SUBORDER" is not null then '      "SubOrder'||'Name": "'|| replace(replace(translate("SUBORDER",'"\'||chr(10),'^/|'),'^','\"'),'/','\')||'",' ||chr(10) else '' end 	||
	case when "INFRAORDER" is not null then '      "InfraOrder'||'Name": "'|| replace(replace(translate("INFRAORDER",'"\'||chr(10),'^/|'),'^','\"'),'/','\')||'",' ||chr(10) else '' end 	||
	case when "SUPERFAMILY" is not null then '      "SuperFamily'||'Name": "'|| replace(replace(translate("SUPERFAMILY",'"\'||chr(10),'^/|'),'^','\"'),'/','\')||'",' ||chr(10) else '' end 	||
	case when "FAMILY" is not null then '      "Family'||'Name": "'|| replace(replace(translate("FAMILY",'"\'||chr(10),'^/|'),'^','\"'),'/','\')||'",' ||chr(10) else '' end 	||
	case when "SUBFAMILY" is not null then '      "SubFamily'||'Name": "'|| replace(replace(translate("SUBFAMILY",'"\'||chr(10),'^/|'),'^','\"'),'/','\')||'",' ||chr(10) else '' end 	||
	case when "TRIBE" is not null then '      "Tribe'||'Name": "'|| replace(replace(translate("TRIBE",'"\'||chr(10),'^/|'),'^','\"'),'/','\')||'",' ||chr(10) else '' end 	||
	case when "SUBTRIBE" is not null then '      "SubTribe'||'Name": "'|| replace(replace(translate("SUBTRIBE",'"\'||chr(10),'^/|'),'^','\"'),'/','\')||'",' ||chr(10) else '' end 	||
	case when "GENUS" is not null then '      "Genus'||'Name": "'|| replace(replace(translate("GENUS",'"\'||chr(10),'^/|'),'^','\"'),'/','\')||'",' ||chr(10) else '' end 	||
	case when "SUBGENUS" is not null then '      "SubGenus'||'Name": "'|| replace(replace(translate("SUBGENUS",'"\'||chr(10),'^/|'),'^','\"'),'/','\')||'",' ||chr(10) else '' end 	||
	case when "SECTION" is not null then '      "Section'||'Name": "'|| replace(replace(translate("SECTION",'"\'||chr(10),'^/|'),'^','\"'),'/','\')||'",' ||chr(10) else '' end 	||
	case when "SUBSECTION" is not null then '      "SubSection'||'Name": "'|| replace(replace(translate("SUBSECTION",'"\'||chr(10),'^/|'),'^','\"'),'/','\')||'",' ||chr(10) else '' end 	||
	case when "SPECIES" is not null then '      "Species'||'Name": "'|| replace(replace(translate("SPECIES",'"\'||chr(10),'^/|'),'^','\"'),'/','\')||'",' ||chr(10) else '' end 	||
	case when "SUBSPECIES" is not null then '      "SubSpecies'||'Name": "'|| replace(replace(translate("SUBSPECIES",'"\'||chr(10),'^/|'),'^','\"'),'/','\')||'",' ||chr(10) else '' end 	||
	case when "VARIETY" is not null then '      "Variety'||'Name": "'|| replace(replace(translate("VARIETY",'"\'||chr(10),'^/|'),'^','\"'),'/','\')||'",' ||chr(10) else '' end 	||
	case when "SUBVARIETY" is not null then '      "SubVariety'||'Name": "'|| replace(replace(translate("SUBVARIETY",'"\'||chr(10),'^/|'),'^','\"'),'/','\')||'",' ||chr(10) else '' end 	||
	case when "FORM" is not null then '      "Form'||'Name": "'|| replace(replace(translate("FORM",'"\'||chr(10),'^/|'),'^','\"'),'/','\')||'",' ||chr(10) else '' end 	||
	case when "SUBFORM" is not null then '      "SubForm'||'Name": "'|| replace(replace(translate("SUBFORM",'"\'||chr(10),'^/|'),'^','\"'),'/','\')||'",' ||chr(10) else '' end 	||
	case when "RACE" is not null then '      "Race'||'Name": "'|| replace(replace(translate("RACE",'"\'||chr(10),'^/|'),'^','\"'),'/','\')||'",' ||chr(10) else '' end 	||
	case when "STIRP" is not null then '      "Stirp'||'Name": "'|| replace(replace(translate("STIRP",'"\'||chr(10),'^/|'),'^','\"'),'/','\')||'",' ||chr(10) else '' end 	||
	case when "MORPH" is not null then '      "Morph'||'Name": "'|| replace(replace(translate("MORPH",'"\'||chr(10),'^/|'),'^','\"'),'/','\')||'",' ||chr(10) else '' end 	||
	case when "ABERRATION" is not null then '      "Aberration'||'Name": "'|| replace(replace(translate("ABERRATION",'"\'||chr(10),'^/|'),'^','\"'),'/','\')||'",' ||chr(10) else '' end 	||
--	'      "Description": "'||txrnk_desc||'",' ||chr(10)||
	'      "LastChangeDate": "'||to_char(cast(taxpar_last_change_date as timestamp with LOCAL time zone), 'YYYY-MM-DD"T"HH24:MI:SS TZR') ||'"'||chr(10)|| 
	'      }' 
 as elementrow 
from wqx.taxon_parent 
order by tax_name
) 
) 
--START WITH rn = 1 
--CONNECT BY rn = PRIOR rn + 1 
/ 
--footerinfo: 
select 
'            ]' ||chr(10)||
'	}' ||chr(10)||
'}' 
from dual;
spool off

--
spool ThermalPreservativeUsed.json
--headerinfo: 
select	
    '{' ||chr(10)||
	'    "root": {' ||chr(10)||
	'      "Thermal Preservative Used": [' 
--	'  "WQXEntity": "' ||chr(10)||
--	'    "WQXElement": " domainEntity="Thermal Preservative Used" 
--schemaElement="ThermalPreservativeUsedName" rowCount="'||(select count(*) from 
--wqx.thermal_preservative)||'" maxLastChangeDate="'||to_char(cast((select max(thprsv_last_change_date) from 
--wqx.thermal_preservative) as timestamp with LOCAL time zone), 'YYYY-MM-DD"T"HH24:MI:SSTZR')||'" }'
from dual;
SELECT 
--SELECT replace( LTRIM(MAX(SYS_CONNECT_BY_PATH(elementrow, '|')), '|'), '|', ','||chr(10)) as elementrecord
	case when rn=1 then '' else ',' end as rowdelimiter, 
	elementrow 
from ( SELECT rownum rn, elementrow
FROM (SELECT 
	'      {' ||chr(10)|| 
	'      "Domain": "Thermal Preservative Used(ThermalPreservativeUsedName)",' ||chr(10)||
	'      "UID": "'||thprsv_uid||'",' ||chr(10)||
	'      "Name": "'|| replace(replace(translate(thprsv_name,'"\'||chr(10),'^/|'),'^','\"'),'/','\')||'",' ||chr(10)||
	'      "Description": "'|| replace(replace(translate(thprsv_desc,'"\'||chr(10),'^/|'),'^','\"'),'/','\')||'",' ||chr(10)||
	'      "LastChangeDate": "'||to_char(cast(thprsv_last_change_date as timestamp with LOCAL time zone), 'YYYY-MM-DD"T"HH24:MI:SS TZR') ||'"'||chr(10)|| 
	'      }' 
 as elementrow 
from wqx.thermal_preservative 
order by thprsv_name
) 
) 
--START WITH rn = 1 
--CONNECT BY rn = PRIOR rn + 1 
/ 
--footerinfo: 
select 
'            ]' ||chr(10)||
'	}' ||chr(10)||
'}' 
from dual;
spool off

--
spool TimeZone.json
--headerinfo: 
select	
    '{' ||chr(10)||
	'    "root": {' ||chr(10)||
	'      "Time Zone": [' 
--	'  "WQXEntity": "' ||chr(10)||
--	'    "WQXElement": " domainEntity="Time Zone" schemaElement="TimeZoneCode" rowCount="'||(select 
--count(*) from wqx.time_zone)||'" maxLastChangeDate="'||to_char(cast((select max(tmzone_last_change_date) 
--from wqx.time_zone) as timestamp with LOCAL time zone), 'YYYY-MM-DD"T"HH24:MI:SSTZR')||'" }'
from dual;
SELECT 
--SELECT replace( LTRIM(MAX(SYS_CONNECT_BY_PATH(elementrow, '|')), '|'), '|', ','||chr(10)) as elementrecord
	case when rn=1 then '' else ',' end as rowdelimiter, 
	elementrow 
from ( SELECT rownum rn, elementrow
FROM (SELECT 
	'      {' ||chr(10)|| 
	'      "Domain": "Time Zone(TimeZoneCode)",' ||chr(10)||
	'      "UID": "'||tmzone_uid||'",' ||chr(10)||
	'      "Code": "'|| replace(replace(translate(tmzone_cd,'"\'||chr(10),'^/|'),'^','\"'),'/','\')||'",' ||chr(10)||
	'      "Name": "'|| replace(replace(translate(tmzone_name,'"\'||chr(10),'^/|'),'^','\"'),'/','\')||'",' ||chr(10)||
	'      "OffSet": "'||tmzone_offset||'",' ||chr(10)||
--	'      "Description": "'||tmzone_desc||'",' ||chr(10)||
	'      "LastChangeDate": "'||to_char(cast(tmzone_last_change_date as timestamp with LOCAL time zone), 'YYYY-MM-DD"T"HH24:MI:SS TZR') ||'"'||chr(10)|| 
	'      }' 
 as elementrow 
from wqx.time_zone 
order by tmzone_name
) 
) 
--START WITH rn = 1 
--CONNECT BY rn = PRIOR rn + 1 
/ 
--footerinfo: 
select 
'            ]' ||chr(10)||
'	}' ||chr(10)||
'}' 
from dual;
spool off

--
spool ToxicityTestType.json
--headerinfo: 
select	
    '{' ||chr(10)||
	'    "root": {' ||chr(10)||
	'      "Toxicity Test Type": [' 
--	'  "WQXEntity": "' ||chr(10)||
--	'    "WQXElement": " domainEntity="Toxicity Test Type" schemaElement="ToxicityTestType" 
--rowCount="'||(select count(*) from wqx.toxicity_test_type)||'" maxLastChangeDate="'||to_char(cast((select 
--max(tttyp_last_change_date) from wqx.toxicity_test_type) as timestamp with LOCAL time zone), 
--'YYYY-MM-DD"T"HH24:MI:SSTZR')||'" }'
from dual;
SELECT 
--SELECT replace( LTRIM(MAX(SYS_CONNECT_BY_PATH(elementrow, '|')), '|'), '|', ','||chr(10)) as elementrecord
	case when rn=1 then '' else ',' end as rowdelimiter, 
	elementrow 
from ( SELECT rownum rn, elementrow
FROM (SELECT 
	'      {' ||chr(10)|| 
	'      "Domain": "Toxicity Test Type(ToxicityTestType)",' ||chr(10)||
	'      "UID": "'||tttyp_uid||'",' ||chr(10)||
	'      "Name": "'|| replace(replace(translate(tttyp_name,'"\'||chr(10),'^/|'),'^','\"'),'/','\')||'",' ||chr(10)||
	'      "Description": "'|| replace(replace(translate(tttyp_desc,'"\'||chr(10),'^/|'),'^','\"'),'/','\')||'",' ||chr(10)||
	'      "LastChangeDate": "'||to_char(cast(tttyp_last_change_date as timestamp with LOCAL time zone), 'YYYY-MM-DD"T"HH24:MI:SS TZR') ||'"'||chr(10)|| 
	'      }' 
 as elementrow 
from wqx.toxicity_test_type 
order by tttyp_name
) 
) 
--START WITH rn = 1 
--CONNECT BY rn = PRIOR rn + 1 
/ 
--footerinfo: 
select 
'            ]' ||chr(10)||
'	}' ||chr(10)||
'}' 
from dual;
spool off

--
spool Tribe.json
--headerinfo: 
select	
    '{' ||chr(10)||
	'    "root": {' ||chr(10)||
	'      "Tribe": [' 
--	'  "WQXEntity": "' ||chr(10)||
--	'    "WQXElement": " domainEntity="Tribe" schemaElement="TribalCode" rowCount="'||(select count(*) 
--from wqx.tribe)||'" maxLastChangeDate="'||to_char(cast((select max(trb_last_change_date) from wqx.tribe) 
--as timestamp with LOCAL time zone), 'YYYY-MM-DD"T"HH24:MI:SSTZR')||'" }'
from dual;
SELECT 
--SELECT replace( LTRIM(MAX(SYS_CONNECT_BY_PATH(elementrow, '|')), '|'), '|', ','||chr(10)) as elementrecord
	case when rn=1 then '' else ',' end as rowdelimiter, 
	elementrow 
from ( SELECT rownum rn, elementrow
FROM (SELECT 
	'      {' ||chr(10)|| 
	'      "Domain": "Tribe(TribalCode)",' ||chr(10)||
	'      "UID": "'||trb_uid||'",' ||chr(10)||
	'      "Code": "'|| replace(replace(translate(trb_cd,'"\'||chr(10),'^/|'),'^','\"'),'/','\')||'",' ||chr(10)||
	'      "Name": "'|| replace(replace(translate(trb_name,'"\'||chr(10),'^/|'),'^','\"'),'/','\')||'",' ||chr(10)||
	'      "EPAID": "'|| replace(replace(translate(trb_epa_id,'"\'||chr(10),'^/|'),'^','\"'),'/','\')||'",' ||chr(10)||
	'      "EPAName": "'|| replace(replace(translate(trb_epa_name,'"\'||chr(10),'^/|'),'^','\"'),'/','\')||'",' ||chr(10)||
	'      "Description": "'|| replace(replace(translate(trb_desc,'"\'||chr(10),'^/|'),'^','\"'),'/','\')||'",' ||chr(10)||
	'      "LastChangeDate": "'||to_char(cast(trb_last_change_date as timestamp with LOCAL time zone), 'YYYY-MM-DD"T"HH24:MI:SS TZR') ||'"'||chr(10)|| 
	'      }' 
 as elementrow 
from wqx.tribe 
order by trb_name
) 
) 
--START WITH rn = 1 
--CONNECT BY rn = PRIOR rn + 1 
/ 
--footerinfo: 
select 
'            ]' ||chr(10)||
'	}' ||chr(10)||
'}' 
from dual;
spool off

--
spool VerticalCollectionMethod.json
--headerinfo: 
select	
    '{' ||chr(10)||
	'    "root": {' ||chr(10)||
	'      "Vertical Collection Method": [' 
--	'  "WQXEntity": "' ||chr(10)||
--	'    "WQXElement": " domainEntity="Vertical Collection Method" 
--schemaElement="VerticalCollectionMethodName" rowCount="'||(select count(*) from 
--wqx.vertical_collection_method)||'" maxLastChangeDate="'||to_char(cast((select max(vcmth_last_change_date) 
--from wqx.vertical_collection_method) as timestamp with LOCAL time zone), 'YYYY-MM-DD"T"HH24:MI:SSTZR')||'" 
--}'
from dual;
SELECT 
--SELECT replace( LTRIM(MAX(SYS_CONNECT_BY_PATH(elementrow, '|')), '|'), '|', ','||chr(10)) as elementrecord
	case when rn=1 then '' else ',' end as rowdelimiter, 
	elementrow 
from ( SELECT rownum rn, elementrow
FROM (SELECT 
	'      {' ||chr(10)|| 
	'      "Domain": "Vertical Collection Method(VerticalCollectionMethodName)",' ||chr(10)||
	'      "UID": "'||vcmth_uid||'",' ||chr(10)||
	'      "Name": "'|| replace(replace(translate(vcmth_name,'"\'||chr(10),'^/|'),'^','\"'),'/','\')||'",' ||chr(10)||
	'      "Description": "'|| replace(replace(translate(vcmth_desc,'"\'||chr(10),'^/|'),'^','\"'),'/','\')||'",' ||chr(10)||
	'      "LastChangeDate": "'||to_char(cast(vcmth_last_change_date as timestamp with LOCAL time zone), 'YYYY-MM-DD"T"HH24:MI:SS TZR') ||'"'||chr(10)|| 
	'      }' 
 as elementrow 
from wqx.vertical_collection_method 
order by vcmth_name
) 
) 
--START WITH rn = 1 
--CONNECT BY rn = PRIOR rn + 1 
/ 
--footerinfo: 
select 
'            ]' ||chr(10)||
'	}' ||chr(10)||
'}' 
from dual;
spool off

--
spool VerticalCoordinateReferenceSystemDatum.json
--headerinfo: 
select	
    '{' ||chr(10)||
	'    "root": {' ||chr(10)||
	'      "Vertical Coordinate Reference System Datum": [' 
--	'  "WQXEntity": "' ||chr(10)||
--	'    "WQXElement": " domainEntity="Vertical Coordinate Reference System Datum" 
--schemaElement="VerticalCoordinateReferenceSystemDatumName" rowCount="'||(select count(*) from 
--wqx.vertical_reference_datum)||'" maxLastChangeDate="'||to_char(cast((select max(vrdat_last_change_date) 
--from wqx.vertical_reference_datum) as timestamp with LOCAL time zone), 'YYYY-MM-DD"T"HH24:MI:SSTZR')||'" }'
from dual;
SELECT 
--SELECT replace( LTRIM(MAX(SYS_CONNECT_BY_PATH(elementrow, '|')), '|'), '|', ','||chr(10)) as elementrecord
	case when rn=1 then '' else ',' end as rowdelimiter, 
	elementrow 
from ( SELECT rownum rn, elementrow
FROM (SELECT 
	'      {' ||chr(10)|| 
	'      "Domain": "Vertical Coordinate Reference System Datum(VerticalCoordinateReferenceSystemDatumName)",' ||chr(10)||
	'      "UID": "'||vrdat_uid||'",' ||chr(10)||
	'      "Name": "'|| replace(replace(translate(vrdat_name,'"\'||chr(10),'^/|'),'^','\"'),'/','\')||'",' ||chr(10)||
	'      "Description": "'|| replace(replace(translate(vrdat_desc,'"\'||chr(10),'^/|'),'^','\"'),'/','\')||'",' ||chr(10)||
	'      "LastChangeDate": "'||to_char(cast(vrdat_last_change_date as timestamp with LOCAL time zone), 'YYYY-MM-DD"T"HH24:MI:SS TZR') ||'"'||chr(10)|| 
	'      }' 
 as elementrow 
from wqx.vertical_reference_datum 
order by vrdat_name
) 
) 
--START WITH rn = 1 
--CONNECT BY rn = PRIOR rn + 1 
/ 
--footerinfo: 
select 
'            ]' ||chr(10)||
'	}' ||chr(10)||
'}' 
from dual;
spool off

--
spool Voltinism.json
--headerinfo: 
select	
    '{' ||chr(10)||
	'    "root": {' ||chr(10)||
	'      "Voltinism": [' 
--	'  "WQXEntity": "' ||chr(10)||
--	'    "WQXElement": " domainEntity="Voltinism" schemaElement="VoltinismName" rowCount="'||(select 
--count(*) from wqx.Voltinism)||'" maxLastChangeDate="'||to_char(cast((select max(volt_last_change_date) from 
--wqx.Voltinism) as timestamp with LOCAL time zone), 'YYYY-MM-DD"T"HH24:MI:SSTZR')||'" }'
from dual;
SELECT 
--SELECT replace( LTRIM(MAX(SYS_CONNECT_BY_PATH(elementrow, '|')), '|'), '|', ','||chr(10)) as elementrecord
	case when rn=1 then '' else ',' end as rowdelimiter, 
	elementrow 
from ( SELECT rownum rn, elementrow
FROM (SELECT 
	'      {' ||chr(10)|| 
	'      "Domain": "Voltinism(VoltinismName)",' ||chr(10)||
	'      "UID": "'||volt_uid||'",' ||chr(10)||
	'      "Name": "'|| replace(replace(translate(volt_name,'"\'||chr(10),'^/|'),'^','\"'),'/','\')||'",' ||chr(10)||
	'      "Description": "'|| replace(replace(translate(volt_desc,'"\'||chr(10),'^/|'),'^','\"'),'/','\')||'",' ||chr(10)||
	'      "LastChangeDate": "'||to_char(cast(volt_last_change_date as timestamp with LOCAL time zone), 'YYYY-MM-DD"T"HH24:MI:SS TZR') ||'"'||chr(10)|| 
	'      }' 
 as elementrow 
from wqx.Voltinism 
order by volt_name
) 
) 
--START WITH rn = 1 
--CONNECT BY rn = PRIOR rn + 1 
/ 
--footerinfo: 
select 
'            ]' ||chr(10)||
'	}' ||chr(10)||
'}' 
from dual;
spool off

--
spool WellFormationType.json
--headerinfo: 
select	
    '{' ||chr(10)||
	'    "root": {' ||chr(10)||
	'      "Well Formation Type": [' 
--	'  "WQXEntity": "' ||chr(10)||
--	'    "WQXElement": " domainEntity="Well Formation Type" schemaElement="WellInformation/FormationTypeText" 
--rowCount="'||(select count(*) from wqx.well_formation_type)||'" maxLastChangeDate="'||to_char(cast((select 
--max(wftyp_last_change_date) from wqx.well_formation_type) as timestamp with LOCAL time zone), 
--'YYYY-MM-DD"T"HH24:MI:SSTZR')||'" }'
from dual;
SELECT 
--SELECT replace( LTRIM(MAX(SYS_CONNECT_BY_PATH(elementrow, '|')), '|'), '|', ','||chr(10)) as elementrecord
	case when rn=1 then '' else ',' end as rowdelimiter, 
	elementrow 
from ( SELECT rownum rn, elementrow
FROM (SELECT 
	'      {' ||chr(10)|| 
	'      "Domain": "Well Formation Type(FormationTypeText)",' ||chr(10)||
	'      "UID": "'||wftyp_uid||'",' ||chr(10)||
	'      "Name": "'|| replace(replace(translate(wftyp_name,'"\'||chr(10),'^/|'),'^','\"'),'/','\')||'",' ||chr(10)||
	'      "Description": "'|| replace(replace(translate(wftyp_desc,'"\'||chr(10),'^/|'),'^','\"'),'/','\')||'",' ||chr(10)||
	'      "LastChangeDate": "'||to_char(cast(wftyp_last_change_date as timestamp with LOCAL time zone), 'YYYY-MM-DD"T"HH24:MI:SS TZR') ||'"'||chr(10)|| 
	'      }' 
 as elementrow 
from wqx.well_formation_type 
order by wftyp_name
) 
) 
--START WITH rn = 1 
--CONNECT BY rn = PRIOR rn + 1 
/ 
--footerinfo: 
select 
'            ]' ||chr(10)||
'	}' ||chr(10)||
'}' 
from dual;
spool off

--
spool WellType.json
--headerinfo: 
select	
    '{' ||chr(10)||
	'    "root": {' ||chr(10)||
	'      "Well Type": [' 
--	'  "WQXEntity": "' ||chr(10)||
--	'    "WQXElement": " domainEntity="Well Type" schemaElement="WellInformation/WellTypeText" rowCount="'||(select 
--count(*) from wqx.well_type)||'" maxLastChangeDate="'||to_char(cast((select max(weltyp_last_change_date) 
--from wqx.well_type) as timestamp with LOCAL time zone), 'YYYY-MM-DD"T"HH24:MI:SSTZR')||'" }'
from dual;
SELECT 
--SELECT replace( LTRIM(MAX(SYS_CONNECT_BY_PATH(elementrow, '|')), '|'), '|', ','||chr(10)) as elementrecord
	case when rn=1 then '' else ',' end as rowdelimiter, 
	elementrow 
from ( SELECT rownum rn, elementrow
FROM (SELECT 
	'      {' ||chr(10)|| 
	'      "Domain": "Well Type(WellTypeText)",' ||chr(10)||
	'      "UID": "'||weltyp_uid||'",' ||chr(10)||
	'      "Name": "'|| replace(replace(translate(weltyp_name,'"\'||chr(10),'^/|'),'^','\"'),'/','\')||'",' ||chr(10)||
	'      "Description": "'|| replace(replace(translate(weltyp_desc,'"\'||chr(10),'^/|'),'^','\"'),'/','\')||'",' ||chr(10)||
	'      "LastChangeDate": "'||to_char(cast(weltyp_last_change_date as timestamp with LOCAL time zone), 'YYYY-MM-DD"T"HH24:MI:SS TZR') ||'"'||chr(10)|| 
	'      }' 
 as elementrow 
from wqx.well_type 
order by weltyp_name
) 
) 
--START WITH rn = 1 
--CONNECT BY rn = PRIOR rn + 1 
/ 
--footerinfo: 
select 
'            ]' ||chr(10)||
'	}' ||chr(10)||
'}' 
from dual;
spool off


