Community talk about communities.sas.com. Meta!

how i can read a Json format in oracle an field

Reply
Occasional Contributor
Posts: 5

how i can read a Json format in oracle an field

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"

             }

       ]

}

 

 

 

Super User
Super User
Posts: 7,050

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

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;
Frequent Contributor
Posts: 83

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

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.

Occasional Contributor
Posts: 5

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

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

Ask a Question
Discussion stats
  • 3 replies
  • 146 views
  • 0 likes
  • 3 in conversation