Convert long to clobs and varchar to varchar2 in Oracle setup script and investigate

[simonp]

From: ianwallen [ianwallen@…] Sent: Monday, 7 May 2012 10:43 PM To: geonetwork-users@… Subject: Re: [GeoNetwork-users] Script for Oracle database creation

You would not be the first person to do it... From the following document it explains how to perform a geonetworks installation and they supply a modified version of the script which uses CLOB's ​http://imos.org.au/fileadmin/user_upload/shared/AODN/EIF023_Implementation_Guide_V1.0.pdf

Personally, I would like to see the geonetwork script modified as dealing with LONGs are problematic. In our case we would like to query certain elements within the XML documents using XMLQUERY/XMLTABLE however we cannot since the data is in a LONG. And it does not looks like there are any ways to convert LONG to CLOB on the fly without making a custom function.

Also, as you mention, based on the Oracle documentation, LONG is depreciated and VARCHAR behaves the same as VARCHAR2 but should not be used as it is reserved for future use. So I'm not sure why this script uses LONG or VARCHAR?

We wanted to changed the datatypes in our environment as well however as we do no want to deviate from the supported documentation with fear that it may cause issues in the future, we have currently opted to create a monthly job that copies the data to another table converting the LONG to CLOB in the process. This means that we are always working with slightly stale information.