DEV Community

umzzil nng
umzzil nng

Posted on • Originally published at oraerror.com

Oracle ORA-00997 Error: Causes and Solutions Complete Guide

#oracle #database #dba #sql

ORA-00997: Illegal Use of LONG Datatype — What You Need to Know

ORA-00997 is thrown by Oracle when you attempt to use a LONG or LONG RAW column in a context where Oracle does not support it, such as SQL functions, comparison operators, subqueries, or set operations. The LONG datatype is a legacy type capable of storing up to 2GB of character data, but it comes with severe restrictions compared to modern CLOB/BLOB types. This error is most commonly encountered during legacy system maintenance or data migration projects.

Top 3 Causes

1. Applying SQL Functions or Operators Directly to a LONG Column

Oracle does not allow standard SQL functions like SUBSTR, UPPER, LENGTH, or operators like LIKE, =, > to be applied directly to a LONG column.

-- This will raise ORA-00997
SELECT SUBSTR(long_column, 1, 100)
FROM legacy_table;

-- Fix: Convert to LOB first using TO_LOB()
SELECT SUBSTR(TO_LOB(long_column), 1, 100)
FROM legacy_table;
Enter fullscreen mode Exit fullscreen mode

2. Using LONG Columns in CTAS or INSERT ... SELECT

Creating a new table from a select (CTAS) or inserting into a table using SELECT with a LONG column can trigger ORA-00997, especially when mixed with functions on other columns.

-- This may raise ORA-00997
CREATE TABLE new_table AS
SELECT id, long_column
FROM legacy_table;

-- Fix: Wrap with TO_LOB()
CREATE TABLE new_table AS
SELECT id, TO_LOB(long_column) AS long_column
FROM legacy_table;

-- Same fix for INSERT ... SELECT
INSERT INTO new_table (id, clob_column)
SELECT id, TO_LOB(long_column)
FROM legacy_table;
COMMIT;
Enter fullscreen mode Exit fullscreen mode

3. Using LONG Columns in Views, Subqueries, GROUP BY, or ORDER BY

Oracle restricts the use of LONG columns inside inline views, subqueries, and clauses like GROUP BY, ORDER BY, and DISTINCT.

-- This will raise ORA-00997
SELECT *
FROM (
    SELECT id, long_column
    FROM legacy_table
)
WHERE long_column = 'some value';

-- Fix: Use a wrapper view with TO_LOB()
CREATE OR REPLACE VIEW legacy_table_v AS
SELECT id, TO_LOB(long_column) AS long_column
FROM legacy_table;

-- Now you can query safely
SELECT *
FROM legacy_table_v
WHERE long_column = 'some value';
Enter fullscreen mode Exit fullscreen mode

Quick Fix Solutions

Option 1 — Use TO_LOB() inline: Wrap any LONG column reference with TO_LOB() to convert it to a CLOB at query time.

Option 2 — Migrate LONG to CLOB permanently:

-- Step 1: Add a CLOB column
ALTER TABLE legacy_table ADD (long_column_clob CLOB);

-- Step 2: Copy data
UPDATE legacy_table
SET long_column_clob = TO_LOB(long_column);
COMMIT;

-- Step 3: Drop old LONG column
ALTER TABLE legacy_table DROP COLUMN long_column;

-- Step 4: Rename new CLOB column
ALTER TABLE legacy_table RENAME COLUMN long_column_clob TO long_column;
Enter fullscreen mode Exit fullscreen mode

Option 3 — Handle via PL/SQL: When SQL-level access is unavailable, read the LONG into a PL/SQL variable and process it there.

DECLARE
    v_data  LONG;
    v_clob  CLOB;
BEGIN
    SELECT long_column INTO v_data
    FROM legacy_table WHERE id = 1;

    v_clob := TO_CLOB(v_data);
    DBMS_OUTPUT.PUT_LINE(DBMS_LOB.SUBSTR(v_clob, 200, 1));
END;
/
Enter fullscreen mode Exit fullscreen mode

Prevention Tips

  • Ban LONG in new designs: Enforce a coding standard that prohibits LONG and LONG RAW in all new table definitions. Always use CLOB for large text and BLOB for binary data. Audit existing schemas regularly: 
SELECT owner, table_name, column_name, data_type
FROM dba_tab_columns
WHERE data_type IN ('LONG', 'LONG RAW')
ORDER BY owner, table_name;
Enter fullscreen mode Exit fullscreen mode
  • Create abstraction views over legacy LONG columns: If you cannot immediately migrate LONG columns, expose them as CLOB through a database view using TO_LOB(). This shields application code from the underlying type restrictions and makes future migration transparent.

Related Errors

  • ORA-00932 — Inconsistent datatypes, often seen alongside ORA-00997 when mixing LONG with other types.
  • ORA-01461 — Can bind a LONG value only for insert into a LONG column, commonly encountered during JDBC/OCI bind operations with LONG data.

📖 Want a more detailed guide?
Check out the full in-depth version (Korean) on oraerror.com — includes detailed analysis, additional SQL examples, and prevention tips.

Top comments (0)