DECLARE
l_clob CLOB;
ctx DBMS_XMLGEN.CTXHANDLE;
BEGIN
ctx := DBMS_XMLGEN.NEWCONTEXT (‘select 1 id,cursor(select 1 subid from dual) subid from dual’); –Assign the query as a new context
DBMS_XMLGEN.GETXML ( ctx, l_clob);
–do your processing on l_clob
DBMS_XMLGEN.CLOSECONTEXT (ctx); –close the context to release all open cursors or any other resources.
END;
Regards
Mohammad Talat
Salam
1. Add C:\WINDOWS\Fonts in REPORTS_PATH in Registry
2. Open this file C:\DSHome\tools\common\uifont.ali
3. Add this under [ Printer ]
Should look like this
[ Printer ]
Arial = "arial.ttf"
Arial..italic... = "ariali.ttf"
Arial...Bold.. = "arialbd.ttf"
Arial..italic.Bold.. = "arialbi.ttf"
"Andale Duospace WT J" = "Aduoj.ttf"
"Albany WT J"="AlbanWTJ.ttf"
"Arabic Transparent"="artro.ttf"
4. Add this under [ PDF:Subset ]
Should look like this
[ PDF:Subset ]
Arial = "arial.ttf"
Arial..italic... = "ariali.ttf"
Arial...Bold.. = "arialbd.ttf"
Arial..italic.Bold.. = "arialbi.ttf"
"Andale Duospace WT J" = "Aduoj.ttf"
"Albany WT J"="AlbanWTJ.ttf"
"Arabic Transparent"="artro.ttf"
regards
Mohmammad Talat
hello
$tkprof tracefile.trc youroutfilename explain=survey_dev_test/survey_dev_test sys=no
Regards
Mohammad Talat
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
SQL> ALTER USER username ACCOUNT UNLOCK;
Regards
select column_name
from user_tab_columns
where table_name=’Table_name’ and num_nulls=0;
Regards
Mohammad Talat
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
Don’t set your XML direct in select,you need to declare variable clob type.
DECLARE
DOC CLOB:='<Root id=”1″><data id=”1″ ………/></Root>’;
x number;
BEGIN
SELECT ID into x
FROM XMLTABLE (‘/Root/Data’
PASSING XMLTYPE(DOC)
COLUMNS
ID VARCHAR2(100) PATH ‘@ID’);
dbms_output.put_line(x);
END;
SELECT wm_concat(ename) FROM emp ;
—-Result—–
AHMAD,JAMES,Mark,Matt,john,shatha,smith
—-End Result—
Regards
Mohammad Talat
(: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
Mohammad Talat 