Because the world needs more PL/SQL blogs
In the previous post Austin discovered this piece of code. DECLARE v VARCHAR2(30); BEGIN — loop through the sparse IBT values and — load them into the JSON Array v := v_unique_sites.FIRST; LOOP EXIT WHEN v IS NULL; v_array2.APPEND(v); v := v_unique_sites.NEXT(v); END LOOP; END; Produced this perplexing result. [“\”Hospital\””] It turns out it’s valid … Read more
Using PLSQL Index By Table’s (IBT’s) is something Austin has been doing for a million years. HIs algorithm to loop through the IBT and add values to the JSON Array originally was this: DECLARE v VARCHAR2(30); BEGIN — loop through the sparse IBT values and — load them into the JSON Array v := v_unique_sites.FIRST; … Read more
Buoyed by his early success Austin sets out for some thorough function testing. He eventually comes across this result. SELECT JSON_VALUE(everything_else,’$.Name’) nm, sites(JSON_QUERY(everything_else,’$.MRNS’)) sites FROM patient_data WHERE JSON_VALUE(everything_else,’$.Name’) = ‘Cooter’; NM SITES ———- ————————————————– Cooter [“Hospital”,”Hospital”] The underlying JSON is this: {“Name”:”Cooter”, “EMPI”:653998, “MRNS”:[{“Site”:”Hospital”,”MRN”:8753}, {“Site”:”Hospital”,”MRN”:44428}]} This patient has two MRN’s from Hospital but the … Read more
After several decades working with Relational Data world-renowned developer Austin Tatious has started a new position working with JSON. He’s working for a patient patient management software company. Being a self starter he dives right in to check what tables exist in the database. SELECT table_name FROM user_tables; TABLE_NAME —————————— PATIENT_DATA Only one table? He … Read more
Not content to just put EMPI keys and values in disparate places in the JSON some applications started entering EMPI as objects. INSERT INTO json_patient VALUES(‘{“EMPI”:{“ISSUER”:”HOSPITALB”,”NUMBER”:88}}’); My table function tried its best… SELECT * FROM TABLE(add_empi) WHERE empi = ‘$.EMPI’; PATIENTDATA EMPI ———————————————- ——————————— {“pname”:”Rosco Coltrane1″,”EMPI”:1} $.EMPI {“EMPI”:{“ISSUER”:”HOSPITALB”,”NUMBER”:88}} $.EMPI It did indeed find EMPI but … Read more
My first JSON DB project did not go well because the data structure (or lack thereof) quickly overwhelmed the applications ability to process it. If I had it to do over again I would have put more of the application in the database to simplify and manage access. That experience inspires this series of posts … Read more
Another option for synchronizing JSON Search Indexes is Manual which is enabled by altering the meta-data of the index like this: ALTER INDEX jindex PARAMETERS (‘replace metadata sync (manual)’); Now when DML occurs… INSERT INTO jtable VALUES(‘{“JTOP”:3}’); SELECT * FROM jtable WHERE JSON_EXISTS(jcol,’$.JTOP’); JCOL ——————- {“JTOP”:1} {“JTOP”:2} It will not show up in queries (even … Read more
I was reading this article by Roger Ford and one statement caught my eye “You may notice that if you insert a document but do not commit it, some searches using a search index will not find it, because the index has not yet been synchronized.” I quickly ran a test for this situation. I … Read more
Its time to deal with the inevitable complex changes to the database. I’ll start working through the JSON changes with this post that handles the following: Non-Unique Patient Names Initially the patient management system was used to maintain Ordering System (OS) and Medical Record Number (MRN) combinations for unique patients (by name). But patient management … Read more
Its time to deal with the inevitable complex changes to the database. I’ll start working through the RDBMS changes with this post that handles the following: Non-Unique Patient Names Initially the patient management system was used to maintain Ordering System (OS) and Medical Record Number (MRN) combinations for unique patients (by name). But patient management … Read more