6 Replies Latest reply on Sep 19, 2018 12:04 PM by Beauty_and_dBest

    EBS 12.0 upgrade guide HRMS


      EBS 12.0.6 upgrade to 12.2.6 or 12.2.7

      12c database


      Oracle® E-Business Suite

      Upgrade Guide

      Release 12.0 and 12.1 to 12.2

      Part No. E48839-16

      September 2017


      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';









      Please help...



      Kind regards,


        • 1. Re: EBS 12.0 upgrade guide HRMS
          Pravin Takpire

          what exactly you are trying to acheive ?

          • 2. Re: EBS 12.0 upgrade guide HRMS

            Hi dear friend,


            Per upgrade guide, it requires that all LONG datatype must be converted to CLOB

            Or do I disregard this one? Oracle docs sometimes is not so accurate on its statements



            Human Resource Management (HRMS)

            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






            FROM all_arguments

            WHERE (package_name,object_name) IN





            FROM hr_api_hook_calls


            AND data_type = 'LONG';


            When all database objects have been identified, change the datatype to CLOB



            Oracle should have included  the solution how to do it, rather than making us search and search adding pain to the vague documentation.



            Kind regards,

            • 3. Re: EBS 12.0 upgrade guide HRMS
              Pravin Takpire

              All custom database objects --> is your object custom ?




              • 4. Re: EBS 12.0 upgrade guide HRMS

                Hi Pravin,


                Actually I do not know

                But the given query to run has provided an output, so to be safe it is better to assume it as custom.



                Kind regards,

                • 5. Re: EBS 12.0 upgrade guide HRMS

                  You can (apparently) ignore this object.


                  Does PSB_HR_POPULATE_DATA_PVT Data Type Need to be Changed to CLOB? (Doc ID 1969202.1)

                  • 6. Re: EBS 12.0 upgrade guide HRMS

                    Thanks ALL