It is not supported, but if someone would like to do this, then this may save some time.
The customer asked if it is possible to resize pei_information_x fields in the per_people_extra_info table of Oracle E-Business Suite. I am not sure why, but this columns has limited field size of 150 bytes. It is business requirement to have things which is larger (especially in the multibyte env) than the allowed field sizes.
So we issued a simple command:
The customer asked if it is possible to resize pei_information_x fields in the per_people_extra_info table of Oracle E-Business Suite. I am not sure why, but this columns has limited field size of 150 bytes. It is business requirement to have things which is larger (especially in the multibyte env) than the allowed field sizes.
So we issued a simple command:
| alter
table HR.PER_PEOPLE_EXTRA_INFO modify (PEI_INFORMATION10
VARCHAR2(1024)); But when you use the following api to load data
DECLARE
l_person_extra_id NUMBER;
l_err_api VARCHAR2(1000);
l_object_version_number NUMBER;
l_validate BOOLEAN DEFAULT TRUE;
CURSOR cPersons
IS
(
SELECT
*
FROM Migration.UDK
WHERE Person_Id IS NOT NULL
AND Status IS NULL
)
;
BEGIN
--hr_utility.set_trace_options
('TRACE_DEST:DBMS_OUTPUT');
--hr_utility.trace_on;
FOR cvPersons IN cPersons
LOOP
BEGIN
hr_person_extra_info_api.create_person_extra_info(
P_VALIDATE =>
l_validate, P_PERSON_ID =>
cvPersons.Person_Id, P_INFORMATION_TYPE =>
'TEST', P_PEI_INFORMATION_CATEGORY =>
'TEST', P_PEI_INFORMATION1 =>
cvPersons."bla bla",
P_PEI_INFORMATION10 => cvPersons."Large keywords",
P_PERSON_EXTRA_INFO_ID =>
l_person_extra_id, P_OBJECT_VERSION_NUMBER =>
l_object_version_number);
COMMIT;
--hr_utility.trace_off;
BEGIN
UPDATE Migration.UDK
SET Status = 'Processed'
,Err_Msg =NULL
WHERE Person_Id =
cvPersons.Person_Id;
EXCEPTION
WHEN OTHERS THEN
NULL;
END;
COMMIT;
EXCEPTION
WHEN OTHERS THEN
l_err_api := SQLERRM;
UPDATE Migration.UDK
SET Status
= 'Error'
,ERR_MSG = l_err_api
WHERE Person_Id = cvPersons.Person_Id;
COMMIT;
END;
END LOOP;
IF l_person_extra_id IS NOT NULL THEN
dbms_output.put_line('person_extra_id
'||l_person_extra_id);
END IF;
END;
It ends with ORA-06502: PL/SQL: numeric or value error: character string buffer too small error. This is because before insert, the values are stored in the g_rec_type record datatype, which defines those fields to be of VARCHAR2(150). This record datatype is defined in pe_pei_shd package definition.
|
