ORA-00997: illegal use of LONG datatype
During PeopleTools upgrade or PeopleSoft application upgrade, you may get an Oracle error: ORA-00997: illegal use of LONG datatype. This post will walk you through the steps to workaround and resolve the error.
I encountered this error during PTools upgrade and based on the symptoms, it looks like you may face this error in PeopleSoft application upgrade as well.
This error is common to the PeopleTools upgrade step “Running the Updated PeopleTools Script.” It is usually encountered when running the scripts such as BuildPPLTLS84CURTables or rel85xxx.sql or REL8xxnDBTSFIX.sql scripts.
How to Resolve ORA-00997: illegal use of LONG datatype
The root cause of the error is presence of a LONG data type in the table for which error occurs. This error occurs because LONG fields are not supported in 9.0 or above applications and the scripts are not designed to handle this because of which they error out.
Go to the bottom of the script, which has errored out and it will have a table name.
Have a look at the DDL of the table in error. For example, if using Oracle database, you can perform a describe on the table to get the DDL of the same. You’ll see the field, which is of type LONG.
To resolve the error, go to the Staging directory of your Change Assistant and open the script in error. Take a backup of the script if you want to otherwise there is no special need as the script is also present in the Output directory.
Comment out the statements that have already been executed successfully.
Now, modify the select clause of the insert statement to put “TO_LOB( )” around the LONG field.
The following is an example of the updated script, which did not encounter the same error.
You might have multiple failures of the same script for different tables in the same script. You’ll have to apply the same fix to all of them.
