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 Go to next message
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.
  • Attachment: Document1.txt
    (Size: 29.65KB, Downloaded 1892 times)
Re: XMLAGG for clob fields [message #597089 is a reply to message #597064] Mon, 30 September 2013 16:15 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9077
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 &gt; 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 &lt; 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 &gt; 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 &lt; 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 &gt; 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 &lt; 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 &gt; 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 &lt;
 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 Go to previous messageGo to next message
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 #597101 is a reply to message #597100] Mon, 30 September 2013 22:38 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9077
Registered: November 2002
Location: California, USA
Senior Member
It seems that when you try to concatenate that many records, the value is just too big. The full text of the error message is:

ORA-22813: operand value exceeds system limits
    Cause: Object or Collection value was too large. The size of the value might have exceeded 30k in a SORT context, or the size might be too big for available memory.
    Action: Choose another value and retry the operation.


I can't think of a workaround, but maybe someone else can. With something that large, I think you will have a problem no matter what method you use. I find it hard to envision why you need something like this.
Re: XMLAGG for clob fields [message #597197 is a reply to message #597101] Tue, 01 October 2013 09:49 Go to previous messageGo to next message
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 #597225 is a reply to message #597197] Tue, 01 October 2013 13:59 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9077
Registered: November 2002
Location: California, USA
Senior Member
Looking back at your code in your second post, I just noticed that you have:

FETCH C_NOTES BULK COLLECT INTO L_TT LIMIT 7500;

I wonder if this might be where the error occurs. Do you get the same error if you set the limit to 1? If not, then experiment until you find a realistic limit. 100 is a common standard limit.
Re: XMLAGG for clob fields [message #597229 is a reply to message #597225] Tue, 01 October 2013 14:54 Go to previous message
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.
Previous Topic: XML Decode Statement
Next Topic: XML -- NOT A SINGLE GROUP GROUP FUNCTION
Goto Forum:
  


Current Time: Thu Mar 28 20:36:09 CDT 2024