XML to CLOB & UTF-8 Queries

[Balaji Krishnan]

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.

[Tim...]

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...

[Balaji Krishnan]

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.

[Tim...]

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...

[Balaji Krishnan]

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?

[Tim...]

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...

[Balaji Krishnan]

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.

[Tim...]

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...

[Balaji Krishnan]

Thank you Tim. I will request for the access and use it.

[Tim...]

[Balaji Krishnan]

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


[Tim...]

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...

[Balaji Krishnan]

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).

[Tim...]

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...

[Balaji Krishnan]

Apologies my mistakes Tim.

As per the example, i am putting the CLOB output into a CLOB variable only.