This discussion is archived
1 2 Previous Next 16 Replies Latest reply: Jun 27, 2012 3:42 AM by Oracle_Walker RSS

XML to CLOB & UTF-8 Queries

Oracle_Walker Newbie
Currently Being Moderated
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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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) Expert
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points