Lucene › Solr › Solr - User

Indexing CLOB Column in Oracle

Classic

List

Threaded

6 messages Options

DarthMaul

Indexing CLOB Column in Oracle

Since my original thread was straying to a new topic, I thought it made sense to create a new thread of discussion.

I am using the DataImportHandler to index 3 fields in a table: an id, a date, and the text of a document. This is an Oracle database, and the document is an XML document stored as Oracle's xmltype data type, which is an instance of oracle.sql.OPAQUE. Still, it is nothing more than a fancy clob.

So in my db-data-config, I have the following:

<document>
<entity name="doc" query="SELECT d.EFFECTIVE_DT, d.ARCHIVE_ID FROM DOC d">
<field column="EFFECTIVE_DT" name="effectiveDate" />
<field column="ARCHIVE_ID" name="id" />
<entity name="text" query="SELECT d.XML FROM DOC d WHERE d.ARCHIVE_ID = '${doc.ARCHIVE_ID}'" transformer="ClobTransformer">
<field column="XML" name="text" clob="true" sourceColName="XML" />
</entity>
</entity>
</document>

Meanwhile, I have this in schema.xml:

<field name="text" type="text_ws" indexed="true" stored="true" multiValued="true" omitNorms="false" termVectors="true" />

However, when I take a look at my indexes with Luke, I find that the items labeled "text" simply say oracle.sql.OPAQUE and a bunch of numbers-in other words, the OPAQUE.toString().

Can you give me some insight into where I am going wrong?

Thanks.

Shawn Heisey-4

Re: Indexing CLOB Column in Oracle

Disclaimer: My Oracle experience is miniscule at best. I am also a
beginner at Solr, so grab yourself the proverbial grain of salt.

I googled a bit on CLOB. One page I found mentioned setting up a view
to return the data type you want. Can you use the functions described
on these pages in either the Solr query or a view?

http://www.oradev.com/dbms_lob.jsp
http://www.dba-oracle.com/t_dbms_lob.htm
http://www.praetoriate.com/dbms_packages/ddp_dbms_lob.htm

I also was trying to find a way to convert from xmltype directly to a
string in a query, but that quickly got way over my level of
understanding. I saw hints that it is possible, though.

Shawn

On 3/16/2010 4:59 PM, Neil Chaudhuri wrote:
> Since my original thread was straying to a new topic, I thought it made sense to create a new thread of discussion.
>
> I am using the DataImportHandler to index 3 fields in a table: an id, a date, and the text of a document. This is an Oracle database, and the document is an XML document stored as Oracle's xmltype data type, which is an instance of oracle.sql.OPAQUE. Still, it is nothing more than a fancy clob.
>

Craig Christman

RE: Indexing CLOB Column in Oracle

To convert an XMLTYPE to CLOB use the getClobVal() method like this:

SELECT d.XML.getClobVal() FROM DOC d WHERE d.ARCHIVE_ID = '${doc.ARCHIVE_ID}'

-----Original Message-----
From: Shawn Heisey [mailto:[hidden email]]
Sent: Tuesday, March 16, 2010 7:37 PM
To: [hidden email]
Subject: Re: Indexing CLOB Column in Oracle

Disclaimer: My Oracle experience is miniscule at best. I am also a
beginner at Solr, so grab yourself the proverbial grain of salt.

I googled a bit on CLOB. One page I found mentioned setting up a view
to return the data type you want. Can you use the functions described
on these pages in either the Solr query or a view?

http://www.oradev.com/dbms_lob.jsp
http://www.dba-oracle.com/t_dbms_lob.htm
http://www.praetoriate.com/dbms_packages/ddp_dbms_lob.htm

I also was trying to find a way to convert from xmltype directly to a
string in a query, but that quickly got way over my level of
understanding. I saw hints that it is possible, though.

Shawn

On 3/16/2010 4:59 PM, Neil Chaudhuri wrote:
> Since my original thread was straying to a new topic, I thought it made sense to create a new thread of discussion.
>
> I am using the DataImportHandler to index 3 fields in a table: an id, a date, and the text of a document. This is an Oracle database, and the document is an XML document stored as Oracle's xmltype data type, which is an instance of oracle.sql.OPAQUE. Still, it is nothing more than a fancy clob.
>

DarthMaul

RE: Indexing CLOB Column in Oracle

After trying that, the indexing process took a lot longer, which I found encouraging. However, when I inspected the indexes, the text property wasn't there. Very strange. I will keep digging.

Thanks for the tip though. I have used that function before, but I forgot to try it in this situation.

-----Original Message-----
From: Craig Christman [mailto:[hidden email]]
Sent: Wednesday, March 17, 2010 11:23 AM
To: [hidden email]
Subject: RE: Indexing CLOB Column in Oracle

To convert an XMLTYPE to CLOB use the getClobVal() method like this:

SELECT d.XML.getClobVal() FROM DOC d WHERE d.ARCHIVE_ID = '${doc.ARCHIVE_ID}'

-----Original Message-----
From: Shawn Heisey [mailto:[hidden email]]
Sent: Tuesday, March 16, 2010 7:37 PM
To: [hidden email]
Subject: Re: Indexing CLOB Column in Oracle

Disclaimer: My Oracle experience is miniscule at best. I am also a
beginner at Solr, so grab yourself the proverbial grain of salt.

I googled a bit on CLOB. One page I found mentioned setting up a view
to return the data type you want. Can you use the functions described
on these pages in either the Solr query or a view?

http://www.oradev.com/dbms_lob.jsp
http://www.dba-oracle.com/t_dbms_lob.htm
http://www.praetoriate.com/dbms_packages/ddp_dbms_lob.htm

I also was trying to find a way to convert from xmltype directly to a
string in a query, but that quickly got way over my level of
understanding. I saw hints that it is possible, though.

Shawn

On 3/16/2010 4:59 PM, Neil Chaudhuri wrote:
> Since my original thread was straying to a new topic, I thought it made sense to create a new thread of discussion.
>
> I am using the DataImportHandler to index 3 fields in a table: an id, a date, and the text of a document. This is an Oracle database, and the document is an XML document stored as Oracle's xmltype data type, which is an instance of oracle.sql.OPAQUE. Still, it is nothing more than a fancy clob.
>

DarthMaul

RE: Indexing CLOB Column in Oracle

In reply to this post by Craig Christman

For those who might encounter a similar issue, merging what I had into a single entity and using getClobVal() did the trick.

In other words:

<document>
<entity name="doc" query="SELECT d.EFFECTIVE_DT, d.ARCHIVE_ID, d.XML.getClobVal() AS TEXT FROM DOC d" transformer="ClobTransformer">
<field column="EFFECTIVE_DT" name="effectiveDate" />
<field column="ARCHIVE_ID" name="id" />
<field column="TEXT" name="text" clob="true">
</entity>
</document>

Thanks.

-----Original Message-----
From: Craig Christman [mailto:[hidden email]]
Sent: Wednesday, March 17, 2010 11:23 AM
To: [hidden email]
Subject: RE: Indexing CLOB Column in Oracle

To convert an XMLTYPE to CLOB use the getClobVal() method like this:

SELECT d.XML.getClobVal() FROM DOC d WHERE d.ARCHIVE_ID = '${doc.ARCHIVE_ID}'

-----Original Message-----
From: Shawn Heisey [mailto:[hidden email]]
Sent: Tuesday, March 16, 2010 7:37 PM
To: [hidden email]
Subject: Re: Indexing CLOB Column in Oracle

Disclaimer: My Oracle experience is miniscule at best. I am also a
beginner at Solr, so grab yourself the proverbial grain of salt.

I googled a bit on CLOB. One page I found mentioned setting up a view
to return the data type you want. Can you use the functions described
on these pages in either the Solr query or a view?

http://www.oradev.com/dbms_lob.jsp
http://www.dba-oracle.com/t_dbms_lob.htm
http://www.praetoriate.com/dbms_packages/ddp_dbms_lob.htm

I also was trying to find a way to convert from xmltype directly to a
string in a query, but that quickly got way over my level of
understanding. I saw hints that it is possible, though.

Shawn

On 3/16/2010 4:59 PM, Neil Chaudhuri wrote:
> Since my original thread was straying to a new topic, I thought it made sense to create a new thread of discussion.
>
> I am using the DataImportHandler to index 3 fields in a table: an id, a date, and the text of a document. This is an Oracle database, and the document is an XML document stored as Oracle's xmltype data type, which is an instance of oracle.sql.OPAQUE. Still, it is nothing more than a fancy clob.
>

Lance Norskog-2

Re: Indexing CLOB Column in Oracle

This could be the problem: the "text" field in the example schema is
indexed, but not stored. If you query the index with "text:monkeys" it
will find records with "monkeys", but the text field will not appear
in the returned XML because it was not stored.

On Wed, Mar 17, 2010 at 11:17 AM, Neil Chaudhuri
<[hidden email]> wrote:

> For those who might encounter a similar issue, merging what I had into a single entity and using getClobVal() did the trick.
>
> In other words:
>
> <document>
> <entity name="doc" query="SELECT d.EFFECTIVE_DT, d.ARCHIVE_ID, d.XML.getClobVal() AS TEXT FROM DOC d" transformer="ClobTransformer">
> <field column="EFFECTIVE_DT" name="effectiveDate" />
> <field column="ARCHIVE_ID" name="id" />
> <field column="TEXT" name="text" clob="true">
> </entity>
> </document>
>
> Thanks.
>
>
>
> -----Original Message-----
> From: Craig Christman [mailto:[hidden email]]
> Sent: Wednesday, March 17, 2010 11:23 AM
> To: [hidden email]
> Subject: RE: Indexing CLOB Column in Oracle
>
> To convert an XMLTYPE to CLOB use the getClobVal() method like this:
>
> SELECT d.XML.getClobVal() FROM DOC d WHERE d.ARCHIVE_ID = '${doc.ARCHIVE_ID}'
>
>
> -----Original Message-----
> From: Shawn Heisey [mailto:[hidden email]]
> Sent: Tuesday, March 16, 2010 7:37 PM
> To: [hidden email]
> Subject: Re: Indexing CLOB Column in Oracle
>
> Disclaimer: My Oracle experience is miniscule at best. I am also a
> beginner at Solr, so grab yourself the proverbial grain of salt.
>
> I googled a bit on CLOB. One page I found mentioned setting up a view
> to return the data type you want. Can you use the functions described
> on these pages in either the Solr query or a view?
>
> http://www.oradev.com/dbms_lob.jsp
> http://www.dba-oracle.com/t_dbms_lob.htm
> http://www.praetoriate.com/dbms_packages/ddp_dbms_lob.htm
>
> I also was trying to find a way to convert from xmltype directly to a
> string in a query, but that quickly got way over my level of
> understanding. I saw hints that it is possible, though.
>
> Shawn
>
> On 3/16/2010 4:59 PM, Neil Chaudhuri wrote:
>> Since my original thread was straying to a new topic, I thought it made sense to create a new thread of discussion.
>>
>> I am using the DataImportHandler to index 3 fields in a table: an id, a date, and the text of a document. This is an Oracle database, and the document is an XML document stored as Oracle's xmltype data type, which is an instance of oracle.sql.OPAQUE. Still, it is nothing more than a fancy clob.
>>
>
>

--
Lance Norskog
[hidden email]