This content has been marked as final. Show 16 replies
Looks like we've already been through this multiple times...
Please explain again what exact problems you're encountering with the previous suggestions.
Personally, I never use SPOOL to write XML files. In my experience, there's always one file with a different line size or text node larger than usual that will just mess the whole thing up.
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.
Yes, your previous suggestions are working perfectly. I just wanted to know about temporary clob objects in the table space and clearing it after i got the xml. And you explained now that there won't be any free CLOB objects will be there in the table space unless we create one.
Please suggest can i use dbms_lob.freetemporary(clobvar) against CLOB object i have created for storing xml.
Please help how to have handle exception/error during the xml creation in PL/SQL block..
Please suggest can i use dbms_lob.freetemporary(clobvar) against CLOB object i have created for storing xml.Show your code, please.
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;
I have one more query related with SQL/XML functions, do these functions have any specific exceptions?
How can i handle them in my client side (or) server side and recreate the xml again after resolving the errors in the data?
Basically i don't have to create incomplete or invalid xmls..
Edited by: Balaji on Jun 15, 2012 8:25 PM
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.
SQL/XML functions are very simple to use. I guess most errors you could get are syntax errors, but they would be detected at compile time anyway.
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.
In other cases, just let the exception propagates up the call stack, to be displayed to the end user.
Thank you for the clarifications Odie. I will let you know if i across any issue
I am getting 'PL/SQL: numeric or value error' when trying to get the xml as Clob value. Debugging to find the root cause. Could this be a data issue?
I need a help from you regarding SQL/XML functions. Can we able to exclude an element if it has null values for a certain record. I tried using case statements but no luck :(
Can you help here?
What did your try look like?
Have you looked at XMLForest (pick your documentation version from under the Database section on the [url http://www.oracle.com/technetwork/indexes/documentation/index.html]Oracle Documentation page).
What about a WHERE clause to exclude those from the SELECT statement. This is probably not right but is an option given your lack of details.
XMLForest works well for this. However i have major issue to not to use these functions to produce XML files as they were deprecated from standard list of SQL/XML functions.
However i have major issue to not to use these functions to produce XML files as they were deprecated from standard list of SQL/XML functions.They're not deprecated.
Where did you see that?
XMLROOT is deprecated as per the documentation.
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.
Rest of the other functions are not deprecated. But if use remove XMLROOT from my select query i get the xml as single line, not in the structured xml format.