Because the world needs more PL/SQL blogs
Life long RDBMS developer Austin Tacious has quickly settled into his new job developing JSON applications. The work has been writing and tuning queries just like in the RDBMS world so the transition was easy. All is well until he overhears his manager discussing some pending database structure changes. Being a self starter he sets … Read more
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