1 2 Previous Next 16 Replies Latest reply: Jun 27, 2012 5:42 AM by Oracle_Walker RSS

    XML to CLOB & UTF-8 Queries

    Oracle_Walker
      I need to create xml files from the available data in the DB tables. I am planning to use SQL/XML functions(xmlroot,xmlelement etc) to construct a well-formed
      xml and stored in CLOB data objects. I will use SPOOL command to get the clob (OUT parameter) in my client server (UNIX).

      Does any temporary CLOB objects will be created and not cleared by Oracle engine in the table space? If yes,how can i use dbms_lob.freetemporary(clobvar);
      to clear the CLOB object after the successful creation and spooling of the xml is done.

      I need to have utf-8 character set and achieve url escaped chracters as well, please let me know if these can be done without using UTL_HTTP or any other UTL packages.
        • 1. Re: XML to CLOB & UTF-8 Queries
          odie_63
          Looks like we've already been through this multiple times...

          {thread:id=2331664}

          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.
          • 2. Re: XML to CLOB & UTF-8 Queries
            Oracle_Walker
            Odie,

            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.
            • 3. Re: XML to CLOB & UTF-8 Queries
              Oracle_Walker
              Hi Odie,

              Please help how to have handle exception/error during the xml creation in PL/SQL block..
              • 4. Re: XML to CLOB & UTF-8 Queries
                odie_63
                Please suggest can i use dbms_lob.freetemporary(clobvar) against CLOB object i have created for storing xml.
                Show your code, please.
                • 5. Re: XML to CLOB & UTF-8 Queries
                  Oracle_Walker
                  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?
                  • 6. Re: XML to CLOB & UTF-8 Queries
                    Oracle_Walker
                    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
                    • 7. Re: XML to CLOB & UTF-8 Queries
                      odie_63
                      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.
                      • 8. Re: XML to CLOB & UTF-8 Queries
                        Oracle_Walker
                        Thank you for the clarifications Odie. I will let you know if i across any issue
                        • 9. Re: XML to CLOB & UTF-8 Queries
                          Oracle_Walker
                          Odie,

                          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?
                          • 10. Re: XML to CLOB & UTF-8 Queries
                            Oracle_Walker
                            Hi Odie,

                            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?
                            • 11. Re: XML to CLOB & UTF-8 Queries
                              Jason_(A_Non)
                              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.
                              • 12. Re: XML to CLOB & UTF-8 Queries
                                Oracle_Walker
                                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.
                                • 13. Re: XML to CLOB & UTF-8 Queries
                                  odie_63
                                  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?
                                  • 14. Re: XML to CLOB & UTF-8 Queries
                                    Oracle_Walker
                                    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.
                                    http://docs.oracle.com/cd/B28359_01/appdev.111/b28369/xdb13gen.htm#i1028612

                                    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.
                                    1 2 Previous Next