Re: Indexing Oracle Long fields?

In article <35AB08AB.2D0A5C54_at_inclusion.net>,   Frank Cohen <fcohen_at_inclusion.net> wrote:
> This is a multi-part message in MIME format.
> --------------AD79DC10205D012E89FEF2AC
> Content-Type: text/plain; charset=us-ascii
> Content-Transfer-Encoding: 7bit
>
> I have a couple of tables created that contain a Long field. I need to
> search for a simple text string in the Long fields. I bought Oracle
> Workgroup Server 7 last quarter. Any ideas where I should look or what
> the answer is?
>
> -Frank
>
> --
>

Hi Frank,

I think you're out of luck with regard to LONG columns

LONG Datatype

LONG columns store variable length character strings containing up to 2 gigabytes, or 231-1 bytes. LONG columns have many of the characteristics of VARCHAR2 columns. You can use LONG columns to store long text strings. Oracle7 uses LONG columns in the data dictionary to store the text of view definitions. The length of LONG values may also be limited by the memory available on your computer.

You can reference LONG columns in SQL statements in these places:

     SELECT lists

     SET clauses of UPDATE statements

     VALUES clauses of INSERT statements

The use of LONG values are subject to some restrictions:

     A table cannot contain more than one LONG column.

     LONG columns cannot appear in integrity constraints (except for NULL and NOT NULL constraints).

     LONG columns cannot be indexed.

     A stored function cannot return a LONG value.

  Within a single SQL statement, all LONG columns, updated tables, and locked tables must be located on the same database.

Also, LONG columns cannot appear in certain SQL statements:

     CREATE SNAPSHOT Also, LONG columns cannot appear in certain parts of SQL statements:

     WHERE, GROUP BY, ORDER BY, or CONNECT BY clauses or with the DISTINCT operator in SELECT

     statements

     UNIQUE clause of a SELECT statement

     the column datatype clause of a CREATE CLUSTER statement

     SQL functions (such as SUBSTR or INSTR) !!!!!!

     expressions or conditions !!!!!!!

     select lists of queries containing GROUP BY clauses

     select lists of subqueries or queries combined by set operators

     select lists of CREATE TABLE AS SELECT statements

     select lists in subqueries in INSERT statements

Triggers can use the LONG datatype in the following manner:

     A SQL statement within a trigger can insert data into a LONG column.

  If data from a LONG column can be converted to a constrained datatype (such as CHAR and VARCHAR2), a LONG column can be referenced in a SQL statement within a trigger. Note that the maximum length for these datatypes is 32 Kbytes.

     Variables in triggers cannot be declared using the LONG datatype.

     :NEW and :OLD cannot be used with LONG columns.

You can use the Oracle Call Interfaces to retrieve a portion of a LONG value from the database. See Programmer's Guide to the Oracle Call Interface.

If the size of your data does not exceed 32K you'd be better off using VARCHAR2 instead of LONG

--
Oliver Willandsen
European Commission
http://europa.eu.int

All remarks are my own and do not necessarily reflect official European Commission policy

-----== Posted via Deja News, The Leader in Internet Discussion ==----- http://www.dejanews.com/rg_mkgrp.xp Create Your Own Free Member Forum Received on Tue Jul 14 1998 - 06:08:03 CDT