Skip to content

Instantly share code, notes, and snippets.

@vasa-chi

vasa-chi/the_shit.sql

Created January 19, 2015 11:14
Show Gist options
  • Save vasa-chi/aa2c8642b733ed345ea2 to your computer and use it in GitHub Desktop.
Save vasa-chi/aa2c8642b733ed345ea2 to your computer and use it in GitHub Desktop.
Download ZIP
Raw
the_shit.sql
CREATE OR REPLACE FUNCTION TOIR.GET_PP_BY_ID_FOR_ASUNO (p_id IN number)
RETURN CLOB
AS
v_CLOB CLOB;
BEGIN
v_clob := EMPTY_CLOB ();
BEGIN
v_clob := '[';
FOR i
IN (SELECT PP_ID,
DISPATCHER_ID,
PARENT_PP_ID,
PP_NAME,
pp.PP_CLASS_ID,
pp.STREET_ID,
ADM_OKR_ID,
HOUSE,
DESCR,
OWNER_ID,
YEAR_ENTER,
CASCAD_PP_ID,
MATERIAL_ID,
VOLTAGE,
CONDITION,
pp.CONTROL_ID,
CONTROL_LINE,
CHARACTERS,
POWER_SUPPLIER,
SBOR_COUNT,
RESOLUTION_POWER,
YEAR_KAP_REM,
pp.ETR_CLASS_ID,
PERIOD_PPR,
CONTROL_PPR,
pp.CODE_1C,
NOMER_OC,
POWERED_FROM,
TP_NOMER,
CABEL_BOX,
LIGHTNING,
MAT_K,
MAT_F,
MAT_D,
CONSTRUCT_YEAR,
SIZE_X,
SIZE_Y,
SIZE_Z,
pp.STATUS_ID,
RESOLUTION_EXTRA_POWER,
MUN_OKR_ID,
CATEGORY_ID,
AOC.ADM_OKR,
AOC.ADM_OKR_SHORT,
PC.CATEGORY,
PCL.PP_TYPE,
PCON.NAME pcon_name,
D.NAME dname,
EC.ETR,
MAT.MATERIAL MAT_MATERIAL,
M.MATERIAL M_MATERIAL,
MF.MATERIAL MF_MATERIAL,
MK.MATERIAL MK_MATERIAL,
MO.MUN_OKRUG,
F.FIRM_NAME,
F.INN,
F.KPP,
S.PP_STATUS,
SC.STREET,
ORA_HASH (
CONCAT (
CONCAT (
CONCAT (
CONCAT (
CONCAT (
CONCAT (
CONCAT (
CONCAT (
CONCAT (
CONCAT (
CONCAT (
CONCAT (
CONCAT (
CONCAT (
CONCAT (
CONCAT (
CONCAT (
CONCAT (
CONCAT (
CONCAT (
CONCAT (
CONCAT (
CONCAT (
CONCAT (
CONCAT (
CONCAT (
CONCAT (
CONCAT (
CONCAT (
CONCAT (
CONCAT (
CONCAT (
CONCAT (
CONCAT (
CONCAT (
CONCAT (
CONCAT (
CONCAT (
CONCAT (
CONCAT (
CONCAT (
PP_ID,
pp.DISPATCHER_ID),
pp.PARENT_PP_ID),
pp.PP_NAME),
pp.PP_CLASS_ID),
pp.STREET_ID),
pp.ADM_OKR_ID),
pp.HOUSE),
pp.DESCR),
pp.OWNER_ID),
pp.YEAR_ENTER),
pp.CASCAD_PP_ID),
pp.MATERIAL_ID),
pp.VOLTAGE),
pp.CONDITION),
pp.CONTROL_ID),
pp.CONTROL_LINE),
pp.CHARACTERS),
pp.POWER_SUPPLIER),
pp.SBOR_COUNT),
pp.RESOLUTION_POWER),
pp.YEAR_KAP_REM),
pp.ETR_CLASS_ID),
pp.PERIOD_PPR),
pp.CONTROL_PPR),
pp.CODE_1C),
pp.NOMER_OC),
pp.POWERED_FROM),
pp.TP_NOMER),
pp.CABEL_BOX),
pp.LIGHTNING),
pp.MAT_K),
pp.MAT_F),
pp.MAT_D),
pp.CONSTRUCT_YEAR),
pp.SIZE_X),
pp.SIZE_Y),
pp.SIZE_Z),
pp.STATUS_ID),
pp.RESOLUTION_EXTRA_POWER),
pp.MUN_OKR_ID),
pp.CATEGORY_ID))
AS hash
FROM pp@MOSLIGHT_DBL pp
LEFT OUTER JOIN ADM_OKRUG_CLASS@MOSLIGHT_DBL aoc
ON pp.ADM_OKR_ID = aoc.ADM_OKR_ID
LEFT OUTER JOIN PP_CATEGORY@MOSLIGHT_DBL pc
ON pp.CATEGORY_ID = pc.CATEGORY_ID
LEFT OUTER JOIN PP_CLASS@MOSLIGHT_DBL pcl
ON pp.PP_CLASS_ID = pcl.PP_CLASS_ID
LEFT OUTER JOIN PP_CONTROL@MOSLIGHT_DBL pcon
ON pp.CONTROL_ID = pcon.CONTROL_ID
LEFT OUTER JOIN DISPATCHER@MOSLIGHT_DBL d
ON pp.DISPATCHER_ID = d.DISPATCHER_ID
LEFT OUTER JOIN ETR_CLASS@MOSLIGHT_DBL ec
ON pp.ETR_CLASS_ID = ec.ETR_CLASS_ID
LEFT OUTER JOIN MATERIAL@MOSLIGHT_DBL mat
ON pp.MAT_D = mat.MATERIAL_ID
LEFT OUTER JOIN MATERIAL@MOSLIGHT_DBL m
ON pp.MATERIAL_ID = M.MATERIAL_ID
LEFT OUTER JOIN MATERIAL@MOSLIGHT_DBL mf
ON pp.MAT_F = MF.MATERIAL_ID
LEFT OUTER JOIN MATERIAL@MOSLIGHT_DBL mk
ON pp.MAT_K = MK.MATERIAL_ID
LEFT OUTER JOIN MUN_OKRUG_CLASS@MOSLIGHT_DBL mo
ON pp.MUN_OKR_ID = MO.MUN_OKRUG_ID
LEFT OUTER JOIN FIRM@MOSLIGHT_DBL f
ON pp.OWNER_ID = F.FIRM_ID
LEFT OUTER JOIN PP_STATUS@MOSLIGHT_DBL s
ON pp.STATUS_ID = s.STATUS_ID
LEFT OUTER JOIN STREET_CLASS@MOSLIGHT_DBL sc
ON pp.STREET_ID = SC.STREET_CLASS_ID
WHERE pp_id = p_id)
LOOP
DBMS_LOB.APPEND(v_clob,'{');
DBMS_LOB.APPEND(v_clob,'"PP_ID":"' || i.PP_ID || '",');
DBMS_LOB.APPEND(v_clob,'"DISPATCHER_ID":"' || i.DISPATCHER_ID || '",');
DBMS_LOB.APPEND(v_clob,'"PARENT_PP_ID":"' || i.PARENT_PP_ID || '",');
DBMS_LOB.APPEND(v_clob,'"PP_NAME":"' || i.PP_NAME || '",');
DBMS_LOB.APPEND(v_clob,'"PP_CLASS_ID":"' || i.PP_CLASS_ID || '",');
DBMS_LOB.APPEND(v_clob,'"STREET_ID":"' || i.STREET_ID || '",');
DBMS_LOB.APPEND(v_clob,'"ADM_OKR_ID":"' || i.ADM_OKR_ID || '",');
DBMS_LOB.APPEND(v_clob,'"HOUSE":"' || i.HOUSE || '",');
DBMS_LOB.APPEND(v_clob,'"DESCR":"' || i.DESCR || '",');
DBMS_LOB.APPEND(v_clob,'"OWNER_ID":"' || i.OWNER_ID || '",');
DBMS_LOB.APPEND(v_clob,'"YEAR_ENTER":"' || i.YEAR_ENTER || '",');
DBMS_LOB.APPEND(v_clob,'"CASCAD_PP_ID":"' || i.CASCAD_PP_ID || '",');
DBMS_LOB.APPEND(v_clob,'"MATERIAL_ID":"' || i.MATERIAL_ID || '",');
DBMS_LOB.APPEND(v_clob,'"VOLTAGE":"' || i.VOLTAGE || '",');
DBMS_LOB.APPEND(v_clob,'"CONDITION":"' || i.CONDITION || '",');
DBMS_LOB.APPEND(v_clob,'"CONTROL_ID":"' || i.CONTROL_ID || '",');
DBMS_LOB.APPEND(v_clob,'"CONTROL_LINE":"' || i.CONTROL_LINE || '",');
DBMS_LOB.APPEND(v_clob,'"CHARACTERS":"' || i.CHARACTERS || '",');
DBMS_LOB.APPEND(v_clob,'"POWER_SUPPLIER":"' || i.POWER_SUPPLIER || '",');
DBMS_LOB.APPEND(v_clob,'"SBOR_COUNT":"' || i.SBOR_COUNT || '",');
DBMS_LOB.APPEND(v_clob,'"RESOLUTION_POWER":"' || i.RESOLUTION_POWER || '",');
DBMS_LOB.APPEND(v_clob,'"YEAR_KAP_REM":"' || i.YEAR_KAP_REM || '",');
DBMS_LOB.APPEND(v_clob,'"ETR_CLASS_ID":"' || i.ETR_CLASS_ID || '",');
DBMS_LOB.APPEND(v_clob,'"PERIOD_PPR":"' || i.PERIOD_PPR || '",');
DBMS_LOB.APPEND(v_clob,'"CONTROL_PPR":"' || i.CONTROL_PPR || '",');
DBMS_LOB.APPEND(v_clob,'"CODE_1C":"' || i.CODE_1C || '",');
DBMS_LOB.APPEND(v_clob,'"NOMER_OC":"' || i.NOMER_OC || '",');
DBMS_LOB.APPEND(v_clob,'"POWERED_FROM":"' || i.POWERED_FROM || '",');
DBMS_LOB.APPEND(v_clob,'"TP_NOMER":"' || i.TP_NOMER || '",');
DBMS_LOB.APPEND(v_clob,'"CABEL_BOX":"' || i.CABEL_BOX || '",');
DBMS_LOB.APPEND(v_clob,'"LIGHTNING":"' || i.LIGHTNING || '",');
DBMS_LOB.APPEND(v_clob,'"MAT_K":"' || i.MAT_K || '",');
DBMS_LOB.APPEND(v_clob,'"MAT_F":"' || i.MAT_F || '",');
DBMS_LOB.APPEND(v_clob,'"MAT_D":"' || i.MAT_D || '",');
DBMS_LOB.APPEND(v_clob,'"CONSTRUCT_YEAR":"' || i.CONSTRUCT_YEAR || '",');
DBMS_LOB.APPEND(v_clob,'"SIZE_X":"' || i.SIZE_X || '",');
DBMS_LOB.APPEND(v_clob,'"SIZE_Y":"' || i.SIZE_Y || '",');
DBMS_LOB.APPEND(v_clob,'"SIZE_Z":"' || i.SIZE_Z || '",');
DBMS_LOB.APPEND(v_clob,'"STATUS_ID":"' || i.STATUS_ID || '",');
DBMS_LOB.APPEND(v_clob,'"RESOLUTION_EXTRA_POWER":"'
|| i.RESOLUTION_EXTRA_POWER
|| '",');
DBMS_LOB.APPEND(v_clob,'"MUN_OKR_ID":"' || i.MUN_OKR_ID || '",');
DBMS_LOB.APPEND(v_clob,'"CATEGORY_ID":"' || i.CATEGORY_ID || '",');
DBMS_LOB.APPEND(v_clob,'"ADM_OKR":"' || i.ADM_OKR || '",');
DBMS_LOB.APPEND(v_clob,'"ADM_OKR_SHORT":"' || i.ADM_OKR_SHORT || '",');
DBMS_LOB.APPEND(v_clob,'"CATEGORY":"' || i.CATEGORY || '",');
DBMS_LOB.APPEND(v_clob,'"PP_TYPE":"' || i.PP_TYPE || '",');
DBMS_LOB.APPEND(v_clob,'"PCON_NAME":"' || i.pcon_name || '",');
DBMS_LOB.APPEND(v_clob,'"DNAME":"' || i.DNAME || '",');
DBMS_LOB.APPEND(v_clob,'"ETR":"' || i.ETR || '",');
DBMS_LOB.APPEND(v_clob,'"MATERIAL MAT_MATERIAL":"' || i.MAT_MATERIAL || '",');
DBMS_LOB.APPEND(v_clob,'"MATERIAL M_MATERIAL":"' || i.M_MATERIAL || '",');
DBMS_LOB.APPEND(v_clob,'"MATERIAL MF_MATERIAL":"' || i.MF_MATERIAL || '",');
DBMS_LOB.APPEND(v_clob,'"MATERIAL MK_MATERIAL":"' || i.MK_MATERIAL || '",');
DBMS_LOB.APPEND(v_clob,'"MUN_OKRUG":"' || i.MUN_OKRUG || '",');
DBMS_LOB.APPEND(v_clob,'"FIRM_NAME":"' || i.FIRM_NAME || '",');
DBMS_LOB.APPEND(v_clob,'"INN":"' || i.INN || '",');
DBMS_LOB.APPEND(v_clob,'"KPP":"' || i.KPP || '",');
DBMS_LOB.APPEND(v_clob,'"PP_STATUS":"' || i.PP_STATUS || '",');
DBMS_LOB.APPEND(v_clob,'"STREET":"' || i.STREET || '"');
DBMS_LOB.APPEND(v_clob,'}');
END LOOP;
DBMS_LOB.APPEND(v_clob,']');
DBMS_OUTPUT.put_line (SUBSTR (v_clob, 1, 12000));
RETURN v_CLOB;
EXCEPTION
WHEN OTHERS
THEN
RAISE_APPLICATION_ERROR (-20100, 'Error processing JSON.');
END;
END;
/
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment