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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
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
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

OP_ZaharinCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.