v_ctx DBMS_XMLGen.ctxHandle;
v_xml CLOB;
v_start NUMBER := 1;
v_len NUMBER := 4000;
v_xline VARCHAR2(32767);
v_xout VARCHAR2(255);
BEGIN
v_ctx := DBMS_XMLGen.newContext('SELECT q_tab.v_val month_num,
DECODE(GREATEST(q_tab.v_val,4), q_tab.v_val,
DECODE(GREATEST(q_tab.v_val,7), q_tab.v_val,
DECODE(GREATEST(q_tab.v_val,10), q_tab.v_val,4,3),2),1) Quarter
FROM (SELECT LEVEL v_val FROM DUAL CONNECT BY LEVEL <=12) q_tab');
v_xml := DBMS_XMLGen.GetXML(v_ctx);
DBMS_XMLGen.setRowTag(v_ctx, '');
DBMS_XMLGen.closeContext(v_ctx);
DBMS_LOB.READ(v_xml,v_len,v_start,v_xline);
DBMS_OUTPUT.enable(1000000);
WHILE v_xline IS NOT NULL
LOOP
v_xout := substr(v_xline,1,instr(v_xline,CHR(10))-1);
DBMS_OUTPUT.put_line(' '||v_xout);
v_xline := substr(v_xline,instr(v_xline,CHR(10))+1);
END LOOP;
EXCEPTION
WHEN OTHERS THEN
RAISE;
END;
/
