I know it sounds a little personal, but I’ve often wanted to look into the contents of some ones UO (user overrride) BLOBs to verify problems.
It’s not overly simple, but with the help of friends in high places: www.dba-oracle.com/t_convert_blob_varchar_datatype.htm
So, if you create the function then you can use it some SQL – first create the function F
create or replace function F(B BLOB)
return clob is
c clob;
n number;
begin
if (b is null) then
return null;
end if;
if (length(b)=0) then
return empty_clob();
end if;
dbms_lob.createtemporary(c,true);
n:=1;
while (n+32767<=length(b)) loop
dbms_lob.writeappend(c,32767,utl_raw.cast_to_varchar2(dbms_lob.substr(b,32767,n)));
n:=n+32767;
end loop;
dbms_lob.writeappend(c,length(b)-n+1,utl_raw.cast_to_varchar2(dbms_lob.substr(b,length(b)-n+1,n)));
return c;
end;
Then use it in your SQL
select f(uobindta) from dv910.f98950
where UOOBNM = 'P5310' and uovers = 'CMS0002' and uouoty in ('GF','GD')
and uouser = '*PUBLIC';
You’ll get something like the following, note that this is a corruption: Not ending with the correct tag.
"(CLOB) <?xml version="1.0" encoding="UTF-8"?>
<GridOverride><formatType>0</formatType><seqNumber>1</seqNumber><numCols>48</numCols><retCol>0</retCol><freezeCol>0</freezeCol><freezeRow>0</freezeRow><zoomFactor>100</zoomFactor><formatName>CMS</formatName><isPixel>1</isPixel><gridColumn><virtCol>1</virtCol><origCol>1</origCol><backColor>-1</backColor><foreColor>0</foreColor><idObject>22</idObject><colWidth>68</colWidth><colHidden>0</colHidden><fontHeader><height>-12</height><width>0</width><weight>700</weight><charset>1</charset><faceName>Arial</faceName><italic>0</italic><underline>0</underline><strikeout>0</strikeout></fontHeader><fontCol><height>-12</height><width>0</width><weight>400</weight><charset>1</charset><faceName>Arial</faceName><italic>0</italic><underline>0</underline><strikeout>0</strikeout></fontCol><alias>MCU</alias><sortSeq>1</sortSeq><sortOrder>A</sortOrder></gridColumn><gridColumn><virtCol>2</virtCol><origCol>2</origCol><backColor>-1</backColor><foreColor>0</foreColor><idObject>23</idObject><colWidth>62</colWidth><colHidden>0</colHidden><fontHeader><height>-12</height><width>0</width><weight>700</weight><charset>1</charset><faceName>Arial</faceName><italic>0</italic><underline>0</underline><strikeout>0</strikeout></fontHeader><fontCol><height>-12</height><width>0</width><weight>400</weight><charset>1</charset><faceName>Arial</faceName><italic>0</italic><underline>0</underline><strikeout>0</strikeout></fontCol><alias>CONO</alias><sortSeq>2</sortSeq><sortOrder>A</sortOrder></gridColumn><gridColumn><virtCol>3</virtCol><origCol>3</origCol><backColor>-1</backColor><foreColor>0</foreColor><idObject>24</idObject><colWidth>221</colWidth><colHidden>0</colHidden><fontHeader><height>-12</height><width>0</width><weight>700</weight><charset>1</charset><faceName>Arial</faceName><italic>0</italic><underline>0</underline><strikeout>0</strikeout></fontHeader><fontCol><height>-12</height><width>0</width><weight>400</weight><charset>1</charset><faceName>Arial</faceName><italic>0</italic><underline>0</underline><strikeout>0</strikeout></fontCol><alias>DL01</alias><sortSeq>0</sortSeq><sortOrder>A</sortOrder></gridColumn><gridColumn><virtCol>13</virtCol><origCol>4</origCol><backColor>-1</backColor><foreColor>0</foreColor><idObject>25</idObject><colWidth>60</colWidth><colHidden>0</colHidden><fontHeader><height>-12</height><width>0</width><weight>700</weight><charset>1</charset><faceName>Arial</faceName><italic>0</italic><underline>0</underline><strikeout>0</strikeout></fontHeader><fontCol><height>-12</height><width>0</width><weight>400</weight><charset>1</charset><faceName>Arial</faceName><italic>0</italic><underline>0</underline><strikeout>0</strikeout></fontCol><alias>REQB</alias><sortSeq>0</sortSeq><sortOrder>A</sortOrder></gridColumn><gridColumn><virtCol>4</virtCol><origCol>5</origCol><backColor>-1</backColor><foreColor>0</foreColor><idObject>26</idObject><colWidth>46</colWidth><colHidden>0</colHidden><fontHeader><height>-12</height><width>0</width><weight>700</weight><charset>1</charset><faceName>Arial</faceName><italic>0</italic><underline>0</underline><strikeout>0</strikeout></fontHeader><fontCol><height>-12</height><width>0</width><weight>400</weight><charset>1</charset><faceName>Arial</faceName><italic>0</italic><underline>0</underline><strikeout>0</strikeout></fontCol><alias>BSTC</alias><sortSeq>0</sortSeq><sortOrder>A</sortOrder></gridColumn><gridColumn><virtCol>8</virtCol><origCol>6</origCol><backColor>-1</backColor><foreColor>0</foreColor><idObject>27</idObject><colWidth>57</colWidth><colHidden>0</colHidden><fontHeader><height>-12</height><width>0</width><weight>700</weight><charset>1</charset><faceName>Arial</faceName><italic>0</italic><underline>0</underline><strikeout>0</strikeout></fontHeader><fontCol><height>-12</height><width>0</width><weight>400</weight><charset>1</charset><faceName>Arial</faceName><italic>0</italic><underline>0</underline><strikeout>0</strikeout></fontCol><alias>OREQ</alias><sortSeq>0</sortSeq><sortOr..."
See how this output is truncated in SQLDeveloper whether I run it as a script or just run it – a real pain.
I need to go “old school” on it to get all of the unadulterated data, I use a script like this is SQL plus:
spool F98950.txt
set linesize 30000
set long 80000
set pagesize 0
set wrap on
select f(uobindta) from dv910.f98950
where UOOBNM = 'P5310' and uovers = 'CMS0002' and uouoty in ('GF','GD')
and uouser = '*PUBLIC';
spool off
See below for a nicely truncated UO:
<?xml version="1.0" encoding="UTF-8"?>
<GridOverride><formatType>0</formatType><seqNumber>1</seqNumber><numCols>146</nu
mCols><retCol>19</retCol><freezeCol>4</freezeCol><freezeRow>-32767</freezeRow><z
oomFactor>100</zoomFactor><formatName>GW Format</formatName><isPixel>1</isPixel>
<gridColumn><virtCol>2</virtCol><origCol>1</origCol><backColor>-1</backColor><fo
reColor>0</foreColor><idObject>135</idObject><colWidth>235</colWidth><colHidden>
0</colHidden><fontHeader><height>-12</height><width>0</width><weight>700</weight
><charset>1</charset><faceName>Arial</faceName><italic>0</italic><underline>0</u
nderline><strikeout>0</strikeout></fontHeader><fontCol><height>-12</height><widt
h>0</width><weight>400</weight><charset>1</charset><faceName>Arial</faceName><it
alic>0</italic><underline>0</underline><strikeout>0</strikeout></fontCol><alias>
DL01</alias><sortSeq>0</sortSeq><sortOrder>A</sortOrder></gridColumn><gridColumn
><virtCol>3</virtCol><origCol>2</origCol><backColor>-1</backColor><foreColor>0</
foreColor><idObject>137</idObject><colWidth>55</colWidth><colHidden>0</colHidden
><fontHeader><height>-12</height><width>0</width><weight>700</weight><charset>1<
/charset><faceName>Arial</faceName><italic>0</italic><underline>0</underline><st
rikeout>0</strikeout></fontHeader><fontCol><height>-12</height><width>0</width><
weight>400</weight><charset>1</charset><faceName>Arial</faceName><italic>0</ital
ic><underline>0</underline><strikeout>0</strikeout></fontCol><alias>SCOS</alias>
<sortSeq>0</sortSeq><sortOrder>A</sortOrder></gridColumn><gridColumn><virtCol>5<
/virtCol><origCol>3</origCol><backColor>-1</backColor><foreColor>0</foreColor><i
dObject>167</idObject><colWidth>111</colWidth><colHidden>0</colHidden><fontHeade
r><height>-12</height><width>0</width><weight>700</weight><charset>1</charset><f
aceName>Arial</faceName><italic>0</italic><underline>0</underline><strikeout>0</
strikeout></fontHeader><fontCol><height>-12</height><width>0</width><weight>400<
/weight><charset>1</charset><faceName>Arial</faceName><italic>0</italic><underli
ne>0</underline><strikeout>0</strikeout></fontCol><alias>OQTC</alias><sortSeq>0<
/sortSeq><sortOrder>A</sortOrder></gridColumn><gridColumn><virtCol>6</virtCol><o
rigCol>4</origCol><backColor>-1</backColor><foreColor>0</foreColor><idObject>390
</idObject><colWidth>0</colWidth><colHidden>1</colHidden><fontHeader><height>-12
</height><width>0</width><weight>700</weight><charset>1</charset><faceName>Arial
</faceName><italic>0</italic><underline>0</underline><strikeout>0</strikeout></f
ontHeader><fontCol><height>-12</height><width>0</width><weight>400</weight><char
set>1</charset><faceName>Arial</faceName><italic>0</italic><underline>0</underli
ne><strikeout>0</strikeout></fontCol><alias>CO</alias><sortSeq>0</sortSeq><sortO
rder>A</sortOrder></gridColumn><gridColumn><virtCol>7</virtCol><origCol>5</origC
ol><backColor>-1</backColor><foreColor>0</foreColor><idObject>163</idObject><col
Width>126</colWidth><colHidden>0</colHidden><fontHeader><height>-12</height><wid
th>0</width><weight>700</weight><charset>1</charset><faceName>Arial</faceName><i
talic>0</italic><underline>0</underline><strikeout>0</strikeout></fontHeader><fo
ntCol><height>-12</height><width>0</width><weight>400</weight><charset>1</charse
t><faceName>Arial</faceName><italic>0</italic><underline>0</underline><strikeout
>0</strikeout></fontCol><alias>CURO</alias><sortSeq>0</sortSeq><sortOrder>A</sor
tOrder></gridColumn><gridColumn><virtCol>8</virtCol><origCol>6</origCol><backCol
or>-1</backColor><foreColor>0</foreColor><idObject>138</idObject><colWidth>111</
colWidth><colHidden>0</colHidden><fontHeader><height>-12</height><width>0</width
><weight>700</weight><charset>1</charset><faceName>Arial</faceName><italic>0</it
alic><underline>0</underline><strikeout>0</strikeout></fontHeader><fontCol><heig
ht>-12</height><width>0</width><weight>400</weight><charset>1</charset><faceName
>Arial</faceName><italic>0</italic><underline>0</underline><strikeout>0</strikeo
ut></fontCol><alias>COAM</alias><sortSeq>0</sortSeq><sortOrder>A</sortOrder></gr
idColumn><gridColumn><virtCol>13</virtCol><origCol>7</origCol><backColor>-1</bac
kColor><foreColor>0</foreColor><idObject>180</idObject><colWidth>112</colWidth><
colHidden>0</colHidden><fontHeader><height>-12</height><width>0</width><weight>7
00</weight><charset>1</charset><faceName>Arial</faceName><italic>0</italic><unde
rline>0</underline><strikeout>0</strikeout></fontHeader><fontCol><height>-12</he
ight><width>0</width><weight>400</weight><charset>1</charset><faceName>Arial</fa
ceName><italic>0</italic><underline>0</underline><strikeout>0</strikeout></fontC
ol><alias>ROAM</alias><sortSeq>0</sortSeq><sortOrder>A</sortOrder></gridColumn><
gridColumn><virtCol>14</virtCol><origCol>8</origCol><backColor>-1</backColor><fo
reColor>0</foreColor><idObject>148</idObject><colWidth>0</colWidth><colHidden>1<
/colHidden><fontHeader><height>-12</height><width>0</width><weight>700</weight><
charset>1</charset><faceName>Arial</faceName><italic>0</italic><underline>0</und
erline><strikeout>0</strikeout></fontHeader><fontCol><height>-12</height><width>
0</width><weight>400</weight><charset>1</charset><faceName>Arial</faceName><ital
ic>0</italic><underline>0</underline><strikeout>0</strikeout></fontCol><alias>MC
U</alias><sortSeq>0</sortSeq><sortOrder>A</sortOrder></gridColumn><gridColumn><v
irtCol>15</virtCol><origCol>9</origCol><backColor>-1</backColor><foreColor>0</fo
reColor><idObject>149</idObject><colWidth>0</colWidth><colHidden>1</colHidden><f
ontHeader><height>-12</height><width>0</width><weight>700</weight><charset>1</ch
arset><faceName>Arial</faceName><italic>0</italic><underline>0</underline><strik
eout>0</strikeout></fontHeader><fontCol><height>-12</height><width>0</width><wei
ght>400</weight><charset>1</charset><faceName>Arial</faceName><italic>0</italic>
<underline>0</underline><strikeout>0</strikeout></fontCol><alias>CONO</alias><so
rtSeq>0</sortSeq><sortOrder>A</sortOrder></gridColumn><gridColumn><virtCol>16</v
irtCol><origCol>10</origCol><backColor>-1</backColor><foreColor>0</foreColor><id
Object>150</idObject><colWidth>0</colWidth><colHidden>1</colHidden><fontHeader><
height>-12</height><width>0</width><weight>700</weight><charset>1</charset><face
Name>Arial</faceName><italic>0</italic><underline>0</underline><strikeout>0</str
ikeout></fontHeader><fontCol><height>-12</height><width>0</width><weight>400</we
ight><charset>1</charset><faceName>Arial</faceName><italic>0</italic><underline>
0</underline><strikeout>0</strikeout></fontCol><alias>REQB</alias><sortSeq>0</so
rtSeq><sortOrder>A</sortOrder></gridColumn><gridColumn><virtCol>17</virtCol><ori
gCol>11</origCol><backColor>-1</backColor><foreColor>0</foreColor><idObject>253<
/idObject><colWidth>0</colWidth><colHidden>1</colHidden><fontHeader><height>-12<
/height><width>0</width><weight>700</weight><charset>1</charset><faceName>Arial<
/faceName><italic>0</italic><underline>0</underline><strikeout>0</strikeout></fo
ntHeader><fontCol><height>-12</height><width>0</width><weight>400</weight><chars
et>1</charset><faceName>Arial</faceName><italic>0</italic><underline>0</underlin
e><strikeout>0</strikeout></fontCol><alias>RQBD</alias><sortSeq>0</sortSeq><sort
Order>A</sortOrder></gridColumn><gridColumn><virtCol>4</virtCol><origCol>12</ori
gCol><backColor>-1</backColor><foreColor>0</foreColor><idObject>134</idObject><c
olWidth>72</colWidth><colHidden>0</colHidden><fontHeader><height>-12</height><wi
dth>0</width><weight>700</weight><charset>1</charset><faceName>Arial</faceName><
italic>0</italic><underline>0</underline><strikeout>0</strikeout></fontHeader><f
ontCol><height>-12</height><width>0</width><weight>400</weight><charset>1</chars
et><faceName>Arial</faceName><italic>0</italic><underline>0</underline><strikeou
t>0</strikeout></fontCol><alias>LNID</alias><sortSeq>1</sortSeq><sortOrder>A</so
rtOrder></gridColumn><gridColumn><virtCol>18</virtCol><origCol>13</origCol><back
Color>-1</backColor><foreColor>0</foreColor><idObject>254</idObject><colWidth>0<
/colWidth><colHidden>1</colHidden><fontH
When a GD and GF is saved properly, the output is more like the below (note that this is 50Kb for a single UO!!!!
<foreColor>0</foreColor>
<idObject>121</idObject>
<colWidth>65</colWidth>
<colHidden>0</colHidden>
<colFrzHidden>0</colFrzHidden>
<colFrozen>0</colFrozen>
<fontHeader>
<height>-12</height>
<width>0</width>
<weight>700</weight>
<charset>1</charset>
<faceName>Arial</faceName>
<italic>0</italic>
<underline>0</underline>
<strikeout>0</strikeout>
</fontHeader>
<fontCol>
<height>-12</height>
<width>0</width>
<weight>400</weight>
<charset>1</charset>
<faceName>Arial</faceName>
<italic>0</italic>
<underline>0</underline>
<strikeout>0</strikeout>
</fontCol>
<alias>USER</alias>
<sortSeq>0</sortSeq>
<sortOrder>A</sortOrder>
</gridColumn>
</GridOverride>
No comments:
Post a Comment