:

ORA-00997: illegal use of LONG datatype

I had a requirement to keep track of the LOBs which were in the DB, their partition names and high values. We wanted to keep this information in a small lookup table so I first attempted to run a Create Table As Select (CTAS) statement, to which I received the following error:

ERROR at line 1:

ORA-00997: illegal use of LONG datatype

The views I need to get data from were:

  • USER_IND_PARTITIONS
  • USER_IND_SUBPARTITIONS
  • USER_LOB_PARTITIONS
  • USER_LOB_SUBPARTITIONS

And from these views I wanted to select two LONG columns, both of which are the high value columns.

The commnad that I found to use was Oracle’s SQL Plus COPY command. It allows you to copy data from one database to another, and an example of it is below:

copy from <user>/<password>@<database> create lob_seg_info -
 using
SELECT
   LP.TABLE_NAME "Table Name"
  , LP.COLUMN_NAME "Column Name"
  , LP.LOB_NAME "LOB Name"
  , LP.PARTITION_NAME "Tab Part Name"
  , LP.LOB_PARTITION_NAME "LOB Part Name"
  , IP.HIGH_VALUE "Part High Val"
  , LSP.subpartitioN_name "Tab Sub Part Name"
  , LSP.LOB_SUBPARTITION_NAME "LOB Sub Part Name"
  , IP.INDEX_NAME "LOB Ind Name"
  , IP.PARTITION_NAME "LOB Ind Part Name"
  , ISP.SUBPARTITION_NAME "LOB Ind Sub Part Name2"
  , LSP.LOB_INDSUBPART_NAME "LOB Ind Sub Part Name"
FROM
     USER_IND_PARTITIONS IP
   , USER_IND_SUBPARTITIONS ISP
   , USER_LOB_PARTITIONS LP
   , USER_LOB_SUBPARTITIONS LSP
WHERE
  LP.LOB_PARTITION_NAME = LSP.LOB_PARTITION_NAME
AND
  LP.LOB_INDPART_NAME = ISP.PARTITION_NAME
AND
  IP.PARTITION_NAME = LP.LOB_INDPART_NAME;

A few things that I found while doing this:

1. Ensure that there are no carriage returns in your string. My first attemp at this resulted in me creating a table with 37 million rows because it did a cartesian join without me realising that it had treated the where conditions as separate lines and not as part of the statement.

2. You must make sure that you set the LONG SQL Plus environment variable otherwise any data in the LONG column which is longer than the value of LONG will be truncated to that length in the new table.

3. You can SET COPYCOMMIT n environment variable in order to tell Oracle to perform a commit after n rows.

Seems strange that Oracle’s tables/views have LONG columns in them still and they haven’t converted them to LOBs.

Like it, share it...

Category: SQL


Related Posts

Leave a Reply

Your email address will not be published. Required fields are marked *