Home » Developer & Programmer » JDeveloper, Java & XML » XMLAGG for clob fields (Oracle 11g, XP)
| XMLAGG for clob fields [message #597064] |
Mon, 30 September 2013 12:43  |
rajivn786
Messages: 161
Registered: January 2010
|
Senior Member |
|
|
Hi,
I am having an issue as to how we can resolve the error ..
ORA-22813 operand value exceeds system limits
I think XMLAGG on clob is creating this issue.
Create table statements :
Create table x
as
Select '42' id, to_clob(null) notes from dual;
[b]Please replace the to_clob(null) with sheet attached.[/b]
I am just attaching just one clob record for notes column.
SELECT XMLElement("Notes",
(XMLAGG(XMLELEMENT("Text", XMLELEMENT("CHUNK", Notes))))) -- Issue here..I think.
from x
How do we use XMLAGG for clob fields?? (Notes is a clob field from table x)
Thanks.
|
|
|
|
| Re: XMLAGG for clob fields [message #597089 is a reply to message #597064] |
Mon, 30 September 2013 16:15  |
 |
Barbara Boehmer
Messages: 9104
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
I am not getting the error that you are getting with just what you have posted. However, you did not specify how you loaded your document1.txt. Please see if you can post a copy and paste of something like the following. With just one document, one row in the table, you should not be exceeding any limits. The problem is not using a clob with xmlelement or xmlagg, but is something else, such as how you loaded your file or so many rows containing big files that you have exceeded some memory limit or some such thing.
SCOTT@orcl12c> CREATE TABLE x AS SELECT '42' id, TO_CLOB (NULL) notes FROM DUAL
2 /
Table created.
SCOTT@orcl12c> SELECT XMLElement("Notes",
2 (XMLAGG(XMLELEMENT("Text", XMLELEMENT("CHUNK", Notes))))) -- Issue here..I think.
3
4 from x
5 /
XMLELEMENT("NOTES",(XMLAGG(XMLELEMENT("TEXT",XMLELEMENT("CHUNK",NOTES)))))--ISSU
--------------------------------------------------------------------------------
<Notes><Text><CHUNK></CHUNK></Text></Notes>
1 row selected.
SCOTT@orcl12c> CREATE OR REPLACE DIRECTORY my_dir AS 'c:\my_oracle_files'
2 /
Directory created.
SCOTT@orcl12c> DECLARE
2 v_clob CLOB;
3 v_bfile BFILE := BFILENAME ('MY_DIR', 'document1.txt');
4 v_dest_offset INTEGER := 1;
5 v_src_offset INTEGER := 1;
6 v_bfile_csid NUMBER := DBMS_LOB.DEFAULT_CSID;
7 v_lang_context INTEGER := DBMS_LOB.DEFAULT_LANG_CTX;
8 v_warning INTEGER;
9 BEGIN
10 DBMS_LOB.CREATETEMPORARY (v_clob, FALSE);
11 DBMS_LOB.OPEN (v_bfile);
12 DBMS_LOB.LOADCLOBFROMFILE
13 (v_clob,
14 v_bfile,
15 DBMS_LOB.GETLENGTH (v_bfile),
16 v_dest_offset,
17 v_src_offset,
18 v_bfile_csid,
19 v_lang_context,
20 v_warning);
21 UPDATE x SET notes = v_clob WHERE id = '42';
22 DBMS_LOB.CLOSE (v_bfile);
23 DBMS_LOB.FREETEMPORARY (v_clob);
24 END;
25 /
PL/SQL procedure successfully completed.
SCOTT@orcl12c> SELECT id, DBMS_LOB.GETLENGTH (notes) FROM x
2 /
ID DBMS_LOB.GETLENGTH(NOTES)
-- -------------------------
42 30365
1 row selected.
SCOTT@orcl12c> SELECT XMLElement("Notes",
2 (XMLAGG(XMLELEMENT("Text", XMLELEMENT("CHUNK", Notes))))) -- Issue here..I think.
3
4 from x
5 /
XMLELEMENT("NOTES",(XMLAGG(XMLELEMENT("TEXT",XMLELEMENT("CHUNK",NOTES)))))--ISSU
--------------------------------------------------------------------------------
<Notes><Text><CHUNK>History: Chief Complaint: CKD PKD HPI: Charlene McDona
ld is a 52 y.o. female who returns for follow up of: Stage III-IV CKD secondary
to PKD. Procardia severe headaches; norvasc didnt work after a few years;
Noticed very salt sensitive. Drinks > 40 ounces of fluid per day.
Has abd fullnessseeing GI for EGD. Increased fullness with meals. Pt denie
s nausea, vomiting, MS change, fatigue, or bitter taste in mouth. Patient has
no dysuria, increased frequency, hematuria, or foamy urine. Little less stre
ss recently. Son who is 27 years old has moved out of house as planned, living
with girlfriend. In January, BP high . 2 week ago, ER with BP 220/108 and
txed with labetolol shots. Had CT with hemangioma as in old imaging. Labet
olol 300 mg tid. Diovan 40 mg daily. Notices she is very salt sensitive. Fr
iend is asking for Tx # because she is willing to donate. But pt has not given
the # yet. Bp was very high one time because forgot to take labetolol on time
, so now knows to take on time. Review Of Systems: Complete ROS performed.
All systems negative except as detailed in HPI or below. PMH: history reviewe
d and without changes. Patient has a past medical history of Polycystic kidney
disease; HEMANGIOMA; and Gout (12/15/2011). Family History: Reviewed and wit
hout changes. Patient family history includes Dialysis in her mother and Polycy
stic Kidney Disease in her mother, others, and son. Social History: Reviewed
and without changes. She reports that she has never smoked. She does not have
any smokeless tobacco history on file. She reports that she does not drink alco
hol or use illicit drugs. Her sexual activity history not on file. Medication
s: Medications - Previous to this Encounter Medication Sig Dispense Refill
? labetalol 100 mg Oral tablet Take 4 tablets by mouth every 8 hours. 360 table
t 5 ? calcium carbonate 600 mg (1,500 mg) Oral tablet Take 1 tablet by mouth
3 times a day with meals. 90 tablet 5 ? valsartan (DIOVAN) 40 mg Oral tablet
Take 1 tablet by mouth daily. 30 tablet 11 ? cholecalciferol (VITAMIN D3) 1
,000 unit Oral capsule Take 1,000 Units by mouth daily. 30 capsule 5 ? sodiu
m bicarbonate 650 mg Oral Tab Take 1 tablet by mouth 3 times a day. 90 tablet
5 ? Simethicone 80 mg Oral Chew Take 1 Tab by mouth every 8 hours as needed fo
r Gas. 90 5 ? Multivitamin Oral Tab take 1 tablet by oral route once daily
with food 30 5 Physical Exam: Multi-System Exam: BP 180/110 | Pul
se 78 | Ht 5 5 | Wt 72.576 kg (160 lb) | BMI 26.63 kg/m2 Body mass index is 26.
63 kg/(m^2). Physical Exam: Constitutional: no acute distress and well de
veloped/well nourished Eyes: lids/conjuctiva normal, anicteric and normal exam
Ears/Nose/Mouth/Throat: oropharynx pink and dry mucous membranes. Cardiovascul
ar: normal s1, s2, no murmurs, no pericardial friction rub and no gallops Respi
ratory: clear to auscultation bilaterally, no wheezing and no rales Abdomen: N
ormoactive bowel sounds, nontender, nondistended. No rebound or guarding. + pal
pable kidneys. Back: no costo-vertebral tenderness (CVAT) Musculoskeletal: no
rmal gait. Tense trapezius muscles Lower Extremity: no peripheral edema. Neu
rologic: alert, awake and oriented times three (AAand 0 x3), normal light touch
sensation, normal strength and no asterixis Lymphatic: no cervical nodes palpa
ted Psychiatric: normal mood/affect, non-anxious and normal judgement and insig
ht Skin: no rash Medical Decision Making: Data Review: UREA NITROGE
N Date Value Range Status 1/2/2013 57* 10 - 30 MG/DL Final CREATININE
Date Value Range Status 1/2/2013 5.59* 0.50 - 1.30 MG/DL Final ALBUMIN
Date Value Range Status 1/2/2013 4.4 3.5 - 4.9 G/DL Final CALCIUM
Date Value Range Status 1/2/2013 9.2 8.5 - 10.5 MG/DL Final _ PHOS
PHORUS Date Value Range Status 12/26/2012 4.1 2.4 - 4.7 MG/DL Final N
o results found for this basename: URICACID MAGNESIUM Date Value Range S
tatus 5/19/2011 2.1 1.5 - 2.5 MG/DL Final NOTE: As of 7/16/09 the new re
ference range for Age 0-10 is 1.5 to 2.5 mg/dl GLUCOSE Date Value Range
Status 1/2/2013 81 65 - 139 MG/DL Final NON-FASTING 65-139 mg/dL FA
STING 65 - 99 mg/dL No components found with this basename: VITAMIND12
INTACT PARATHYROID HORMONE Date Value Range Status 12/26/2012 201* 16 - 8
7 PG/ML Final WHITE BLOOD CELL Date Value Range Status 12/26/2012 4.3*
4.5 - 11.0 x10 3/uL Final HEMOGLOBIN Date Value Range Status 12/26/20
12 10.1* 11.7 - 15.0 G/DL Final No components found with this basename: HC
T PLATELET Date Value Range Status 12/26/2012 199 150 - 450 x10 3/uL
Final No components found with this basename: PROCREA No components
found with this basename: IMMUNOFIXSER No components found with this basen
ame: IMMUNOFIXURI No results found for this basename: C3 No results
found for this basename: C4 No results found for this basename: ANAQUANT
HEPATITIS BS AG Date Value Range Status 5/19/2011 NON-REACTIVE NON-REA
CTIVE Final HEPATITIS C VIRUS AB Date Value Range Status 5/19/2011 NON
-REACTIVE NON-REACTIVE Final No components found with this basename: CRYO
GLOB TSH Date Value Range Status 12/26/2012 1.10 0.34 - 5.60 uIU/Ml F
inal Reference Ranges: Age: Female: Male: 1-
25 days 1.5-6.5 0.7-9.8 1-24 months 1.6-5.7 0.7-5.9 9-
10 years 0.9-4.0 1.0-3.7 11-14 years 0.7-3.4 0.8-3.9 15
-17 years 0.6-3.7 0.7-2.8 18-99 years 0.34-5.60 0.34-5.60
No results found for this basename: ANCA Laboratory: Cr was 4.54 A
ssessment/Plan: 1. CKD (chronic kidney disease) stage 4, GFR 15-29 ml/min URIC
ACID-BLD, URINALYSIS, ROUTINE, VITAMIN D 1, 25-DIHYDROXY, VITAMIN D, 25-HYDROXY
, CK(CPK)-BLD, UREA-URINE RANDOM, CBC+PLT+DIFF, COMP METABOLIC PANEL(AKA CMP), E
LECTROLYTES-URINE, MICROALBUMIN-URINE RANDOM, CREATININE-URINE RANDOM, MICROALB/
CREAT,URINE (RAND), PARATHYROID HORMONE INTACT IRMA, PHOSPHORUS-BLOOD, PROTEIN/C
REA.RATIO,URINE 2. Anemia VITAMIN B12 and FOLATE-SERUM, FERRITIN, IRON, IRON
+ TIBC 3. Hypertension LIPID PANEL 4. Fatigue TSH 52 F with PKD, CK
D IV, HTN, tension headaches. HTN: Goal BP < or = 130/80 for maximal r
enal preservation. Pt feels she is salt sensitive, but has not increased salt i
ntake. Bp well controlled today. D/w pt re: taking labetolol every 12 ho
urs to avoid rebound HTN, to keep a few pills in her bag always. CKD: From
PKD. Pt now on wait list for kidney tx. She does not have a potential living
donor but friend is interested in work uppt has not given her the # yet. Friend
asked againencouraged work up early to see if she is a match so if needed, can
do preemptive tx. Discussed with patient to avoid NSAIDS, Fleets phosphosoda,
iv contrast for CT or angiograms if possible (and re: use of N-acetylcysteine f
or prophylaxis if contrast is needed), given CKD. Fatigue:Improved. Anem
ia: Check fe studies, vitamin b12, folate. Gout: Check uric acid, esr, crp
. F/u in3 months. History: Chi
ef Complaint: CKD PKD HPI: Charlene McDonald is a 52 y.o. female who returns f
or follow up of: Stage III-IV CKD secondary to PKD. Procardia severe headac
hes; norvasc didnt work after a few years; Noticed very salt sensitive.
Drinks > 40 ounces of fluid per day. Abd painLeft side closer to the fron
t. Lasted all night at about 10/10. Took 2 tylenol and now is 3-4 out of 10 pain
. No dysuria, hematuria, no fevers. Likely, burst cyst from PKD. No gravel, hema
turia, or radiation to groin, May 13th had bad headache, and BP was 210. Gav
e her reglan and it helped the headache. BP 190s. Was anxious. Yesterday, BP
150/100. Saw Dr. Duddenpuddi who told her that her EKG was irregularcardiol
ogist said it may have been related to the way she was breathing. Weight 182,
BP was 160. Took extra labetolol. Eating well, and on weight watcherlost 8 pound
s over 6 weeks. Avg 150-160 now. When taking diovan 40 mg (instead of the prescr
ibed 20 mg), BP can decrease to 140s. H/o diverticulitis 4 years agowas in ER
and given fluids. Has had granola bar with nuts and seeds over 4 weeks.
Review Of Systems: Complete ROS performed. All systems negative except as deta
iled in HPI or below. PMH: history reviewed and without changes. Patient ha
s a past medical history of Polycystic kidney disease; HEMANGIOMA; and Gout (12/
15/2011). Family History: Reviewed and without changes. Patient family histo
ry includes Dialysis in her mother and Polycystic Kidney Disease in her mother,
others, and son. Social History: Reviewed and without changes. She reports
that she has never smoked. She does not have any smokeless tobacco history on f
ile. She reports that she does not drink alcohol or use illicit drugs. Her sexua
l activity history not on file. Medications: Medications - Previous to this
Encounter Medication Sig Dispense Refill ? labetalol 100 mg Oral tablet Take
4 tablets by mouth every 8 hours. 360 tablet 5 ? calcium carbonate 600 mg (
1,500 mg) Oral tablet Take 1 tablet by mouth 3 times a day with meals. 90 table
t 5 ? valsartan (DIOVAN) 40 mg Oral tablet Take 1 tablet by mouth daily. 30
tablet 11 ? cholecalciferol (VITAMIN D3) 1,000 unit Oral capsule Take 1,000 U
nits by mouth daily. 30 capsule 5 ? sodium bicarbonate 650 mg Oral Tab Take
1 tablet by mouth 3 times a day. 90 tablet 5 ? Simethicone 80 mg Oral Chew T
ake 1 Tab by mouth every 8 hours as needed for Gas. 90 5 ? Multivitamin Ora
l Tab take 1 tablet by oral route once daily with food 30 5 Physical E
xam: Multi-System Exam: BP 180/110 | Pulse 78 | Ht 5 5 | Wt 72.576 kg (160
lb) | BMI 26.63 kg/m2 Body mass index is 26.63 kg/(m^2). Physical Exam:
Constitutional: no acute distress and well developed/well nourished Eyes: lids/
conjuctiva normal, anicteric and normal exam Ears/Nose/Mouth/Throat: oropharynx
pink and dry mucous membranes. Cardiovascular: normal s1, s2, no murmurs, no p
ericardial friction rub and no gallops Respiratory: clear to auscultation bilat
erally, no wheezing and no rales Abdomen: Normoactive bowel sounds, nontender,
nondistended. No rebound or guarding. + palpable kidneys. Back: no costo-ver
tebral tenderness (CVAT) Musculoskeletal: normal gait. Tense trapezius muscles
Lower Extremity: no peripheral edema. Neurologic: alert, awake and oriented
times three (AAand 0 x3), normal light touch sensation, normal strength and no
asterixis Lymphatic: no cervical nodes palpated Psychiatric: normal mood/affec
t, non-anxious and normal judgement and insight Skin: no rash Medical Deci
sion Making: Data Review: UREA NITROGEN Date Value Range Status 1/2/2
013 57* 10 - 30 MG/DL Final CREATININE Date Value Range Status 1/2/201
3 5.59* 0.50 - 1.30 MG/DL Final ALBUMIN Date Value Range Status 1/2/20
13 4.4 3.5 - 4.9 G/DL Final CALCIUM Date Value Range Status 1/2/2013
9.2 8.5 - 10.5 MG/DL Final _ PHOSPHORUS Date Value Range Status
12/26/2012 4.1 2.4 - 4.7 MG/DL Final No results found for this basename:
URICACID MAGNESIUM Date Value Range Status 5/19/2011 2.1 1.5 - 2.5 MG
/DL Final NOTE: As of 7/16/09 the new reference range for Age 0-10 is 1.5 t
o 2.5 mg/dl GLUCOSE Date Value Range Status 1/2/2013 81 65 - 139 MG/D
L Final NON-FASTING 65-139 mg/dL FASTING 65 - 99 mg/dL No compon
ents found with this basename: VITAMIND12 INTACT PARATHYROID HORMONE Dat
e Value Range Status 12/26/2012 201* 16 - 87 PG/ML Final WHITE BLOOD CEL
L Date Value Range Status 12/26/2012 4.3* 4.5 - 11.0 x10 3/uL Final HE
MOGLOBIN Date Value Range Status 12/26/2012 10.1* 11.7 - 15.0 G/DL Final
No components found with this basename: HCT PLATELET Date Value Range
Status 12/26/2012 199 150 - 450 x10 3/uL Final No components found wit
h this basename: PROCREA No components found with this basename: IMMUNOFIX
SER No components found with this basename: IMMUNOFIXURI No results
found for this basename: C3 No results found for this basename: C4 N
o results found for this basename: ANAQUANT HEPATITIS BS AG Date Value R
ange Status 5/19/2011 NON-REACTIVE NON-REACTIVE Final HEPATITIS C VIRUS
AB Date Value Range Status 5/19/2011 NON-REACTIVE NON-REACTIVE Final
No components found with this basename: CRYOGLOB TSH Date Value Range S
tatus 12/26/2012 1.10 0.34 - 5.60 uIU/Ml Final Reference Ranges:
Age: Female: Male: 1-25 days 1.5-6.5 0.7-9.8
1-24 months 1.6-5.7 0.7-5.9 9-10 years 0.9-4.0 1.0-3.7
11-14 years 0.7-3.4 0.8-3.9 15-17 years 0.6-3.7 0.7-2.8
18-99 years 0.34-5.60 0.34-5.60 No results found for this basename
: ANCA Laboratory: Cr was 4.54 Assessment/Plan: 1. CKD (chronic ki
dney disease) stage 4, GFR 15-29 ml/min URIC ACID-BLD, URINALYSIS, ROUTINE, VIT
AMIN D 1, 25-DIHYDROXY, VITAMIN D, 25-HYDROXY, CK(CPK)-BLD, UREA-URINE RANDOM, C
BC+PLT+DIFF, COMP METABOLIC PANEL(AKA CMP), ELECTROLYTES-URINE, MICROALBUMIN-URI
NE RANDOM, CREATININE-URINE RANDOM, MICROALB/CREAT,URINE (RAND), PARATHYROID HOR
MONE INTACT IRMA, PHOSPHORUS-BLOOD, PROTEIN/CREA.RATIO,URINE 2. Anemia VITAMI
N B12 and FOLATE-SERUM, FERRITIN, IRON, IRON + TIBC 3. Hypertension LIPID PA
NEL 4. Fatigue TSH 51 F with PKD, CKD IV, HTN, tension headaches. H
TN: Goal BP < or = 130/80 for maximal renal preservation. Pt feels she is
salt sensitive, but has not increased salt intake. Headache may be from hi
gher BP, or BP may be high due to tension headache. D/w pt re: supportive measu
res (warm pack, shower water, bengay, massage) to help decrease tension in trape
zius muscle. And f/u with BP. In meantime, can take diovan 40 mg daily for no
w as it seems to help BP significantly. D/w pt re: Cr can increase with higher
diovan, but that high BP itself can increase Cr. Will need to determine f/u as
we see how Bp does. But will need repeat labs in about 2 weeks on increased di
ovan if she still requires higher dose diovan even with supportive methods to de
crease tension headaches. CKD: From PKD. Pt now on wait list for kidney t
x. She does not have a potential living donor. Discussed with patient to a
void NSAIDS, Fleets phosphosoda, iv contrast for CT or angiograms if possible (a
nd re: use of N-acetylcysteine for prophylaxis if contrast is needed), given CKD
. D/w pt re: timing of HD. But with friend who wants to donate kidneyd/w pt
re: preemptive Tx is best option. To call Tx office to inquire instructions for
friends workup - Abdominal fullness: GI doing EGD. Gave lab copy to pt. M
ay be form enlarged kidneys. 2008 Us with 19 amnd 17 cm kidneysct 2011 with 17 c
m. Repeat u/s to eval kidney sizes. Fullness may be from kidney size if GI w/u
is neg. Anemia: Check fe studies, vitamin b12, folate. F/u in 2 months.
History: Chief Complaint: CKD PKD H
PI: Charlene McDonald is a 52 y.o. female who returns for follow up of: Stage
III-IV CKD secondary to PKD. Procardia severe headaches; norvasc didnt work
after a few years; Noticed very salt sensitive. Drinks > 40 ounces o
f fluid per day. Has abd fullnessseeing GI for EGD. Increased fullness with
meals. Pt denies nausea, vomiting, MS change, fatigue, or bitter taste in m
outh. Patient has no dysuria, increased frequency, hematuria, or foamy urine.
Little less stress recently. Son who is 27 years old has moved out of house
as planned, living with girlfriend. Has 2 weeks off for vacation. Seei
ng Dr. Dudempuddi re: hernia and toe which may be fungal. Friend is asking fo
r Tx # because she is willing to donate. But pt has not given the # yet. Bp
was very high one time because forgot to take labetolol on time, so now knows to
take on time. Review Of Systems: Complete ROS performed. All systems negativ
e except as detailed in HPI or below. PMH: history reviewed and without chang
es. Patient has a past medical history of Polycystic kidney disease; HEMANGIOM
A; and Gout (12/15/2011). Family History: Reviewed and without changes. Pati
ent family history includes Dialysis in her mother and Polycystic Kidney Disease
in her mother, others, and son. Social History: Reviewed and without changes
. She reports that she has never smoked. She does not have any smokeless toba
cco history on file. She reports that she does not drink alcohol or use illicit
drugs. Her sexual activity history not on file. Medications: Medications - P
revious to this Encounter Medication Sig Dispense Refill ? labetalol 100 mg
Oral tablet Take 4 tablets by mouth every 8 hours. 360 tablet 5 ? calcium ca
rbonate 600 mg (1,500 mg) Oral tablet Take 1 tablet by mouth 3 times a day with
meals. 90 tablet 5 ? valsartan (DIOVAN) 40 mg Oral tablet Take 1 tablet by m
outh daily. 30 tablet 11 ? cholecalciferol (VITAMIN D3) 1,000 unit Oral caps
ule Take 1,000 Units by mouth daily. 30 capsule 5 ? sodium bicarbonate 650 m
g Oral Tab Take 1 tablet by mouth 3 times a day. 90 tablet 5 ? Simethicone 8
0 mg Oral Chew Take 1 Tab by mouth every 8 hours as needed for Gas. 90 5 ?
Multivitamin Oral Tab take 1 tablet by oral route once daily with food 30 5
Physical Exam: Multi-System Exam: BP 180/110 | Pulse 78 | Ht 5 5 | Wt
72.576 kg (160 lb) | BMI 26.63 kg/m2 Body mass index is 26.63 kg/(m^2).
Physical Exam: Constitutional: no acute distress and well developed/well nouris
hed Eyes: lids/conjuctiva normal, anicteric and normal exam Ears/Nose/Mouth/Th
roat: oropharynx pink and dry mucous membranes. Cardiovascular: normal s1, s2,
no murmurs, no pericardial friction rub and no gallops Respiratory: clear to au
scultation bilaterally, no wheezing and no rales Abdomen: Normoactive bowel so
unds, nontender, nondistended. No rebound or guarding. + palpable kidneys. Ba
ck: no costo-vertebral tenderness (CVAT) Musculoskeletal: normal gait. Tense t
rapezius muscles Lower Extremity: no peripheral edema. Neurologic: alert, awa
ke and oriented times three (AAand 0 x3), normal light touch sensation, normal
strength and no asterixis Lymphatic: no cervical nodes palpated Psychiatric: n
ormal mood/affect, non-anxious and normal judgement and insight Skin: no rash
Medical Decision Making: Data Review: UREA NITROGEN Date Value Rang
e Status 1/2/2013 57* 10 - 30 MG/DL Final CREATININE Date Value Range
Status 1/2/2013 5.59* 0.50 - 1.30 MG/DL Final ALBUMIN Date Value Range
Status 1/2/2013 4.4 3.5 - 4.9 G/DL Final CALCIUM Date Value Range St
atus 1/2/2013 9.2 8.5 - 10.5 MG/DL Final _ PHOSPHORUS Date Value
Range Status 12/26/2012 4.1 2.4 - 4.7 MG/DL Final No results found for
this basename: URICACID MAGNESIUM Date Value Range Status 5/19/2011 2
.1 1.5 - 2.5 MG/DL Final NOTE: As of 7/16/09 the new reference range for A
ge 0-10 is 1.5 to 2.5 mg/dl GLUCOSE Date Value Range Status 1/2/2013 8
1 65 - 139 MG/DL Final NON-FASTING 65-139 mg/dL FASTING 65 - 99 mg/dL
No components found with this basename: VITAMIND12 INTACT PARATHYRO
ID HORMONE Date Value Range Status 12/26/2012 201* 16 - 87 PG/ML Final
WHITE BLOOD CELL Date Value Range Status 12/26/2012 4.3* 4.5 - 11.0 x10 3/u
L Final HEMOGLOBIN Date Value Range Status 12/26/2012 10.1* 11.7 - 15.
0 G/DL Final No components found with this basename: HCT PLATELET
Date Value Range Status 12/26/2012 199 150 - 450 x10 3/uL Final No comp
onents found with this basename: PROCREA No components found with this bas
ename: IMMUNOFIXSER No components found with this basename: IMMUNOFIXURI
No results found for this basename: C3 No results found for this base
name: C4 No results found for this basename: ANAQUANT HEPATITIS BS A
G Date Value Range Status 5/19/2011 NON-REACTIVE NON-REACTIVE Final H
EPATITIS C VIRUS AB Date Value Range Status 5/19/2011 NON-REACTIVE NON-REAC
TIVE Final No components found with this basename: CRYOGLOB TSH Da
te Value Range Status 12/26/2012 1.10 0.34 - 5.60 uIU/Ml Final Reference
Ranges: Age: Female: Male: 1-25 days 1.5-6
.5 0.7-9.8 1-24 months 1.6-5.7 0.7-5.9 9-10 years 0.9-4
.0 1.0-3.7 11-14 years 0.7-3.4 0.8-3.9 15-17 years 0.6-3
.7 0.7-2.8 18-99 years 0.34-5.60 0.34-5.60 No results found f
or this basename: ANCA Laboratory: Cr was 4.54 Assessment/Plan: 1.
CKD (chronic kidney disease) stage 4, GFR 15-29 ml/min URIC ACID-BLD, URINALYS
IS, ROUTINE, VITAMIN D 1, 25-DIHYDROXY, VITAMIN D, 25-HYDROXY, CK(CPK)-BLD, UREA
-URINE RANDOM, CBC+PLT+DIFF, COMP METABOLIC PANEL(AKA CMP), ELECTROLYTES-URINE,
MICROALBUMIN-URINE RANDOM, CREATININE-URINE RANDOM, MICROALB/CREAT,URINE (RAND),
PARATHYROID HORMONE INTACT IRMA, PHOSPHORUS-BLOOD, PROTEIN/CREA.RATIO,URINE 2
. Anemia VITAMIN B12 and FOLATE-SERUM, FERRITIN, IRON, IRON + TIBC 3. Hypert
ension LIPID PANEL 4. Fatigue TSH 52 F with PKD, CKD IV, HTN, tension
headaches. HTN: Goal BP < or = 130/80 for maximal renal preservation.
Pt feels she is salt sensitive, but has not increased salt intake. Bp well
controlled today. D/w pt re: taking labetolol every 12 hours to avoid reboun
d HTN, to keep a few pills in her bag always. CKD: From PKD. Pt now on wa
it list for kidney tx. She does not have a potential living donor but friend is
interested in work uppt has not given her the # yet. Friend asked againencoura
ged work up early to see if she is a match so if needed, can do preemptive tx.
Discussed with patient to avoid NSAIDS, Fleets phosphosoda, iv contrast for CT
or angiograms if possible (and re: use of N-acetylcysteine for prophylaxis if c
ontrast is needed), given CKD. Fatigue:Improved. Anemia: Check fe studi
es, vitamin b12, folate. Gout: Check uric acid, esr, crp. F/u in3 mo
nths. History: Chief Complaint: CKD P
KD HPI: Charlene McDonald is a 52 y.o. female who returns for follow up of: S
tage III-IV CKD secondary to PKD. Procardia severe headaches; norvasc didnt
work after a few years; Noticed very salt sensitive. Drinks > 40 oun
ces of fluid per day. Abd painLeft side closer to the front. Lasted all night
at about 10/10. Took 2 tylenol and now is 3-4 out of 10 pain. No dysuria, hemat
uria, no fevers. Likely, burst cyst from PKD. No gravel, hematuria, or radiation
to groin, May 13th had bad headache, and BP was 210. Gave her reglan and it
helped the headache. BP 190s. Was anxious. Yesterday, BP 150/100. Saw D
r. Duddenpuddi who told her that her EKG was irregularcardiologist said it may h
ave been related to the way she was breathing. Weight 182, BP was 160. Took ex
tra labetolol. Eating well, and on weight watcherlost 8 pounds over 6 weeks. Avg
150-160 now. When taking diovan 40 mg (instead of the prescribed 20 mg), BP can
decrease to 140s. H/o diverticulitis 4 years agowas in ER and given fluids.
Has had granola bar with nuts and seeds over 4 weeks. Review Of Systems:
Complete ROS performed. All systems negative except as detailed in HPI or belo
w. PMH: history reviewed and without changes. Patient has a past medical hi
story of Polycystic kidney disease; HEMANGIOMA; and Gout (12/15/2011). Family
History: Reviewed and without changes. Patient family history includes Dialysi
s in her mother and Polycystic Kidney Disease in her mother, others, and son.
Social History: Reviewed and without changes. She reports that she has never
smoked. She does not have any smokeless tobacco history on file. She reports th
at she does not drink alcohol or use illicit drugs. Her sexual activity history
not on file. Medications: Medications - Previous to this Encounter Medicat
ion Sig Dispense Refill ? labetalol 100 mg Oral tablet Take 4 tablets by mouth
every 8 hours. 360 tablet 5 ? calcium carbonate 600 mg (1,500 mg) Oral tabl
et Take 1 tablet by mouth 3 times a day with meals. 90 tablet 5 ? valsartan
(DIOVAN) 40 mg Oral tablet Take 1 tablet by mouth daily. 30 tablet 11 ? chol
ecalciferol (VITAMIN D3) 1,000 unit Oral capsule Take 1,000 Units by mouth daily
. 30 capsule 5 ? sodium bicarbonate 650 mg Oral Tab Take 1 tablet by mouth 3
times a day. 90 tablet 5 ? Simethicone 80 mg Oral Chew Take 1 Tab by mouth
every 8 hours as needed for Gas. 90 5 ? Multivitamin Oral Tab take 1 tablet
by oral route once daily with food 30 5 Physical Exam: Multi-Syst
em Exam: BP 180/110 | Pulse 78 | Ht 5 5 | Wt 72.576 kg (160 lb) | BMI 26.63 kg/
m2 Body mass index is 26.63 kg/(m^2). Physical Exam: Constitutional: no
acute distress and well developed/well nourished Eyes: lids/conjuctiva normal,
anicteric and normal exam Ears/Nose/Mouth/Throat: oropharynx pink and dry mucou
s membranes. Cardiovascular: normal s1, s2, no murmurs, no pericardial friction
rub and no gallops Respiratory: clear to auscultation bilaterally, no wheezing
and no rales Abdomen: Normoactive bowel sounds, nontender, nondistended. No r
ebound or guarding. + palpable kidneys. Back: no costo-vertebral tenderness (
CVAT) Musculoskeletal: normal gait. Tense trapezius muscles Lower Extremity:
no peripheral edema. Neurologic: alert, awake and oriented times three (AAand
0 x3), normal light touch sensation, normal strength and no asterixis Lymphati
c: no cervical nodes palpated Psychiatric: normal mood/affect, non-anxious and
normal judgement and insight Skin: no rash Medical Decision Making: Da
ta Review: UREA NITROGEN Date Value Range Status 1/2/2013 57* 10 - 30 MG/
DL Final CREATININE Date Value Range Status 1/2/2013 5.59* 0.50 - 1.30
MG/DL Final ALBUMIN Date Value Range Status 1/2/2013 4.4 3.5 - 4.9 G
/DL Final CALCIUM Date Value Range Status 1/2/2013 9.2 8.5 - 10.5 MG/
DL Final _ PHOSPHORUS Date Value Range Status 12/26/2012 4.1 2.4
- 4.7 MG/DL Final No results found for this basename: URICACID MAGN
ESIUM Date Value Range Status 5/19/2011 2.1 1.5 - 2.5 MG/DL Final NOTE
: As of 7/16/09 the new reference range for Age 0-10 is 1.5 to 2.5 mg/dl G
LUCOSE Date Value Range Status 1/2/2013 81 65 - 139 MG/DL Final NON-FA
STING 65-139 mg/dL FASTING 65 - 99 mg/dL No components found with thi
s basename: VITAMIND12 INTACT PARATHYROID HORMONE Date Value Range Statu
s 12/26/2012 201* 16 - 87 PG/ML Final WHITE BLOOD CELL Date Value Rang
e Status 12/26/2012 4.3* 4.5 - 11.0 x10 3/uL Final HEMOGLOBIN Date Val
ue Range Status 12/26/2012 10.1* 11.7 - 15.0 G/DL Final No components fo
und with this basename: HCT PLATELET Date Value Range Status 12/26/201
2 199 150 - 450 x10 3/uL Final No components found with this basename: PR
OCREA No components found with this basename: IMMUNOFIXSER No compon
ents found with this basename: IMMUNOFIXURI No results found for this base
name: C3 No results found for this basename: C4 No results found for
this basename: ANAQUANT HEPATITIS BS AG Date Value Range Status 5/19/
2011 NON-REACTIVE NON-REACTIVE Final HEPATITIS C VIRUS AB Date Value Ra
nge Status 5/19/2011 NON-REACTIVE NON-REACTIVE Final No components foun
d with this basename: CRYOGLOB TSH Date Value Range Status 12/26/2012
1.10 0.34 - 5.60 uIU/Ml Final Reference Ranges: Age:
Female: Male: 1-25 days 1.5-6.5 0.7-9.8 1-24 months
1.6-5.7 0.7-5.9 9-10 years 0.9-4.0 1.0-3.7 11-14 years
0.7-3.4 0.8-3.9 15-17 years 0.6-3.7 0.7-2.8 18-99 years
0.34-5.60 0.34-5.60 No results found for this basename: ANCA Labo
ratory: Cr was 4.54 Assessment/Plan: 1. CKD (chronic kidney disease) stage
4, GFR 15-29 ml/min URIC ACID-BLD, URINALYSIS, ROUTINE, VITAMIN D 1, 25-DIHYDR
OXY, VITAMIN D, 25-HYDROXY, CK(CPK)-BLD, UREA-URINE RANDOM, CBC+PLT+DIFF, COMP M
ETABOLIC PANEL(AKA CMP), ELECTROLYTES-URINE, MICROALBUMIN-URINE RANDOM, CREATINI
NE-URINE RANDOM, MICROALB/CREAT,URINE (RAND), PARATHYROID HORMONE INTACT IRMA, P
HOSPHORUS-BLOOD, PROTEIN/CREA.RATIO,URINE 2. Anemia VITAMIN B12 and FOLATE-S
ERUM, FERRITIN, IRON, IRON + TIBC 3. Hypertension LIPID PANEL 4. Fatigue T
SH 51 F with PKD, CKD IV, HTN, tension headaches. HTN: Goal BP <
or = 130/80 for maximal renal preservation. Pt feels she is salt sensitive, bu
t has not increased salt intake. Cont current BP meds. Add lasix 20 mg bid
or 40 mg daily on days BP is high as pt sometimes see BP 120-130 but increases w
hen eats something salty. BP great when she cooks but cant always . Check weig
tht daily. If no change in BP in 3 days of lasix, call or email. D/w pt to sue
sparinglyonly when needed, as cr may increase with diuretics long term. C
KD: From PKD. Pt now on wait list for kidney tx. Friend in California intere
sted in donating. Discussed with patient to avoid NSAIDS, Fleets phosphosoda,
iv contrast for CT or angiograms if possible (and re: use of N-acetylcysteine f
or prophylaxis if contrast is needed), given CKD. D/w pt re: timing of HD. B
ut with friend who wants to donate kidneyd/w pt re: preemptive Tx is best option
. To call Tx office to inquire instructions for friends workup - Abdominal f
ullness: GI doing EGD. Gave lab copy to pt. May be form enlarged kidneys. 200
8 Us with 19 amnd 17 cm kidneysct 2011 with 17 cm. Repeat u/s to eval kidney si
zes. Fullness may be from kidney size if GI w/u is neg. Anemia: Check fe s
tudies, vitamin b12, folate. F/u in 2 weeks.
</CHUNK></Text></Notes>
1 row selected.
[Updated on: Mon, 30 September 2013 16:23] Report message to a moderator |
|
|
|
| Re: XMLAGG for clob fields [message #597100 is a reply to message #597089] |
Mon, 30 September 2013 21:18 |
rajivn786
Messages: 161
Registered: January 2010
|
Senior Member |
|
|
Thanks for replying back. With your help from previous posts, I achieved a lot in writing complex XML structures.
Coming back to this post :
I have a table which has clob field...(like the above) . This table has close to 6k records.
I am posting the code here...how I loaded that table
create or replace procedure prc_prog_notes
as
V_NOTE_ID VARCHAR2(1000);
V_NOTE_DATE DATE;
V_NOTE_STATUS VARCHAR2(1000);
V_NOTE_EDITOR_ID VARCHAR2(1000);
V_NOTES CLOB;
V_ENTERED_BY VARCHAR2(1000);
V_ENCOUNTER_ID VARCHAR2(1000);
CURSOR C_ALL_ENCOUNTERS IS
SELECT DISTINCT NOTE_ID,
NOTE_DATE,
NOTE_STATUS,
ENTERED_BY,
NOTE_EDITOR_ID,
ENCOUNTER_ID
FROM CM_PROGRESS_NOTES
/* WHERE ENCOUNTER_ID = 16*/;
CURSOR C_NOTES (P_ENC_ID VARCHAR2, P_NOTE_ID VARCHAR2) IS
SELECT
CP.ENCOUNTER_ID,
CP.NOTE_ID,
CP.NOTE_TEXT
FROM CM_PROGRESS_NOTES CP
WHERE CP.ENCOUNTER_ID = P_ENC_ID
AND CP.NOTE_ID = P_NOTE_ID
-- AND NOTE_ID = 18142669
-- AND ENCOUNTER_ID = 16
ORDER BY NOTE_ID, ENCOUNTER_ID, LINE;
TYPE V_TT IS TABLE OF C_NOTES%ROWTYPE INDEX BY PLS_INTEGER;
L_TT V_TT;
BEGIN
-- DBMS_OUTPUT.put_line('BEFORE LOOP');
FOR L1 IN C_ALL_ENCOUNTERS
LOOP
V_ENCOUNTER_ID := L1.ENCOUNTER_ID;
V_NOTE_ID := L1.NOTE_ID;
V_NOTE_DATE := L1.NOTE_DATE;
V_NOTE_STATUS := L1.NOTE_STATUS;
V_NOTE_EDITOR_ID := L1.NOTE_EDITOR_ID;
V_ENTERED_BY := L1.ENTERED_BY;
OPEN C_NOTES(L1.ENCOUNTER_ID,L1.NOTE_ID);
V_NOTES := '';
LOOP
FETCH C_NOTES BULK COLLECT INTO L_TT LIMIT 7500;
FOR indx IN 1 .. L_TT.COUNT
LOOP
V_NOTES := V_NOTES || L_TT(indx).NOTE_TEXT;
END LOOP;
EXIT WHEN L_TT.COUNT = 0;
END LOOP;
CLOSE C_NOTES;
INSERT /*+ append */
INTO PROGRESS_NOTES VALUES (V_ENCOUNTER_ID,V_NOTE_ID,V_NOTE_DATE,V_NOTE_STATUS,
V_NOTE_EDITOR_ID,V_ENTERED_BY,V_NOTES);
COMMIT;
End Loop;
END;
The record which I attached is just a record from it.Table x which I populated is a record from progress notes table.
When I am trying to form the xml using the code above it throws me error:
Create table y as
SELECT XMLElement("Notes",
(XMLAGG(XMLELEMENT("Text", XMLELEMENT("CHUNK", Notes))))) -- Issue here..I think.
from x
You don't get an error with 1 record ...but when I try to do the above with 6k records, it throws me errorthat operand exceeds system limits..
|
|
|
|
|
|
| Re: XMLAGG for clob fields [message #597197 is a reply to message #597101] |
Tue, 01 October 2013 09:49 |
rajivn786
Messages: 161
Registered: January 2010
|
Senior Member |
|
|
This is the actual code...
Select P.PAT_ID,
P.PATIENT_MRN,
(SELECT XMLElement("Notes",
XMLAGG(XMLElement("Note",
XMLElement("HNOID", CP.NOTE_ID),
XMLElement("HNODAT", CP.NOTE_DATE),
XMLElement("OriginalFormat", NULL),
XMLElement("Status", CP.NOTE_STATUS),
XMLElement("Type", NULL),
XMLElement("Sensitive", NULL),
XMLElement("Author",
XMLELEMENT("EMPID",CP.ENTERED_BY),
XMLELEMENT("SERID",CP.ENTERED_BY)),
XMLELEMENT("UpdateUser",CP.NOTE_EDITOR_ID),
xmlagg(XMLELEMENT("Text",
XMLELEMENT("CHUNK", CP.notes))))))
FROM PROGRESS_NOTES CP
WHERE CP.ENCOUNTER_ID = E.ENCOUNTER_ID
GROUP BY CP.NOTE_ID,CP.NOTE_DATE,CP.NOTE_STATUS,CP.ENTERED_BY,CP.NOTE_EDITOR_ID)
AS Orderxml
FROM CM_PATIENT P, CM_ENCOUNTER E
WHERE P.Patient_Mrn = E.Patient_Mrn(+)
For each patient&encounter, I need to send one notes (big chunk).
The max length of notes is 72502.
So what would be your suggestion to send such big data.
|
|
|
|
|
|
| Re: XMLAGG for clob fields [message #597229 is a reply to message #597225] |
Tue, 01 October 2013 14:54 |
rajivn786
Messages: 161
Registered: January 2010
|
Senior Member |
|
|
The error is with the XML & not the PLSQL code.
Anway, I think I resolved the issue.I already aggregated the notes & doing it again..May be that's the issue..
SELECT XMLElement("Notes",
XMLAGG(XMLElement("Note",
XMLElement("HNOID", CP.NOTE_ID),
XMLElement("HNODAT", CP.NOTE_DATE),
XMLElement("OriginalFormat", NULL),
XMLElement("Status", CP.NOTE_STATUS),
XMLElement("Type", NULL),
XMLElement("Sensitive", NULL),
XMLElement("Author",
XMLELEMENT("EMPID",
CP.ENTERED_BY),
XMLELEMENT("SERID",
CP.ENTERED_BY)),
XMLELEMENT("UpdateUser",
CP.NOTE_EDITOR_ID),
XMLELEMENT("Text",
XMLELEMENT("CHUNK", CP.notes)))))
FROM PROGRESS_NOTES CP
WHERE CP.ENCOUNTER_ID = P_ENC_ID;
This one works now...& I am not getting error now.
|
|
|
|
Goto Forum:
Current Time: Thu Feb 06 18:50:19 CST 2025
|
] 
Blog
Search
Help
Members
Register
Login
Home
