Does any temporary CLOB objects will be created and not cleared by Oracle engine in the table space?If you don't create any temp CLOB, either through an explicit DBMS_LOB.createTemporary() call or function than returns one, then there's no temp CLOB to free.
Does this pl/sql code block create any temp CLOB's (or) do i need clear/free xml CLOB variable used here after completion or in case of exception?
DDL & DML CREATE TABLE EMP_DETAILS (ENAME VARCHAR2(50),ID NUMBER(20),HIREDATE DATE); INSERT INTO EMP_DETAILS (ENAME,ID,HIREDATE) VALUES ('SCOTT',1,SYSDATE-1); INSERT INTO EMP_DETAILS (ENAME,ID,HIREDATE) VALUES ('SAM',2,SYSDATE-2); INSERT INTO EMP_DETAILS (ENAME,ID,HIREDATE) VALUES ('JIM',3,SYSDATE-3); PL/SQL create or replace PACKAGE body test_pack IS PROCEDURE test_proc (vxml OUT CLOB) IS xml CLOB; vQuery VARCHAR2(1000); BEGIN select XMLROOT( xmlelement("root", xmlagg( xmlelement("emp", xmlelement("name", ename) , xmlelement("hiredate", hiredate) ) ) ),version '1.0" encoding="UTF-8').getClobVal() into xml from EMP_DETAILS; vxml := xml; END test_proc; END;
Does this pl/sql code block create any temp CLOB's (or) do i need clear/free xml CLOB variable used here after completion or in case of exception?No.
I have one more query related with SQL/XML functions, do these functions have any specific exceptions?I don't know, maybe.
How can i handle them in my client side (or) server side and recreate the xml again after resolving the errors in the data?Handling an exception is useful when you know the origin of the problem (i.e. when you actually expects the exception), and when you know how to resolve that problem.
SQL function XMLRoot was at one time part of the SQL/XML standard, but it is deprecated as a standard function as of SQL/XML 2005. It remains available in Oracle XML DB, as an Oracle function.