Re: Indexing Oracle Long fields?

From: <iolo_at_my-dejanews.com>
Date: Tue, 14 Jul 1998 11:14:24 GMT
Message-ID: <6ofeig$q80$1@nnrp1.dejanews.com>

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 again Frank,

here's an extract from the OCI reference manual

oflng

Purpose

oflng() fetches a portion of a LONG or LONG RAW column.

Syntax

oflng(Cda_Def *cursor, sword pos,

      ub1 *buf, sb4 bufl, sword dtype,
      ub4 *retl, sb4 offset);

Comments

LONG and LONG RAW columns can hold up to 2 gigabytes of data. The oflng() function allows you to fetch up to 64K bytes, starting at any offset, from the LONG or LONG RAW column of the current row. There can be only one LONG or LONG RAW column in a table; however, a query that includes a join operation can include in its select list several LONG-type items. The pos parameter specifies the LONG-type column that the oflng() call uses.

Note: Although the datatype of bufl is sb4, oflng() can only retrieve up to 64K at a time. If an attempt is made to retrieve more than 64K, the returned data will not be complete. The use of sb4 in the interface is for future enhancements.

Before calling oflng() to retrieve the portion of the LONG-type column, you must do one or more fetches to position the cursor at the desired row.

oflng() is useful in cases where unstructured LONG or LONG RAW column data cannot be manipulated as a solid block.; for example, a voicemail application that uses sampled speech, stored as one byte per sample, at perhaps 10000 samples per second. If the voice message is to be played out using a buffered digital-to-analog converter, and the buffer takes 64 Kbyte samples at a time, you can use oflng() to extract the message in chunks of this size, sending them to the converter buffer. See the cdemo3.c program in Appendix A for an example that demonstrates this technique.

When calling oflng() to retrieve multiple segments from a LONG-type column, it is much more efficient to retrieve sequentially from low to high offsets, rather than from high to low, or randomly.

Note: With release 7.3, it may be possible to perform piecewise operations more efficiently using the new obindps(), odefinps(), ogetpi(), and osetpi() calls. See the section "Piecewise Insert, Update and Fetch" for more information.

The program fragment below shows how to retrieve 64 Kbytes, starting at offset 70000, from a LONG column. There are two columns in the table; the LONG data is in column two.

#define DB_SIZE 65536

#define FALSE 0

#define TRUE 1

ub1 *data_area;

sb4 offset;

sb2 da_indp, id_no;

ub4 ret_len;

Cda_Def cda;

...

data_area = (ub1 *) malloc(DB_SIZE);

...

oparse(&cda, "SELECT id_no, data FROM data_table

WHERE id_no = 100", -1, TRUE, 1);/* deferred parse */

/* define the first column - id_no, with no indicator parameter */

odefin(&cda, 1, &id_no, (int) sizeof (int), 3, -1, 0, 0, 0,

-1, 0, 0);

/* define the 2nd column - data, with indicator parameter */

odefin(&cda, 2, data_area, DB_SIZE, 1, -1, &da_indp,

0, 0, -1, 0, 0);

oexfet(&cda, 1, FALSE, FALSE); /* cursor is now at the row */

oflng(&cda, 2, data_area, DB_SIZE, 1, &ret_len, (sb4) 70000);

Parameters

Parameter Name

cursor A pointer to the CDA specified in the associated oparse() call.

pos The index position of the LONG-type column in the row. The first column is position one. If the column at the index position is not a LONG type, a "column does not have LONG datatype" error is returned. If you do not know the position, you can use odescr() to index through the select-list. When a LONG datatype code (8 or 24) is returned in the dtype parameter, the value of the loop index variable (that started at 1) is the position of the LONG-type column.

buf A pointer to the buffer that receives the portion of the LONG-type column data.

bufl The length of buf in bytes.

dtype The datatype code corresponding to the type of buf. See the "External Datatypes" section for a list of the datatype codes.

retl The number of bytes returned. If more than 65535 bytes were requested and returned, the maximum value returned in this parameter is still 65535.

offset The zero-based offset of the first byte in the LONG-type column to be fetched.