oracle long to number conversion

Question

I am trying to get the name of the current partition from all_tab_partitions table. When i try to compare high_value (long data Type) and number. It throws an error:

ORA-00997: illegal use of LONG datatype

SELECT PARTITION_NAME, HIGH_VALUE FROM ALL_TAB_PARTITIONS
WHERE TABLE_OWNER='SCHEMA_NAM'
AND TABLE_NAME='TABLE_NAME'
AND HIGH_VALUE>to_number(TO_CHAR(TRUNC(ADD_MONTHS(SYSDATE, 1), 'MONTH'), 'YYYYDDD'))
/

Answer 1

Long type stores large variable lenght character strings, it is NOT a numeric type. It has many limitations. Oracle documentation says:

The use of LONG values is subject to these restrictions:

• A table can contain only one LONG column.
• You cannot create an object type with a LONG attribute.
• LONG columns cannot appear in WHERE or in integrity constraints (except that they can appear in and NOT NULL constraints).
• LONG columns cannot be indexed.
• LONG cannot be specified in regular expressions.
• A stored function return a LONG value.
• You can declare a variable or argument of PL/SQL program unit using the LONG datatype. However, you cannot call the program unit from SQL.
• Within a single SQL statement, LONG columns, updated tables, and locked tables must be located the same database. LONG and LONG RAW columns cannot be used in distributed SQL statements and cannot be replicated.
• If a table has both LONG and LOB columns, then you cannot bind more than 4000 bytes of data to both the LONG and LOB columns in the same SQL statement. However, you can bind more than 4000 bytes of data to either the LONG or the LOB column.