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:s...@elyograg.org]
Sent: Tuesday, March 16, 2010 7:37 PM
To: solr-user@lucene.apache.org
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.
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
nchaudh...@potomacfusion.com 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:cchrist...@caci.com]
Sent: Wednesday, March 17, 2010 11:23 AM
To: solr-user@lucene.apache.org
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:s...@elyograg.org]
Sent: Tuesday, March 16, 2010 7:37 PM
To: solr-user@lucene.apache.org
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
goks...@gmail.com
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.
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.
