XML to CLOB & UTF-8 Queries

All posts relating to Oracle PL/SQL development.

Moderator: Tim...

XML to CLOB & UTF-8 Queries

Postby Balaji Krishnan » Fri Jun 15, 2012 7:51 am

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.
Balaji Krishnan
Member
 
Posts: 20
Joined: Fri Mar 30, 2012 3:03 pm

Re: XML to CLOB & UTF-8 Queries

Postby Tim... » Fri Jun 15, 2012 9:53 am

Hi.

If you are just doing a query and spooling it using SQL*Plus, there is no need to do anything with temporary lobs.

You only need to worry about temporary lobs if you have created them in PL/SQL.

Cheers

Tim...
Tim...
Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: http://www.oracle-base.com
My blog: http://www.oracle-base.com/blog
Tim...
Site Admin
 
Posts: 17953
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK

Re: XML to CLOB & UTF-8 Queries

Postby Balaji Krishnan » Fri Jun 15, 2012 10:46 am

Thank you Tim. I have a CLOB data type variable in my PL/SQL code to store the xml. Do i need to clear this variable after spool the xml.
Balaji Krishnan
Member
 
Posts: 20
Joined: Fri Mar 30, 2012 3:03 pm

Re: XML to CLOB & UTF-8 Queries

Postby Tim... » Fri Jun 15, 2012 11:43 am

Hi.

If you did a manual DBMS_LOB.createtemporary, then it is best to release it with a DBMS_LOB.freetemporary, otherwise it will be fine.

Cheers

Tim...
Tim...
Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: http://www.oracle-base.com
My blog: http://www.oracle-base.com/blog
Tim...
Site Admin
 
Posts: 17953
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK

Re: XML to CLOB & UTF-8 Queries

Postby Balaji Krishnan » Fri Jun 15, 2012 12:05 pm

Thank you Tim. Is there a way i can convert characters in the DB tables to UTF-8 format before creating the xml CLOB object. I don't want to use UTL packages. Please suggest.

Also i executing stored procedures(create xml) through shell script, is there a way i can handle errors if PL/SQL block fails and recreate the XML using shell script?
Balaji Krishnan
Member
 
Posts: 20
Joined: Fri Mar 30, 2012 3:03 pm

Re: XML to CLOB & UTF-8 Queries

Postby Tim... » Fri Jun 15, 2012 1:17 pm

Hi.

You can convert charactersets using the CONVER function.

http://docs.oracle.com/cd/B28359_01/ser ... ons027.htm

As for the exception issue. Use an exception handler, like you would in any PL/SQL block.

Cheers

Tim...
Tim...
Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: http://www.oracle-base.com
My blog: http://www.oracle-base.com/blog
Tim...
Site Admin
 
Posts: 17953
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK

Re: XML to CLOB & UTF-8 Queries

Postby Balaji Krishnan » Fri Jun 15, 2012 2:26 pm

Tim,

I have used the below statement to convert the multi byte characters & special characters to UTF8 and got the o/p mentioned,

Code: Select all
SELECT CONVERT('test de con<trôle ', 'WE8MSWIN1252', 'UTF8')
FROM DUAL;

o/p:
-----
test de con<tr¿le

I couldn't able to find any sql function to perform url escape. Please help me here..
Desired output for the above string is

Code: Select all
test%20de%20con%3Ctr%F4le //url escaped string

I don't use utl packages.
Balaji Krishnan
Member
 
Posts: 20
Joined: Fri Mar 30, 2012 3:03 pm

Re: XML to CLOB & UTF-8 Queries

Postby Tim... » Fri Jun 15, 2012 5:04 pm

Hi.

Regarding your first issue, you obviously didn't read the manual entry. You have the parameters the wrong way round. It's destination, then source.

Code: Select all
SQL> SELECT CONVERT('test de con<trôle ', 'UTF8', 'WE8MSWIN1252')
  2  FROM DUAL;

CONVERT('TESTDECON<
-------------------
test de con<trôle

SQL>


The answer to your second issue is UTL_URL (ESCAPE and UNESCAPE):

Code: Select all
select utl_url.escape('http://www.oracle-base.com/forums?param1=Tim Hall') from dual;

UTL_URL.ESCAPE('HTTP://WWW.ORACLE-BASE.COM/FORUMS?PARAM1=TIMHALL')
---------------------------------------------------------------------

http://www.oracle-base.com/forums?param1=Tim%20Hall

SQL>


You say you don't use them, but I don;t see why not. They are there for a reason, so you should use them. If you don't have access to them, get your DBA to grant it.

Cheers

Tim...
Tim...
Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: http://www.oracle-base.com
My blog: http://www.oracle-base.com/blog
Tim...
Site Admin
 
Posts: 17953
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK

Re: XML to CLOB & UTF-8 Queries

Postby Balaji Krishnan » Mon Jun 18, 2012 7:23 am

Thank you Tim. I will request for the access and use it.
Balaji Krishnan
Member
 
Posts: 20
Joined: Fri Mar 30, 2012 3:03 pm

Re: XML to CLOB & UTF-8 Queries

Postby Tim... » Mon Jun 18, 2012 7:49 am

:)
Tim...
Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: http://www.oracle-base.com
My blog: http://www.oracle-base.com/blog
Tim...
Site Admin
 
Posts: 17953
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK

Re: XML to CLOB & UTF-8 Queries

Postby Balaji Krishnan » Mon Jun 18, 2012 11:48 am

Tim,

I have one more issue here, can you please help me out

Code: Select all
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;

ORA-06502: PL/SQL: numeric or value error
ORA-06512: at line 8
Balaji Krishnan
Member
 
Posts: 20
Joined: Fri Mar 30, 2012 3:03 pm

Re: XML to CLOB & UTF-8 Queries

Postby Tim... » Mon Jun 18, 2012 12:23 pm

Hi.

1) There is no package specification, just the body.
2) The output you show looks like something I would expect to see if I ran the code, yet you didn't post the call.

You should be sending full examples, not half the environment. :)

I assume the missing package spec is...

Code: Select all
create or replace
PACKAGE test_pack
IS
PROCEDURE test_proc (vxml OUT CLOB);
end;
/


When I call it, it works fine.

Code: Select all
set serveroutput on
DECLARE
  l_clob CLOB;
BEGIN
  test_pack.test_proc(l_clob);
  dbms_output.put_line(l_clob);
END;
/
<?xml version="1.0" encoding="UTF-8"?>
<root>
  <emp>
    <name>SCOTT</name>

<hiredate>2012-06-17</hiredate>
  </emp>
  <emp>
    <name>SAM</name>

<hiredate>2012-06-16</hiredate>
  </emp>
  <emp>
    <name>JIM</name>

<hiredate>2012-06-15</hiredate>
  </emp>
</root>


PL/SQL procedure successfully completed.

SQL>


Cheers

Tim...
Tim...
Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: http://www.oracle-base.com
My blog: http://www.oracle-base.com/blog
Tim...
Site Admin
 
Posts: 17953
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK

Re: XML to CLOB & UTF-8 Queries

Postby Balaji Krishnan » Mon Jun 18, 2012 12:28 pm

Tim,
Sorry for not posting the package specification. With this sample data, it is working fine. But i get the mentioned
exception when the data is huge.More 500 rows(for ex).
Balaji Krishnan
Member
 
Posts: 20
Joined: Fri Mar 30, 2012 3:03 pm

Re: XML to CLOB & UTF-8 Queries

Postby Tim... » Mon Jun 18, 2012 12:44 pm

LOL.

Seems a bit pointless posting the example code for me and not mentioning it works fine, but fails on large data sets. :)

The most likely cause of this you are talking the CLOB output and trying to put it into a variable that is too small, like a VARCHAR2.

Cheers

Tim...
Tim...
Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: http://www.oracle-base.com
My blog: http://www.oracle-base.com/blog
Tim...
Site Admin
 
Posts: 17953
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK

Re: XML to CLOB & UTF-8 Queries

Postby Balaji Krishnan » Mon Jun 18, 2012 1:09 pm

Apologies my mistakes Tim.

As per the example, i am putting the CLOB output into a CLOB variable only.
Balaji Krishnan
Member
 
Posts: 20
Joined: Fri Mar 30, 2012 3:03 pm

Next

Return to Oracle SQL and PL/SQL Development

Who is online

Users browsing this forum: No registered users and 2 guests