how i can read a Json format in oracle an field

Yakop — Fri, 19 May 2017 14:26:27 GMT

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"

}

]

}

Re: how i can read a Json format in oracle an field

Tom — Fri, 19 May 2017 15:34:10 GMT

I would suggest using Oracle code that pulls out the values your want from the JSON text as seperate fields.

proc sql ;
  connect to oracle .... ;
  create table want as 
   select * from connection to oracle
   (select ......)
  ;
quit;

Re: how i can read a Json format in oracle an field

Sven111 — Fri, 19 May 2017 16:38:01 GMT

What version of Oracle are you using? If it's 12c, then it supports JSON natively so you could define the fields as CLOB with a IS JSON constraint instead of standard CLOBs and Oracle is actually pretty good at querying them internally using SQL. If this is possible, I'd just leave everything in Oracle and access it from SAS using explicity SQL pass-through as @Tom was demonstrating.

Re: how i can read a Json format in oracle an field

Yakop — Fri, 19 May 2017 17:42:47 GMT

Hi Guys,
thanks for your answers.
I don't konw wich version of oracle is used. I will know more on next monday.
And i will let you know.

have a good WE

topic Re: how i can read a Json format in oracle an field in All Things Community

how i can read a Json format in oracle an field

Re: how i can read a Json format in oracle an field

Re: how i can read a Json format in oracle an field

Re: how i can read a Json format in oracle an field