Home » SQL & PL/SQL » SQL & PL/SQL » Use CLOB XML coming from Oracle
Use CLOB XML coming from Oracle [message #430546] Tue, 10 November 2009 23:56 Go to next message
iamkrazee
Messages: 12
Registered: November 2009
Junior Member
hi,
I have a table in oracle which has a column of type Clob. There is some data stored in

the clolumn which is in XML format. I need to use this XML for comparing purpose. The

format of that XML is as follows:
(Clob) <?xml version="1.0" encoding="UTF-8"?>
<query itemType="news" version="1.0">
<where>
<gt>
<term type="field">CTD-NEWS-DISPLAY-DATE</term>
<term type="constant">1251975600000</term>
</gt>
</where>
<order-by propName="CTD-NEWS-DISPLAY-DATE"/>
</query>

How can i store this XML in a String Buffer or something?? Please help me.
Thanks
Re: Use CLOB XML coming from Oracle [message #430569 is a reply to message #430546] Wed, 11 November 2009 01:23 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
How can i store this XML in a String Buffer or something??

Just read it. What is EXACTLY your problem.
Use SQL*Plus and copy and paste what you tried.
Explain in DETAILS what you're trying to achieve.

Regards
Michel
Re: Use CLOB XML coming from Oracle [message #430576 is a reply to message #430569] Wed, 11 November 2009 01:33 Go to previous messageGo to next message
iamkrazee
Messages: 12
Registered: November 2009
Junior Member
Hi,
i tried retrieving value from the database. Suppose my column value is <Query>abc</Query>
and i try to retrieve the the same by firing the query from java,
then what i get is only 'abc'. where as i want to retrive the whole column i.e. <Query>abc</Query>
Is it possible to do it??
Re: Use CLOB XML coming from Oracle [message #430578 is a reply to message #430576] Wed, 11 November 2009 01:41 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
If you are able to extract "abc" then you know it comes from <query> tags, so explicitly concat this tag (just an example of how you can modify your code as you don't show us what you do).

Regards
Michel
Re: Use CLOB XML coming from Oracle [message #430581 is a reply to message #430578] Wed, 11 November 2009 01:49 Go to previous messageGo to next message
iamkrazee
Messages: 12
Registered: November 2009
Junior Member
oh no.. my basic problem is very different. Let me try to elaborate it.
1. I am using Oracle as database and Vignette as CMS.
2. Whenever i want to display certain values on our portal, i just chose them from the options given on front end like drop drown buttons etc. and the query gets built. and gets stored in a table. Now this query that gets stored in the database, has type CLOB. and it is in the following format.
<?xml version="1.0" encoding="UTF-8"?>
<query itemType="news" version="1.0">
<where>
<gt>
<term type="field">CTD-NEWS-DISPLAY-DATE</term>
<term type="constant">1251975600000</term>
</gt>
</where>
<order-by propName="CTD-NEWS-DISPLAY-DATE"/>
</query>
3. The values in this query may differ depending upon the query chosen by end user. so i cannot explicitely edit the code like you told me in the last reply.
4. <term type="constant">1251975600000</term> this value from the above query is a DATE which automatically gets converted in to LONG data type.
5. And when the above query gets fired using VIGNETTE API's i get NULL POINTER EXCEPTION because it fails retriving the values.
6. Thus, i thought of retriving the WHOLE query and then i can use XML parser and write my own API to retrieve the corresponding values.

So, i want the WHOLE column value including the XML TAGS.

I hope i could explain u what my problem is..


Re: Use CLOB XML coming from Oracle [message #430584 is a reply to message #430581] Wed, 11 November 2009 02:09 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Sorry, I still don't understand where is the problem.
I don't underrstand what is in the database and what is generated in the application, I don't understand when you access the database and what you retrieve (or modify or insert or what else) from it.
And so on....
Maybe someone else will.

Regards
Michel
Re: Use CLOB XML coming from Oracle [message #430586 is a reply to message #430584] Wed, 11 November 2009 02:18 Go to previous messageGo to next message
iamkrazee
Messages: 12
Registered: November 2009
Junior Member
Is there any way for me to contact you?
Re: Use CLOB XML coming from Oracle [message #430588 is a reply to message #430584] Wed, 11 November 2009 02:24 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
I don't understand either. When you select from a CLOB column, you get the value of the CLOB, including everything that is in it, whether it's XML, RTF, HTML, whatever.

Maybe it's a "VIGNETTE API" Problem?
Re: Use CLOB XML coming from Oracle [message #430589 is a reply to message #430588] Wed, 11 November 2009 02:31 Go to previous messageGo to next message
iamkrazee
Messages: 12
Registered: November 2009
Junior Member
No its not a vignette problem.
You can try this out.
Create a table e.g. create table t(id number, query clob);

insert into t values (1, '<query>ABCD</query>');

now, from java class, connect to database and write select query

preparedStatement = connection.prepareStatement("SELECT " +
"query " +
"FROM t WHERE id = 1 ");

resultSet = preparedStatement.executeQuery();

and when you print the result, you WONT <query>ABCD</query>
INSTEAD YOU'LL GET ABCD as an output.

and i want <query>ABCD</query>.. the complete column value. Please help me Sad

Thanks
Prachee
Re: Use CLOB XML coming from Oracle [message #430590 is a reply to message #430589] Wed, 11 November 2009 02:38 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
SQL> create table t(id number, query clob);

Table created.

SQL> insert into t values (1, '<query>ABCD</query>');

1 row created.

SQL> select query from t where id=1;
QUERY
---------------------------------------------------------
<query>ABCD</query>

1 row selected.

Oracle returns the whole field, it does not filter, it does not know that the CLOB contains XML.
So it seems it is not an Oracle problem.

Regards
Michel

[Updated on: Wed, 11 November 2009 02:39]

Report message to a moderator

Re: Use CLOB XML coming from Oracle [message #430591 is a reply to message #430589] Wed, 11 November 2009 02:40 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
I do that almost every day, and I never experience what you are experiencing.

how do you "get" and "print" the results in Java?

Do you do it this way?
Re: Use CLOB XML coming from Oracle [message #430592 is a reply to message #430590] Wed, 11 November 2009 02:41 Go to previous messageGo to next message
iamkrazee
Messages: 12
Registered: November 2009
Junior Member
thats what the point is!!
in oracle, it displays the whole thing..
but from java code it displays only what is present in the XML tags.

btw, i am using resultset.getClob(1) function. can this create any prob??
Re: Use CLOB XML coming from Oracle [message #430594 is a reply to message #430592] Wed, 11 November 2009 02:45 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
Not really. I still think it's a problem in the "display" part.

Can you print the returned value to STDOUT directly after you fetched it?

Re: Use CLOB XML coming from Oracle [message #430595 is a reply to message #430594] Wed, 11 November 2009 02:48 Go to previous messageGo to next message
iamkrazee
Messages: 12
Registered: November 2009
Junior Member
i tried.. it gives the same thing Sad
Re: Use CLOB XML coming from Oracle [message #430597 is a reply to message #430595] Wed, 11 November 2009 02:49 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Post the whole code.

Regards
Michel
Re: Use CLOB XML coming from Oracle [message #430598 is a reply to message #430595] Wed, 11 November 2009 02:50 Go to previous messageGo to next message
iamkrazee
Messages: 12
Registered: November 2009
Junior Member
Btw Thomas, I checked the code in the link. I am doing exactly the same thing. but still getting what i told you..

Re: Use CLOB XML coming from Oracle [message #430599 is a reply to message #430546] Wed, 11 November 2009 02:55 Go to previous messageGo to next message
iamkrazee
Messages: 12
Registered: November 2009
Junior Member
Alright the whole code is as follws:
Connection con = null;
PreparedStatement pstmt = null;
ResultSet result = null;


try {

con = ConnectionProvider.getConnection();
/* SAVED_QUERY is column name.. and CTD_AZ_QUERY is table name */
pstmt = con.prepareStatement("SELECT " +
"SAVED_QUERY " +
"FROM CTD_AZ_QUERY WHERE id= ? ");

/* myEneteredQuery is the value is pass to ? */

pstmt.setString(1,myEneteredQuery);
result = pstmt.executeQuery();
while(result.next()){
/* here i have tried with getString(1) also */
Clob clb = result.getClob(1);

if(clb!=null){
/* this following code i got from net. tried only printing the clob directly */
StringBuffer str = new StringBuffer();
String strng;
BufferedReader bufferRead = new BufferedReader(clb.getCharacterStream());
while ((strng=bufferRead .readLine())!=null)
str.append(strng);



out.print(str.toString());
}else{
out.print("it is null");
}



}


}catch(Exception e) {
out.print("Error :"+ e);
} finally {
try {result.close();} catch(Exception e) {}
try {pstmt.close();} catch(Exception e) {}
ConnectionProvider.freeConnection(con);
}
Re: Use CLOB XML coming from Oracle [message #430609 is a reply to message #430599] Wed, 11 November 2009 03:24 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
That is not complete stand-alone code.

"out" maybe something special we don't know, the ConnectionProvider might be something special we don't know

Anyway, this adjusted code here still works for me:


package test;
import java.sql.*;
import java.io.*;
import oracle.jdbc.pool.*;
/**
 *
 * @author ThomasG
 */
public class Main {

    /**
     * @param args the command line arguments
     */
    public static void main(String[] args) {
        Connection con = null;
        PreparedStatement pstmt = null;
        ResultSet result = null;


            try {
                OracleDataSource ds = new oracle.jdbc.pool.OracleDataSource();

                ds.setURL("jdbc:oracle:thin:@127.0.0.1:" + "1521" + ":" + "SID");
                ds.setLoginTimeout(10);
                con = ds.getConnection("user","pass");

                /* SAVED_QUERY is column name.. and CTD_AZ_QUERY is table name */
                pstmt = con.prepareStatement("SELECT col from test_tab");

                /* myEneteredQuery is the value is pass to ? */

                result = pstmt.executeQuery();
                while(result.next()){
            /* here i have tried with getString(1) also */
                    Clob clb = result.getClob(1);

                    if(clb!=null){
                        /* this following code i got from net. tried only printing the clob directly */
                        StringBuffer str = new StringBuffer();
                        String strng;
                        BufferedReader bufferRead = new BufferedReader(clb.getCharacterStream());
                        while ((strng=bufferRead .readLine())!=null)
                        str.append(strng);
                        System.out.print(str.toString());
            }else{
                System.out.print("it is null");
            }

        }

    }catch(Exception e) {
        System.out.print("Error :"+ e);
    } finally {
        try {result.close();} catch(Exception e) {}
        try {pstmt.close();} catch(Exception e) {}
    }
}


Table setup:

SQL> CREATE TABLE test_tab (col CLOB);

Table created.

SQL>
SQL> INSERT INTO test_tab VALUES ('<test>bla</test>');

1 row created.

SQL>

Running it:

init:
deps-jar:
Compiling 1 source file to C:\test
compile:
run:
<test>bla</test>
BUILD SUCCESSFUL (total time: 3 seconds)


If you still get something else, post a COMPLETE and FORMATTED test case, like I did.
Re: Use CLOB XML coming from Oracle [message #430614 is a reply to message #430609] Wed, 11 November 2009 03:38 Go to previous messageGo to next message
iamkrazee
Messages: 12
Registered: November 2009
Junior Member
It worked for you?? Strange!!

1. ConnectionProvider class only makes connection to the database. I am sorry i cant post that code here as i am directly using the jar file. Let me assure that there is nothing in this class other than making and releasing JDBC connection.

2. I am using out.print because i have written the whole code in JSP.

3. Can you do me a favour? Can you just try and paste your java code in a JSP and check the output?
Now i think it could be related to jsp.

<%@ page contentType="text/html; charset=UTF-8" %>
is the content type written in my JSP. i have also tried with
<%@ page contentType="text/xml; charset=UTF-8" %>

But no luck!

Thanks a lot for your help!
Re: Use CLOB XML coming from Oracle [message #430615 is a reply to message #430614] Wed, 11 November 2009 03:42 Go to previous messageGo to next message
iamkrazee
Messages: 12
Registered: November 2009
Junior Member
Thomas! i tried that in java class! mine too is working..
Could it be related to JSP???
Re: Use CLOB XML coming from Oracle [message #430616 is a reply to message #430614] Wed, 11 November 2009 03:44 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
So it is a display problem all along.

JSP = HTML = "Tags" are not displayed in whatever frontend you display it.

Re: Use CLOB XML coming from Oracle [message #430619 is a reply to message #430616] Wed, 11 November 2009 03:52 Go to previous message
iamkrazee
Messages: 12
Registered: November 2009
Junior Member
Hi,

Ya..
I think...we have to replace < by &lt; and > by &gt; signs
Previous Topic: Col size
Next Topic: CLOB - Appending CLOB in for loop.
Goto Forum:
  


Current Time: Sun Dec 04 12:39:32 CST 2016

Total time taken to generate the page: 0.05483 seconds