Stack Overflow
Stack Overflow

Questions tagged [ora-00997]

ORA-00997: illegal use of LONG datatype

Error: 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.

Additional information: This error is due to a known restriction where it is not possible to execute an INSERT statement with SELECT or CREATE TABLE AS SELECT involving tables with LONG datatypes. It is possible, however, to perform the data transfer using the COPY command.

8 questions
12
votes
3 answers

Converting Long to Varchar2

I am trying to insert into varchar2 column from a long column. here is the below example, TEXT.TEXT_COL = VARCHAR2(4000) and NOTE.TEXT_NOTE = LONG. INSERT INTO TEXT(ROW_ID, TEXT_COL) SELECT 1, TEXT_NOTE FROM NOTE; When i run the above sql i get…
Kriti
  • 197
  • 1
  • 4
  • 11
9
votes
3 answers

Get the LENGTH of a LONG RAW

I have a table with a column of data type LONG RAW. How do I determine the size (in bytes) of the data in this column? If I call the LENGTH function on it, it raises ORA-00932: inconsistent datatypes: expected NUMBER got LONG BINARY. Just in case…
Jeffrey Kemp
  • 59,135
  • 14
  • 106
  • 158
3
votes
3 answers

Illegal use of LONG datatype Oracle

this is my firt question here I have a problem trying to convert a table column (long raw) to a base 64 string, this column contains some of the employees pictures. This is the query, the field that i'm trying to convert is f.fot_empl: SELECT…
diegofer11
  • 41
  • 2
  • 8
2
votes
3 answers

How to include the column USER_VIEWS.TEXT in a where clause

This seems like it should have been an easy thing to figure out but I am struggling to find any answers. I want to be able to query against the USER_VIEWS table in Oracle to find other views that are using a particular table. Something like: SELECT…
northpole
  • 10,244
  • 7
  • 35
  • 58
0
votes
0 answers

ORA-00997: illegal use of LONG datatype when "fetch next 1 rows only" is used

We have a table called CONTACT_INFO that has a column COMMENTS of type LONG. These queries work fine: select comments from contact_info; select comments from contact_info where rownum <= 3; However, this query throws ORA-00997: select comments from…
Timothy Blaisdell
  • 129
  • 8
0
votes
2 answers

How to visualize CLOB content - Oracle

I want to select and visualize the content of a column with CLOB datatype (>100 bytes). select UTL_RAW.CAST_TO_VARCHAR2(DBMS_LOB.SUBSTR(ds_cirurgia,1,4000)) from AVISO_CIRURGIA where cd_paciente = 123456789; But I get this error: [SELECT - 0…
Henrique Amaral
  • 47
  • 2
  • 9
0
votes
1 answer

Oracle Long Raw Problem

select utl_raw.cast_to_varchar2(DCFILE) hexchar from T_FILE ORA-00997: illegal use of LONG datatype select to_char(DOC_FILE) hexchar from T_DOC_FILE ORA-00932: inconsistent datatypes: expected CHAR got LONG BINARY My column type long raw, how to…
Chicharito
  • 1,450
  • 8
  • 31
  • 49
0
votes
1 answer

How to extract data from a LONG column holding XML strings

Here is my table MYTABLE(ID NUMBER(10), DATATYPE VARCHAR2(2 BYTE), XMLDATA LONG ) Note1: I cannot alter this table Note2: I'm using Oracle 10g Here is a sample of XMLDATA value to…
Philippe
  • 1,733
  • 1
  • 14
  • 28