Re: Select content from a column of type 'long'

In article <375e3d65.0_at_news.touch.net>,   "bp" <bp_at_gsi-office.de#nospam> wrote:
> Hello,
>
> we have text stored in a long - column of an Oracle - Database and
want to
> retrieve some specific lines by issuing an select like
>
> Select * from outable WHERE long_column like '%abcdef%'. But this
brings up
> an error message like ORA-00932 : Datatype mismatch.
>
> How can I avoid this problem? The solution to change the column - type
isn't
> one, because the long - field is the one and only datatype for storing
big
> contents and being accessed via ODBC. (CLOB and such other datatypes
have
> problems with ODBC!)
>
> Bastien
>
>
Bastien,

from the manual :

Restrictions on LONG and LONG RAW Data

Although LONG (and LONG RAW; see below) columns have many uses, there are some restrictions on
their use:

     Only one LONG column is allowed per table.

     LONG columns cannot be indexed.

     LONG columns cannot appear in integrity constraints.

!!!!! LONG columns cannot be used in WHERE, GROUP BY, ORDER BY, CONNECT BY clauses, or with the DISTINCT operator in SELECT statements. !!!!

     LONG columns cannot be referenced by SQL functions (such as SUBSTR or INSTR).

     LONG columns cannot be used in the SELECT list of a subquery or queries combined by set

     operators (UNION, UNION ALL, INTERSECT, or MINUS).

     LONG columns cannot be used in expressions.

     LONG columns cannot be referenced when creating a table with a query (CREATE TABLE . . . AS

     SELECT . . .) or when inserting into a table (or view) with a query (INSERT INTO . . . SELECT . . .).      A variable or argument of a PL/SQL program unit cannot be declared using the LONG datatype.

     Variables in database triggers cannot be declared using the LONG or LONG RAW datatypes.

     References to :NEW and :OLD in database triggers cannot be used with LONG or LONG RAW

     columns.

--

It can't be done !

What you can do instead, is create a 2-column table where you would have
stored all the words/strings and the rowids of the records containing
your long column, then do a query on that table, which gives you back
the rowids of all the records containing your search string, then query
yout 'long' table passing the rowids as search criteria. It's much
easier than it sounds and very fast. You could also use the Context
option of Oracle of course.

HTH


Oliver Willandsen [Europäische Kommission - http://europa.eu.int]

Alle Kommentare spiegeln ausschliesslich meine eigene Meinung wieder und
können unter keinen
Umständen als eine offizielle Stellungnahme der Europäischen Kommission
aufgefasst werden


Sent via Deja.com http://www.deja.com/
Share what you know. Learn what you don't.