Hi,
how i can read a Json format in oracle an field.
my DSI will create an oracle Database contain a table with fields CLOB and BLOB dat type and these fields contains a JSON format text.
I need to read this field and transforme it ( the JSON format) in an classical table sas.
Please someone can help me? or is it possible easely?
ex of table will created and data will be in the fields
CREATE TABLE "REFERENTIEL"."TR_LIQ_PIECE_JUSTIFICATIVE"
( "F_NUM_LIQ_PIECE_JUSTIFICATIVE" NUMBER GENERATED BY DEFAULT ON NULL AS IDENTITY MINVALUE 1 MAXVALUE 9999999999999999999999999999 INCREMENT BY 1 START WITH 1 CACHE 20 NOORDER NOCYCLE NOT NULL ENABLE,
"F_DOC" CLOB,
"F_DT_DEBUT" DATE NOT NULL ENABLE,
"F_DT_FIN" DATE,
"F_USER_MAJ" VARCHAR2(15 BYTE) NOT NULL ENABLE,
"F_DT_CREATION" DATE DEFAULT SYSDATE NOT NULL ENABLE,
"F_DT_MAJ" DATE,
"F_VERSION" NUMBER(10,0) DEFAULT (0) NOT NULL ENABLE,
CONSTRAINT "PK_TR_LIQ_PIECE_JUSTIFICATIVE" PRIMARY KEY ("F_NUM_LIQ_PIECE_JUSTIFICATIVE")
USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "TBS_INDX" ENABLE
) SEGMENT CREATION IMMEDIATE
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
NOCOMPRESS LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "TBS_DATA"
LOB ("F_DOC") STORE AS SECUREFILE (
TABLESPACE "TBS_DATA" ENABLE STORAGE IN ROW CHUNK 8192
NOCACHE LOGGING NOCOMPRESS KEEP_DUPLICATES
STORAGE(INITIAL 106496 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)) ;
Visuellement, le F_DOC contiendra: le but est de pouvoir faire un select dans la table pour ramener les pièces justificatives suivant les critères par exple objet=’ETUDES’ AND situation_adherent=’INAPTITUDE’
{
"data": [
{
"num_element": 1,
"objet": "OBLIGATOIRE",
"regime": "",
"type_pension": "",
"situation_adherent": "",
"piece_justificative": "FORMULAIRE_LIQ,CARTE_IDENTITE"
},
{
"num_element": 2,
"objet": "ETUDES",
"regime": "RB",
"type_pension": "TX_PLEIN",
"situation_adherent": "AGE_TX_PLEIN",
"piece_justificative": ""
},
{
"num_element": 3,
"objet": "ETUDES",
"regime": "RB",
"type_pension": "TX_PLEIN",
"situation_adherent": "AGE_ATTEINT_TRI_OK",
"piece_justificative": ""
},
{
"num_element": 4,
"objet": "ETUDES",
"regime": "RB",
"type_pension": "TX_PLEIN",
"situation_adherent": "INAPTITUDE",
"piece_justificative": ""
},
{
"num_element": 5,
"objet": "ETUDES",
"regime": "RB",
"type_pension": "TX_PLEIN",
"situation_adherent": "HANDICAPE",
"piece_justificative": "JUST_HANDI_INCA"
},
{
"num_element": 6,
"objet": "ETUDES",
"regime": "RB",
"type_pension": "TX_PLEIN",
"situation_adherent": "ANCIEN_COMBATTANT",
"piece_justificative": "JUST_ANC_COMBATTANT"
},
{
"num_element": 7,
"objet": "ETUDES",
"regime": "RB",
"type_pension": "TX_PLEIN",
"situation_adherent": "LONGUE_CARRIERE",
"piece_justificative": ""
},
{
"num_element": 8,
"objet": "ETUDES",
"regime": "RB",
"type_pension": "TX_PLEIN",
"situation_adherent": "ALLOC_AMIANTE",
"piece_justificative": "JUST_AMIANTE"
},
{
"num_element": 9,
"objet": "ETUDES",
"regime": "RB",
"type_pension": "ABATTEMENT",
"situation_adherent": "AGE_ATTEINT_TRI_KO",
"piece_justificative": ""
},
{
"num_element": 10,
"objet": "ETUDES",
"regime": "RB",
"type_pension": "",
"situation_adherent": "RETRAITE_PROGRESSIVE",
"piece_justificative": "NOTIF_RE_PROGRESSIVE"
},
{
"num_element": 11,
"objet": "ETUDES",
"regime": "RC",
"type_pension": "TX_PLEIN",
"situation_adherent": "AGE_TX_PLEIN",
"piece_justificative": ""
},
{
"num_element": 12,
"objet": "ETUDES",
"regime": "RC",
"type_pension": "TX_PLEIN",
"situation_adherent": "AGE_ATT_RB_TX_PLEIN",
"piece_justificative": ""
},
{
"num_element": 13,
"objet": "ETUDES",
"regime": "RC",
"type_pension": "TX_PLEIN",
"situation_adherent": "AGE_ATT_TX_ABAT_RB",
"piece_justificative": ""
},
{
"num_element": 14,
"objet": "ETUDES",
"regime": "RC",
"type_pension": "ABATTEMENT",
"situation_adherent": "AGE_ATT_TX_ABAT_RB",
"piece_justificative": ""
},
{
"num_element": 15,
"objet": "ETUDES",
"regime": "RC",
"type_pension": "ABATTEMENT",
"situation_adherent": "AGE_ATT_TX_ABAT_RC",
"piece_justificative": ""
},
{
"num_element": 16,
"objet": "ETUDES",
"regime": "RC",
"type_pension": "MAJORATION",
"situation_adherent": "AGE_TX_PLEIN_MAJ_3_15",
"piece_justificative": ""
},
{
"num_element": 17,
"objet": "ETUDES",
"regime": "RC",
"type_pension": "",
"situation_adherent": "MAJ_FAMILIALE",
"piece_justificative": "LIVRET_FAMILLE_ACTE_NAISS"
},
{
"num_element": 18,
"objet": "FACULTATIF",
"regime": "",
"type_pension": "",
"situation_adherent": "",
"piece_justificative": "RIB,AVIS_IMPOSITION"
}
]
}
