Oninit
Talk to an expert →

Informix Error -611: Scroll cursor can't select TEXT or BYTE columns.

Cause and resolution

Scroll cursor can't select TEXT or BYTE columns.

The cursor that is named in this statement is associated with a SELECT statement that returns one or more TEXT or BYTE columns. Also, the cursor is declared with the SCROLL keyword. This action is not supported.Rows that are fetched through a scroll cursor are also stored in a temporary table. Because of the bulk of TEXT and BYTE values, this action would produce an unacceptable cost in time and disk space. Revise the declaration of the cursor to select the desired columns of other types and also the ROWID. After you fetch a row through the scrolling cursor, use a separate, nonscrolling cursor to fetch the BYTE or TEXT values, WHERE ROWID=host-var.

Oninit® Troubleshooting Guidance

Reasons / Common Causes

-611 fires when a SCROLL cursor's SELECT includes a TEXT/BYTE column — per the official guidance, rows fetched through a scroll cursor are stored in a temporary table, and the bulk of TEXT/BYTE values would make that unacceptably expensive in time and disk space.

  1. DECLARE ... SCROLL CURSOR with a SELECT list including a TEXT/BYTE column — the direct, only cause.
  2. A generic "select everything for browsing" cursor pattern applied to a table that includes a BLOB column, without excluding it from the scroll cursor's column list.
Solutions / Resolution
  1. Revise the cursor to select the other columns plus ROWID, per the official guidance, leaving the TEXT/BYTE column out of the scroll cursor's SELECT list entirely.
  2. Use a separate, non-scrolling cursor to fetch the BLOB value, per the official guidance, positioned via WHERE ROWID = host-var after the scroll cursor has fetched the row of interest.
Examples
The disallowed attempt
DECLARE doc_cursor SCROLL CURSOR FOR
  SELECT doc_id, title, content FROM documents;
-- -611: content is TEXT
Corrected — the two-cursor pattern
DECLARE doc_cursor SCROLL CURSOR FOR
  SELECT doc_id, title, ROWID rid FROM documents;
OPEN doc_cursor;
FETCH doc_cursor INTO :doc_id, :title, :rid;

DECLARE content_cursor CURSOR FOR
  SELECT content FROM documents WHERE ROWID = :rid;
OPEN content_cursor;
FETCH content_cursor INTO :content_locator;
Diagnostic Checks
  1. Scan SCROLL CURSOR declarations for a TEXT/BYTE column in the SELECT list, and split it out into a separate ROWID-based cursor.
Related Errors / Related Topics
  • -526 — "Updates are not allowed on a scroll cursor." Another scroll-cursor restriction, resolved by the same ROWID-based two-cursor pattern.
  • -612 — "TEXT and BYTE columns are not allowed in the group by clause." Same family of restrictions rooted in TEXT/BYTE having no defined lexical ordering or comparability.
  • -613 — "TEXT and BYTE columns are not allowed in the distinct clause." Same family.
  • -614 — "TEXT and BYTE columns are not allowed in the order by clause." Same family.

The same ROWID-based two-cursor pattern used for -526 also resolves this — keep the BLOB column out of the scroll cursor and fetch it separately.