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
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
cheers4beers
What application are you running the query from? Are you using an ODBC connection? Also, what version of Oracle?
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
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
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.
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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?
I am not allowed to create a table on the Oracle database server. Can I do this with a local Access database?
- 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: https://www.experts-exchange.com/questions/20263343/LONG-to-VARCHAR2-Function.html
- 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: https://www.experts-exchange.com/questions/20263343/LONG-to-VARCHAR2-Function.html
ASKER
Thanks
glad to help :)