General discussion

Locked

ORA-00997: illegal use of LONG datatype

By loganj ·
netadmin:

slightly modified your script listed within your dba article and executed the script on a table that has duplicates, however, received the attached error. please respond with a possible solution. i would like to determine the duplicate data, and then modify the same sql to delete the duplicates. thank you in advance for your help.

dr. james s. logan

/export/home/oracle/SCRIPTS>more duprow.sql
SELECT
LFPFX,
LFKEY,
CDATA
FROM
SSYSTEM A
WHERE
rowid SELECT min(rowid) FROM SSYSTEM B
WHERE
B.LFPFX = A.LFPFX
and
B.LFKEY = A.LFKEY
and
B.CDATA = A.CDATA
);


------------------------------------------------log--------------------------------------------

SQL> @duprow.sql
B.CDATA = A.CDATA
*
ERROR at line 15:
ORA-00997: illegal use of LONG datatype


SQL> quit

This conversation is currently closed to new comments.

3 total posts (Page 1 of 1)  
| Thread display: Collapse - | Expand +

All Comments

Collapse -

ORA-00997: illegal use of LONG datatype

by sparent In reply to ORA-00997: illegal use of ...

I would venture to say that CDATA is your LONG column. Oracle does not allow table joins on LONG columns.

You may have to resort to PL/SLQ to do what you want to do. (But that's a little more work!)

Collapse -

ORA-00997: illegal use of LONG datatype

by loganj In reply to ORA-00997: illegal use of ...

Yes,CDATA LONG RAW..can you post an example that would deal with this issue, as was post to handle the previous issue ? Thank you in advance for your help.

Dr. Logan

Collapse -

ORA-00997: illegal use of LONG datatype

by sparent In reply to ORA-00997: illegal use of ...

To give you a working (read, debugged) example that would work would take me at least a few hours that I don't have.

I have included the pseudo-code of what you want to do. Good luck!

Loop through every row of the SSYSTEM table,
ordered by LFPX, LFKEY, ROWID
If first time through or current row's
LFPX and LFKEY are different than
those of the previous row
Then
save current row as previous row
save current row's CDATA into an emptied
array
Else
Check current row's CDATA against each
array element.

If a match is found within the array
Then
this is a duplicate than can be deleted
Else
save current row's CDATA into the array
End If
End If

Get next row
End Loop

Back to Linux Forum
3 total posts (Page 1 of 1)  

Related Forums