12c
Hi ALL,
How do I change LONG to CLOB?
SQL> SELECT package_name||', '||object_name||', '||argument_name||', '||data_type FROM all_arguments WHERE (package_name,object_name) IN (SELECT call_package,call_procedure FROM hr_api_hook_calls) AND data_type = 'LONG';
PACKAGE_NAME||','||OBJECT_NAME||','||ARGUMENT_NAME||','||DATA_TYPE
--------------------------------------------------------------------------------
PSB_HR_POPULATE_DATA_PVT, INSERT_POSITION_TXN_INFO, P_COMMENTS, LONG
SQL>
All custom database objects that are mapped to seeded APIs/Row Handlers must be
changed from LONG/LONG RAW to CLOB.
The LONG and LONG RAW data type was obsoleted by the database group in release 8
i and was replaced by the CLOB data type. LONG and LONG RAW remain a valid data
type only for backward compatibility and have numerous restrictions. Many database
features after release 8i do not support the LONG and LONG RAW data type, and
interferes with upgrading to new technologies. Specifically, for Online Patching, LONG
and LONG RAW columns cannot be referenced in a database trigger. This means that
LONG and LONG RAW columns cannot be patched using Online Patching as the
solution uses Cross Edition Triggers to upgrade data. Changes to seed data in the RUN
edition cannot be propagated to the PATCH edition as Cross Edition Triggers are used
to synchronize the changes. Due to this, all database objects/Oracle Forms/JAVA
pages/PRO C/API programs have been changed to use CLOB data type that were
previously using LONG and LONG RAW data types.
However, Oracle provides implicit conversion between LONG/LONG RAW data types
and LOB data types, with one limitation in this conversion. The maximum size of a LOB
is 128 terabytes depending on database block size, and the maximum size of a LONG is
two gigabytes. If more than two gigabytes of data is assigned to LONG/LONG RAW
data types from the CLOB data type, then a VALUE_ERROR exception will be raised.
All of the seed Database Objects/Oracle Forms/JAVA Pages/PRO C code/APIs have
been modified to use the CLOB data type and provides more storage. If these new large
values are passed to old custom programs that continue to use old LONG/LONG RAW
data type and have less storage, then the exception error will be raised.
This enhancement will affect the functionality of User Hook pre-processors, Business
Event pre-processors, and Data Pump pre-processors. Oracle suggests that you use the
following script to identify procedure/UDF containing LONG parameters, and mapped
with seeded APIs/Row Handlers:
Preparing for the Upgrade 2-7
SELECT
package_name
,object_name
,argument_name
,data_type
FROM all_arguments
WHERE (package_name,object_name) IN
(
SELECT
call_package
,call_procedure
FROM hr_api_hook_calls
)
AND data_type = 'LONG';
When all database objects have been identified, change the datatype to CLOB
Please help...
Kind regards,
jc