dbms_xmlgen.newContextFromHierarchy(queryString IN VARCHAR2)
RETURN ctxHandle;
set serveroutput on
DECLARE
qryctx dbmx_xmlgen.ctxhandle;
result XMLTYPE;
PROCEDURE lob_output (p_clob CLOB) IS l_clob CLOB;
l_clob_length NUMBER;
l_iterations NUMBER;
l_chunk VARCHAR2(32767);
l_chunk_length NUMBER := 32767;
BEGIN
l_clob := p_clob;
l_clob_length := dbms_lob.getlength(l_clob);
l_iterations := CEIL(l_clob_length / l_chunk_length);
FOR i IN 0 .. l_iterations - 1 LOOP
l_chunk := dbms_lob.substr(l_clob,l_chunk_length,i*l_chunk_length+1);
dbms_output.put_line(l_chunk);
END LOOP;
END;
BEGIN
qryctx := dbms_xmlgen.newcontextFromHierarchy('SELECT level,
XMLElement("Position", XMLElement("Name", ename), XMLElement("Title",
job)) FROM emp CONNECT BY PRIOR empno = mgr START WITH mgr is NULL');
result := dbms_xmlgen.getxmltype(qryctx);
dbms_xmlgen.closeContext(qryctx);
lob_output(RESULT.getClobVal());
END;
/
Halim is a Sr. Database Engineer/Data Architect (in Atlanta, USA) who is an Oracle certified (OCP) DBA, (OCP) Developer, Certified Cloud Architect Professional as well as OCI Autonomous DB specialist with extensive expertise in Database design, configuration, tuning, capacity planning, RAC, DG, Scripting, Python, PL/SQL etc. He achieved 16th position in worldwide first-ever PL/SQL Challenge cup playoff- http://plsql-challenge.blogspot.com/2010/07/winners-of-first-plsql-challenge.html
Subscribe to:
Post Comments (Atom)
My Blog List
-
-
Index Usage – 21 week ago
-
-
-
-
-
-
-
-
Oracle Cloud & Third party tools3 years ago
-
-
-
Moving Sideways7 years ago
-
Upcoming Events...10 years ago
-
No comments:
Post a Comment