1

I am trying to execute the below-mentioned select statement in Oracle SQL developer and get the ORA-00997: illegal use of LONG datatype ,00997. 00000 - "illegal use of LONG datatype" error. The Datatype of the column A.COLUMN_EXPRESSION is Long, is there was way where this can be converted to Varchar2? , Without the use of the DISTINCT clause, everything is working fine.

SELECT DISTINCT
  B.OWNER TABLE_OWNER,
  B.TABLE_NAME,
  A.INDEX_OWNER,
  A.INDEX_NAME,
  A.COLUMN_EXPRESSION,
  NVL(CNT,0) RCNT
FROM
  (
    SELECT
      COL.INDEX_OWNER,
      IND.INDEX_NAME,
      IND.TABLE_OWNER,
      IND.TABLE_NAME,
      EXP.COLUMN_EXPRESSION,
      1 CNT
    FROM
      ALL_INDEXES IND,
      ALL_IND_COLUMNS COL,
      ALL_IND_EXPRESSIONS EXP
    WHERE
      IND.TABLE_NAME   = COL.TABLE_NAME
    AND IND.INDEX_NAME = COL.INDEX_NAME
    AND IND.INDEX_NAME = EXP.INDEX_NAME
    AND IND.INDEX_TYPE LIKE 'FUN%'
  )
  A,
  ALL_INDEXES B
WHERE
  A.TABLE_NAME (+)   = B.TABLE_NAME
AND A.TABLE_OWNER (+)= B.TABLE_OWNER
AND B.TABLE_NAME    IN ('GA_EXPENDITURE_COMMITMENT_F','GA_ABC_DETAIL_RPTG_F')
AND B.TABLE_OWNER    = 'EDWFIN';
karthik
  • 185
  • 3
  • 13
  • 1
    You can retry within a PL/SQL statement rather than a direct SQL such as [this case](https://stackoverflow.com/questions/31004844/converting-long-to-varchar2) – Barbaros Özhan Jul 07 '22 at 10:48
  • What's the point for joining with `ALL_INDEXES B`? Without that join you don't have to use `distinct`... If you need to know what tables has indexes but not necessary with `column expression` then join with `all_tables ` and add condition for only tables with existing index. – robertus Jul 09 '22 at 21:34

0 Answers0