– ORA-06512: at “WMSYS.WM_CONCAT_IMPL”, line 30

you can find it below link….

https://community.oracle.com/thread/2200987?start=0&tstart=0

That’s because there is a limit on how big a varchar2 result can be (4000 bytes), and you are exceeding that.

What you need is to use a CLOB based version instead e.g.

create or replace
type clobagg_type as object(
text clob,
static function ODCIAggregateInitialize(
sctx in out clobagg_type
)
return number,
member function ODCIAggregateIterate(
self in out clobagg_type,
value in clob
)
return number,
member function ODCIAggregateTerminate(
self in clobagg_type,
returnvalue out clob,
flags in number
)
return number,
member function ODCIAggregateMerge(
self in out clobagg_type,
ctx2 in clobagg_type
)
return number
);
/
create or replace
type body clobagg_type
is
static function ODCIAggregateInitialize(
sctx in out clobagg_type
)
return number
is
begin
sctx := clobagg_type(null) ;
return ODCIConst.Success ;
end;
member function ODCIAggregateIterate(
self in out clobagg_type,
value in clob
)
return number
is
begin
self.text := self.text || value ;
return ODCIConst.Success;
end;
member function ODCIAggregateTerminate(
self in clobagg_type,
returnvalue out clob,
flags in number
)
return number
is
begin
returnValue := self.text;
return ODCIConst.Success;
end;
member function ODCIAggregateMerge(
self in out clobagg_type ,
ctx2 in clobagg_type
)
return number
is
begin
self.text := self.text || ctx2.text;
return ODCIConst.Success;
end;
end;
/
create or replace
function clobagg(
input clob
)
return clob
deterministic
parallel_enable
aggregate using clobagg_type;
/

Regards

—ORA-19025: EXTRACTVALUE returns value of only one node

with t as
(
select XMLTYPE(‘<Root> <DataEmp>
<Status>1</Status>
<UserGroup>2</UserGroup>
<ShiftType>1</ShiftType>
<SurveyType>1</SurveyType>
<SurveyData />
<Skills>
<Skill>
<SkillID>1</SkillID>
<Priority>1</Priority>
</Skill>
<Skill>
<SkillID>2</SkillID>
<Priority>2</Priority>
</Skill>
</Skills>
</DataEmp>
<DataEmp>
<Status>2</Status>
<UserGroup>2</UserGroup>
<ShiftType>1</ShiftType>
<SurveyType>2</SurveyType>
<SurveyData />
<Skills>
<Skill>
<SkillID>3</SkillID>
<Priority>1</Priority>
</Skill>
<Skill>
<SkillID>4</SkillID>
<Priority>2</Priority>
</Skill>
</Skills>
</DataEmp>
</Root>’) as OBJECT_VALUE from dual)
select q.Status
, s.SkillID

from t
left join xmltable(‘Root/DataEmp’
passing t.OBJECT_VALUE
columns Status varchar2(21) path ‘Status’,
empskill XmlType path ‘Skills/Skill/SkillID’

) q
on (1=1)
left join xmltable(‘/SkillID’
passing q.empskill
columns SkillID varchar2(21) path ‘.’) s

on (1=1)

——————-OUTPUT—————–

Status    SkillID 

   1           1
   1           2
   2           3
   2           4

 

regards

Mohammad Talat

–Write comments in XMLQuery

(:Your Comment HERE:)

Example:

SELECT XMLQuery(

‘for $i in ora:view(“SCOTT”, “DEPT”)/ROW
(:Dept comment:)
return <DEPT id=”{$i/DEPTNO}” DNAME =”{$i/DNAME}” >
{for $j in ora:view(“SCOTT”, “EMP”)/ROW
where $j/DEPTNO = $i/DEPTNO
return <EMP ID=”{$j/EMPNO}” Test=”{if ($param eq “EN”) then “True” else “False”}” ></EMP>
(: Employee Comment:)
}
</DEPT>’
passing cast(‘EN’ as varchar2(10)) as “param”

RETURNING CONTENT).getstringval() a FROM DUAL;

Regards

Mohammad Talat