How do I fix ORA-00997: illegal use of LONG datatype error in Oracle results?

I am trying to get a DISTINCT value for TSW_CODES_6.USER_CODE in my Oracle query (see attached).

However, when I add DISTINCT to the query I get this error:

Microsoft OLE DB Provider for Oracle (0x80040E14)
ORA-00997: illegal use of LONG datatype

What am I doing wrong? query.txt
Donnie WalkerAsked:
Who is Participating?
 
OP_ZaharinConnect With a Mentor Commented:
hi rentawebguy,
- the LONG datatypes have been deprecated. they are still exist in the data dictionary in Oracle mainly because some of the legacy systems is using it. Oracle recommends that we convert existing LONG columns to LOB columns. LOB columns are subject to far fewer restrictions than LONG columns.

- one of the workaround that I would use to deal with LONG datatype is to change the LONG datatype to CLOB using build-in function TO_LOB by creating a temporary table
- then run the distinct on the temp table by using DBMS_LOB. however please note that we can  comparing only the first 4000 character.

1- i have a table called TEST contain IDS and BACKGROUNDS
Name                                        Null?    Type
 ---------------------------------- -------- ---------------
 IDS                                           NOT NULL VARCHAR2(5)
 BACKGROUNDS                      NOT NULL LONG


2- create a temp table and use the built-in SQL function TO_LOB. this function designed to convert a LONG or LONG RAW column to a CLOB or BLOB:

CREATE TABLE test2
   AS
        SELECT a.ids, TO_LOB(a.backgrounds) AS backgrounds
  FROM test a


3- now I have copied the TEST resultset to a new table TEST2 and converted BACKGROUNDS to a CLOB. do a describe on TEST2 to confirmed the datatype changed:

SQL> DESC TEST2
Name                                        Null?    Type
 ---------------------------------- -------- ---------------
 IDS                                           NOT NULL VARCHAR2(5)
 BACKGROUNDS                      NOT NULL CLOB


4- run the distinct query on the TEST2 table:  

SELECT DISTINCT DBMS_LOB.SUBSTR(backgrounds,4000,1) FROM test2

0
 
cheers4beersCommented:
What application are you running the query from?  Are you using an ODBC connection?  Also, what version of Oracle?
0
 
subratabiswasCommented:
When you are adding DISTINCT, the query will try to find distinct combination not only on the column immediately following the DISTINCT clause, but on combination of all the columns in the select statement.

In your query, probably the intention is to get distinct USER_CODE and then corresponding attributes for those distinct USER_CODES appearing only once in the result set. But what will will happen is, the query will try to return one unique combination of all the columns listed in the query.

Most probably the failure is because of a LONG column in the list of selected columns. UNIQUE can not be applied on LONG columns. Try the following simple test:

CREATE TABLE ABCD (A long)
INSERT INTO ABCD VALUES ('asldkfldksdlhsdf')
INSERT INTO ABCD VALUES ('asldkfldksdlhsdf')
INSERT INTO ABCD VALUES ('asldkfldksdlhsdf')
INSERT INTO ABCD VALUES ('asldkfldksdlhsdf')
INSERT INTO ABCD VALUES ('asldkfldksdlhsdf')
INSERT INTO ABCD VALUES ('asldkfldksdlhsdf')
INSERT INTO ABCD VALUES ('asldkfldksdlhsdf')
COMMIT
select DISTINCT A FROM ABCD


You should receive the same error message
0
The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

 
cheers4beersCommented:
Yes, subratabiswas is right, check the following link:

http://ora-00997.ora-code.com/

ORA-00997:      illegal use of LONG datatype

Cause:      A value of datatype LONG was used in a function or in a DISTINCT, WHERE, CONNECT BY, GROUP BY, or ORDER BY clause. A LONG value can only be used in a SELECT clause.

Action:   Remove the LONG value from the function or clause.
0
 
Donnie WalkerAuthor Commented:
Can you take my existing query and show me ho this works?

I am not allowed to create a table on the Oracle database server. Can I do this with a local Access database?
0
 
OP_ZaharinCommented:
- if you do not have the rights to create a table on Oracle, then you would not be able to use the suggested approach.
- here is an accepted answer on distinct a LONG datatype. DrSQL suggesting to use Substring up to maximum size of your LONG column and then Distinct. it's much simpler and no table/column creation needed: http://www.experts-exchange.com/Databases/Oracle/Q_20263343.html

0
 
Donnie WalkerAuthor Commented:
Thanks
0
 
OP_ZaharinCommented:
glad to help :)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.