0

I have a table which contains a field of type LONG. It can contain large blocks of text. I can extract data from this field as follows:

SELECT
    NOTES.NOTE_TEXT
FROM
    NOTES
WHERE
    NOTES.ID = 1

The problem, however, is that I am unable to search for text within the field. If I try the following:

SELECT
    NOTES.NOTE_TEXT
FROM
    NOTES
WHERE
    NOTES.NOTE_TEXT LIKE '%hello%'

... then I get an error: "Exception: DBD, ORA-00932: Inconsistent datatypes: expected NUMBER got LONG".

Of course, I Googled this problem to find many articles about how LONG is very limited, and that you could not search text within it. As to a solution, in several places it was suggested that the LONG could be converted to another data type. CAST AS CLOB was suggested.

I tried:

WHERE
    CAST(NOTES.NOTE_TEXT AS CLOB) LIKE '%hello%'

... however this also returned an error.

I also saw some other more complex solutions involving creating functions in PL/SQL. However I do not have access to this. I am using the "Business Objects" application, which is solely a reporting tool for a read-only database. I have no access to create tables or any other kind of write operation.

(Note: the above paragraph explains why the solutions elsewhere on SO do not answer my question, as they require write access to the database. I did not find any other similar question asking exactly what I need, which is to be able to do it in a read-only query.)

Does anyone know of a way in which I can convert LONG to something usable, within a read-only query in Oracle? Thanks.

Chris Melville
  • 1,476
  • 1
  • 14
  • 30
  • 2
    Sorry, I had an answer (and a comment on another answer) which I have now deleted. As others have stated, your problem is converting from LONG to CLOB, not what to do after you converted to CLOB. The suggestion has been made that you should get help from your DBA (or IT department) since you won't be able to do much without first physically storing your LONG values as CLOB. –  Jun 30 '16 at 15:57
  • Possible duplicate of [(Oracle/SQL) Merge all data types into a single column](http://stackoverflow.com/questions/38060163/oracle-sql-merge-all-data-types-into-a-single-column) – kevinskio Jun 30 '16 at 16:08
  • @mathguy, so just to confirm: without having access to the DB administration, it is definitely impossible - is that what you're saying? – Chris Melville Jun 30 '16 at 16:10
  • Possible duplicate of [ORA-00932: inconsistent datatypes: expected NUMBER got LONG](http://stackoverflow.com/questions/21158559/ora-00932-inconsistent-datatypes-expected-number-got-long) – topshot Jun 30 '16 at 17:03
  • Correct. It cannot be done with just read only commands. http://www.oracle-developer.net/display.php?id=430 had some decent explanation of options – topshot Jun 30 '16 at 17:03
  • Thanks anyway guys! Shame it can't be done, but at least it is now clear. – Chris Melville Jun 30 '16 at 17:07
  • FWIW, the Long type has been deprecated for a long time so you should look into upgrading your BO app. – topshot Jun 30 '16 at 17:20
  • @topshot, How does the BO app have any relevance to the data types stored in the database? BO is just an interface for extracting data. Unfortunately I have no influence in the design of the database itself: it's a huge corporate thing. – Chris Melville Jun 30 '16 at 20:08
  • I assumed it was a package of some kind - that the BO app came with scripts to create the needed db & schema and, thus, a newer version would have the conversion scripts as well. It likely was, but as you say, only the corporate folks would know that. – topshot Jun 30 '16 at 20:25

0 Answers0