MrHopkinson
Technical User
- Mar 21, 2007
- 23
SYS.DBMS_OUTPUT buffer overflow error - please advise!
hello.
Could someone please please please read through my PLSQL code and tell me why its bombing out with SYS.DBMS_OUTPUT buffer error? Ive limited what I thought was the problem - the 2nd cursor so it only reads 1 record in before deciding to go ahead with the API updates but it still errors out..
As you can see Ive also tried commenting out most of the dbms_ouput lines but to no avail..
I did try the serveroutput ON SIZE UNLIMITED and the other unlimited thing you now do in 10gr2 but again the code was just hanging for 30+ minutes..
=========== my script ==== ================
/* Formatted on 2009/01/30 11:11 (Formatter Plus v4.8.7) */
SET serveroutput ON SIZE 1000000
--serveroutput ON SIZE UNLIMITED FORMAT WORD_WRAPPED
--SET serveroutput OFF
SET verify OFF
SET feedback OFF
DECLARE
-- *********
-- Debugging/error handling
-- *********
v_err_seq NUMBER := 0;
v_err_num VARCHAR2 (30);
v_err_msg VARCHAR2 (250);
v_err_line VARCHAR2 (350);
-- *********
-- Work variables
-- *********
p_hire_date DATE;
p_business_group_id NUMBER := 0;
p_person_id NUMBER := 0;
p_address_line1 VARCHAR2 (240);
p_date_of_birth VARCHAR2 (35);
p_address_line2 VARCHAR2 (240);
employee_number VARCHAR2 (14);
p_employee_number VARCHAR2 (14);
emp_number VARCHAR2 (14);
p_email_address VARCHAR2 (240);
p_address_line3 VARCHAR2 (240);
p_first_name VARCHAR2 (150);
p_address_line4 VARCHAR2 (240);
p_middle_names VARCHAR2 (30);
p_post_code VARCHAR2 (30);
p_last_name VARCHAR2 (150);
p_nationality VARCHAR2 (30);
p_sex VARCHAR2 (30);
p_national_identifier VARCHAR2 (30);
p_title VARCHAR2 (30);
v_rec_cnt NUMBER := 0;
insert_flag VARCHAR2 (8);
-- ip_p_address_id NUMBER;
ip_p_address_id per_addresses.address_id%TYPE;
ip_p_object_version_number NUMBER;
ip_p_party_id per_addresses.party_id%TYPE;
l_person_id NUMBER;
l_employee_number VARCHAR2 (35);
l_validate BOOLEAN DEFAULT FALSE;
l_assignment_id NUMBER;
l_per_object_version_number NUMBER;
l_asg_object_version_number NUMBER;
l_per_effective_start_date DATE;
l_per_effective_end_date DATE;
l_full_name VARCHAR2 (240);
l_per_comment_id NUMBER;
l_assignment_sequence NUMBER;
l_assignment_number VARCHAR2 (100);
l_name_combination_warning BOOLEAN := FALSE;
l_assign_payroll_warning BOOLEAN := FALSE;
l_address_id NUMBER;
l_object_version_number NUMBER;
-- return_code NUMBER;
-- return_message VARCHAR2 (2000);
-- command_prin VARCHAR2 (9000);
-- fh UTL_FILE.FILE_TYPE;
-- path VARCHAR2(135);
-- name VARCHAR2(30);
-- ***********************************
-- Get employee details info from work table
-- ***********************************
CURSOR get_employee_details
IS
SELECT p_person_id, p_validate, p_hire_date, p_business_group_id,
p_last_name, p_sex, p_date_of_birth, p_email_address,
p_employee_number, p_first_name, p_marital_status,
p_middle_names, p_nationality, p_title, p_national_identifier,
p_address_line1, p_address_line2, p_address_line3,
p_address_line4, p_post_code
FROM SU_TEMPLOYEE_DETAILS;
-- *****************************************
-- checks employee details info from PER_ALL_PEOPLE_F table
-- *****************************************
CURSOR c_check_employee (emp_number VARCHAR2)
IS
SELECT per.person_id, per.business_group_id, per.last_name,
per.start_date, per.date_of_birth, per.email_address,
per.employee_number, per.first_name, per.marital_status,
per.middle_names, per.nationality, per.national_identifier,
per.sex, per.title, padd.address_id, padd.primary_flag,
padd.address_line1, padd.address_line2, padd.address_line3,
padd.town_or_city, padd.postal_code, padd.telephone_number_1,
padd.object_version_number
FROM per_all_people_f per, per_addresses padd
WHERE per.employee_number = emp_number
AND TRUNC (per.start_date) > '01-JAN-2009'
AND per.person_id = padd.person_id;
emp_rec c_check_employee%ROWTYPE;
BEGIN
--v_err_seq := 2;
-- command_prin := SQLERRM;
-- p(l_string);
-- path := '%SU_TOP/employees';
-- name := 'utl_tester.txt';
LOOP
-- ***********************************
-- Process each record in the work table
-- ***********************************
FOR v_emp IN get_employee_details
LOOP
v_rec_cnt := v_rec_cnt + 1;
-- ************************************
-- determine whether customer already exists
-- ************************************
OPEN c_check_employee (v_emp.p_employee_number);
FETCH c_check_employee
INTO emp_rec;
IF c_check_employee%NOTFOUND
THEN
insert_flag := 'I';
ELSE
insert_flag := 'X';
END IF;
IF insert_flag = 'I'
THEN
DBMS_OUTPUT.PUT_LINE
('Employee does not exist, continue import..');
ELSE
DBMS_OUTPUT.PUT_LINE
('Employee found - record cannot be imported.');
END IF;
CLOSE c_check_employee;
-- ***********************************
-- Create new PER_ALL_PEOPLE_F and PER_ADDRESSES record from
-- info in table record
-- ***********************************
IF insert_flag = 'I'
THEN
BEGIN -- Importing Employee Procedure --
--fh := UTL_FILE.FOPEN(path, name, 'w');
--UTL_FILE.PUT_LINE( fh,'Importing employees....Hold On.......!');
--UTL_FILE.FCLOSE(fh);
-- DBMS_OUTPUT.PUT_LINE (' ');
-- DBMS_OUTPUT.PUT_LINE ('Importing employees....Hold On.......! ');
-- DBMS_OUTPUT.PUT_LINE (' ');
BEGIN
Hr_Employee_Api.create_gb_employee
(p_validate => l_validate,
p_hire_date => v_emp.p_hire_date,
p_business_group_id => v_emp.p_business_group_id,
p_date_of_birth => v_emp.p_date_of_birth,
p_email_address => v_emp.p_email_address,
p_first_name => v_emp.p_first_name,
p_middle_names => v_emp.p_middle_names,
p_last_name => v_emp.p_last_name,
p_sex => v_emp.p_sex,
p_ni_number => v_emp.p_national_identifier,
p_employee_number => v_emp.p_employee_number,
p_person_id => l_person_id,
p_title => v_emp.p_title,
p_assignment_id => l_assignment_id,
p_per_object_version_number => l_per_object_version_number,
p_asg_object_version_number => l_asg_object_version_number,
p_per_effective_start_date => l_per_effective_start_date,
p_per_effective_end_date => l_per_effective_end_date,
p_full_name => l_full_name,
p_per_comment_id => l_per_comment_id,
p_assignment_sequence => l_assignment_sequence,
p_assignment_number => l_assignment_number,
p_name_combination_warning => l_name_combination_warning,
p_assign_payroll_warning => l_assign_payroll_warning
);
-- DBMS_OUTPUT.PUT_LINE ('..employee record updated succesfully..');
-- DBMS_OUTPUT.PUT_LINE (' ');
EXCEPTION
WHEN OTHERS
THEN
NULL;
-- DBMS_OUTPUT.PUT_LINE ('..SQLCodeErrors:- ' || SQLCODE);
-- DBMS_OUTPUT.PUT_LINE (' ');
-- DBMS_OUTPUT.PUT_LINE ('Employee No:-' || v_emp.p_employee_number);
-- DBMS_OUTPUT.PUT_LINE ('Last name '|| v_emp.p_last_name);
-- DBMS_OUTPUT.PUT_LINE ('Record failed to load.. ' || SQLERRM);
-- DBMS_OUTPUT.PUT_LINE ('Record failed to load.. ' || l_string);
-- DBMS_OUTPUT.PUT_LINE (SUBSTR(command_prin, 1, 250));
END;
BEGIN -- Importing Associated Address Procedure --
-- DBMS_OUTPUT.PUT_LINE ('..and the associated employee address....');
Hr_Person_Address_Api.create_person_address
(p_validate => l_validate,
-- p_effective_date => v_emp.p_hire_date,
p_effective_date => SYSDATE,
p_pradd_ovlapval_override => NULL,
p_validate_county => NULL,
p_person_id => l_person_id,
p_primary_flag => 'Y',
p_style => 'GB_GLB',
-- p_date_from => v_emp.p_hire_date,
p_date_from => SYSDATE,
p_date_to => NULL,
p_address_type => NULL,
p_comments => NULL,
p_address_line1 => v_emp.p_address_line1,
p_address_line2 => v_emp.p_address_line2,
p_address_line3 => v_emp.p_address_line3,
p_town_or_city => v_emp.p_address_line4,
p_region_1 => NULL,
p_region_2 => NULL,
p_region_3 => NULL,
p_postal_code => v_emp.p_post_code,
p_country => v_emp.p_nationality,
p_telephone_number_1 => NULL,
p_telephone_number_2 => NULL,
p_telephone_number_3 => NULL,
p_addr_attribute_category => NULL,
p_addr_attribute1 => NULL,
p_addr_attribute2 => NULL,
p_addr_attribute3 => NULL,
p_addr_attribute4 => NULL,
p_addr_attribute5 => NULL,
p_addr_attribute6 => NULL,
p_addr_attribute7 => NULL,
p_addr_attribute8 => NULL,
p_addr_attribute9 => NULL,
p_addr_attribute10 => NULL,
p_addr_attribute11 => NULL,
p_addr_attribute12 => NULL,
p_addr_attribute13 => NULL,
p_addr_attribute14 => NULL,
p_addr_attribute15 => NULL,
p_addr_attribute16 => NULL,
p_addr_attribute17 => NULL,
p_addr_attribute18 => NULL,
p_addr_attribute19 => NULL,
p_addr_attribute20 => NULL,
p_add_information13 => NULL,
p_add_information14 => NULL,
p_add_information15 => NULL,
p_add_information16 => NULL,
p_add_information17 => NULL,
p_add_information18 => NULL,
p_add_information19 => NULL,
p_add_information20 => NULL,
-- p_party_id => NULL,
p_party_id => ip_p_party_id,
p_address_id => ip_p_address_id,
p_object_version_number => ip_p_object_version_number
);
-- DBMS_OUTPUT.PUT_LINE ('Address Updation/Insertion has been successful!');
-- EXIT WHEN command_prin IS NULL;
-- command_prin := SUBSTR (command_prin, 251);
END;
END;
-- ******************************
-- End of customer related details
-- ******************************
-- ******************************
END IF;
END LOOP;
-- DBMS_OUTPUT.PUT_LINE ('Records read : ' || v_rec_cnt);
--EXCEPTION
-- WHEN OTHERS THEN
-- ROLLBACK;
-- Output Error Message
-- v_err_num := TO_CHAR(SQLCODE);
-- v_err_msg := SUBSTR(SQLERRM,1,250);
-- v_err_line := 'Oracle error (seqno=' || v_err_seq || ') ' ||
-- v_err_num ||' occurred processing record '||
-- TO_CHAR(v_rec_cnt + 1) ||' : '||v_err_msg;
-- DBMS_OUTPUT.PUT_LINE(v_err_line);
END LOOP;
COMMIT;
END;
--END;
/
EXIT;
======================================
much, much obliged..
Steven
hello.
Could someone please please please read through my PLSQL code and tell me why its bombing out with SYS.DBMS_OUTPUT buffer error? Ive limited what I thought was the problem - the 2nd cursor so it only reads 1 record in before deciding to go ahead with the API updates but it still errors out..
As you can see Ive also tried commenting out most of the dbms_ouput lines but to no avail..
I did try the serveroutput ON SIZE UNLIMITED and the other unlimited thing you now do in 10gr2 but again the code was just hanging for 30+ minutes..
=========== my script ==== ================
/* Formatted on 2009/01/30 11:11 (Formatter Plus v4.8.7) */
SET serveroutput ON SIZE 1000000
--serveroutput ON SIZE UNLIMITED FORMAT WORD_WRAPPED
--SET serveroutput OFF
SET verify OFF
SET feedback OFF
DECLARE
-- *********
-- Debugging/error handling
-- *********
v_err_seq NUMBER := 0;
v_err_num VARCHAR2 (30);
v_err_msg VARCHAR2 (250);
v_err_line VARCHAR2 (350);
-- *********
-- Work variables
-- *********
p_hire_date DATE;
p_business_group_id NUMBER := 0;
p_person_id NUMBER := 0;
p_address_line1 VARCHAR2 (240);
p_date_of_birth VARCHAR2 (35);
p_address_line2 VARCHAR2 (240);
employee_number VARCHAR2 (14);
p_employee_number VARCHAR2 (14);
emp_number VARCHAR2 (14);
p_email_address VARCHAR2 (240);
p_address_line3 VARCHAR2 (240);
p_first_name VARCHAR2 (150);
p_address_line4 VARCHAR2 (240);
p_middle_names VARCHAR2 (30);
p_post_code VARCHAR2 (30);
p_last_name VARCHAR2 (150);
p_nationality VARCHAR2 (30);
p_sex VARCHAR2 (30);
p_national_identifier VARCHAR2 (30);
p_title VARCHAR2 (30);
v_rec_cnt NUMBER := 0;
insert_flag VARCHAR2 (8);
-- ip_p_address_id NUMBER;
ip_p_address_id per_addresses.address_id%TYPE;
ip_p_object_version_number NUMBER;
ip_p_party_id per_addresses.party_id%TYPE;
l_person_id NUMBER;
l_employee_number VARCHAR2 (35);
l_validate BOOLEAN DEFAULT FALSE;
l_assignment_id NUMBER;
l_per_object_version_number NUMBER;
l_asg_object_version_number NUMBER;
l_per_effective_start_date DATE;
l_per_effective_end_date DATE;
l_full_name VARCHAR2 (240);
l_per_comment_id NUMBER;
l_assignment_sequence NUMBER;
l_assignment_number VARCHAR2 (100);
l_name_combination_warning BOOLEAN := FALSE;
l_assign_payroll_warning BOOLEAN := FALSE;
l_address_id NUMBER;
l_object_version_number NUMBER;
-- return_code NUMBER;
-- return_message VARCHAR2 (2000);
-- command_prin VARCHAR2 (9000);
-- fh UTL_FILE.FILE_TYPE;
-- path VARCHAR2(135);
-- name VARCHAR2(30);
-- ***********************************
-- Get employee details info from work table
-- ***********************************
CURSOR get_employee_details
IS
SELECT p_person_id, p_validate, p_hire_date, p_business_group_id,
p_last_name, p_sex, p_date_of_birth, p_email_address,
p_employee_number, p_first_name, p_marital_status,
p_middle_names, p_nationality, p_title, p_national_identifier,
p_address_line1, p_address_line2, p_address_line3,
p_address_line4, p_post_code
FROM SU_TEMPLOYEE_DETAILS;
-- *****************************************
-- checks employee details info from PER_ALL_PEOPLE_F table
-- *****************************************
CURSOR c_check_employee (emp_number VARCHAR2)
IS
SELECT per.person_id, per.business_group_id, per.last_name,
per.start_date, per.date_of_birth, per.email_address,
per.employee_number, per.first_name, per.marital_status,
per.middle_names, per.nationality, per.national_identifier,
per.sex, per.title, padd.address_id, padd.primary_flag,
padd.address_line1, padd.address_line2, padd.address_line3,
padd.town_or_city, padd.postal_code, padd.telephone_number_1,
padd.object_version_number
FROM per_all_people_f per, per_addresses padd
WHERE per.employee_number = emp_number
AND TRUNC (per.start_date) > '01-JAN-2009'
AND per.person_id = padd.person_id;
emp_rec c_check_employee%ROWTYPE;
BEGIN
--v_err_seq := 2;
-- command_prin := SQLERRM;
-- p(l_string);
-- path := '%SU_TOP/employees';
-- name := 'utl_tester.txt';
LOOP
-- ***********************************
-- Process each record in the work table
-- ***********************************
FOR v_emp IN get_employee_details
LOOP
v_rec_cnt := v_rec_cnt + 1;
-- ************************************
-- determine whether customer already exists
-- ************************************
OPEN c_check_employee (v_emp.p_employee_number);
FETCH c_check_employee
INTO emp_rec;
IF c_check_employee%NOTFOUND
THEN
insert_flag := 'I';
ELSE
insert_flag := 'X';
END IF;
IF insert_flag = 'I'
THEN
DBMS_OUTPUT.PUT_LINE
('Employee does not exist, continue import..');
ELSE
DBMS_OUTPUT.PUT_LINE
('Employee found - record cannot be imported.');
END IF;
CLOSE c_check_employee;
-- ***********************************
-- Create new PER_ALL_PEOPLE_F and PER_ADDRESSES record from
-- info in table record
-- ***********************************
IF insert_flag = 'I'
THEN
BEGIN -- Importing Employee Procedure --
--fh := UTL_FILE.FOPEN(path, name, 'w');
--UTL_FILE.PUT_LINE( fh,'Importing employees....Hold On.......!');
--UTL_FILE.FCLOSE(fh);
-- DBMS_OUTPUT.PUT_LINE (' ');
-- DBMS_OUTPUT.PUT_LINE ('Importing employees....Hold On.......! ');
-- DBMS_OUTPUT.PUT_LINE (' ');
BEGIN
Hr_Employee_Api.create_gb_employee
(p_validate => l_validate,
p_hire_date => v_emp.p_hire_date,
p_business_group_id => v_emp.p_business_group_id,
p_date_of_birth => v_emp.p_date_of_birth,
p_email_address => v_emp.p_email_address,
p_first_name => v_emp.p_first_name,
p_middle_names => v_emp.p_middle_names,
p_last_name => v_emp.p_last_name,
p_sex => v_emp.p_sex,
p_ni_number => v_emp.p_national_identifier,
p_employee_number => v_emp.p_employee_number,
p_person_id => l_person_id,
p_title => v_emp.p_title,
p_assignment_id => l_assignment_id,
p_per_object_version_number => l_per_object_version_number,
p_asg_object_version_number => l_asg_object_version_number,
p_per_effective_start_date => l_per_effective_start_date,
p_per_effective_end_date => l_per_effective_end_date,
p_full_name => l_full_name,
p_per_comment_id => l_per_comment_id,
p_assignment_sequence => l_assignment_sequence,
p_assignment_number => l_assignment_number,
p_name_combination_warning => l_name_combination_warning,
p_assign_payroll_warning => l_assign_payroll_warning
);
-- DBMS_OUTPUT.PUT_LINE ('..employee record updated succesfully..');
-- DBMS_OUTPUT.PUT_LINE (' ');
EXCEPTION
WHEN OTHERS
THEN
NULL;
-- DBMS_OUTPUT.PUT_LINE ('..SQLCodeErrors:- ' || SQLCODE);
-- DBMS_OUTPUT.PUT_LINE (' ');
-- DBMS_OUTPUT.PUT_LINE ('Employee No:-' || v_emp.p_employee_number);
-- DBMS_OUTPUT.PUT_LINE ('Last name '|| v_emp.p_last_name);
-- DBMS_OUTPUT.PUT_LINE ('Record failed to load.. ' || SQLERRM);
-- DBMS_OUTPUT.PUT_LINE ('Record failed to load.. ' || l_string);
-- DBMS_OUTPUT.PUT_LINE (SUBSTR(command_prin, 1, 250));
END;
BEGIN -- Importing Associated Address Procedure --
-- DBMS_OUTPUT.PUT_LINE ('..and the associated employee address....');
Hr_Person_Address_Api.create_person_address
(p_validate => l_validate,
-- p_effective_date => v_emp.p_hire_date,
p_effective_date => SYSDATE,
p_pradd_ovlapval_override => NULL,
p_validate_county => NULL,
p_person_id => l_person_id,
p_primary_flag => 'Y',
p_style => 'GB_GLB',
-- p_date_from => v_emp.p_hire_date,
p_date_from => SYSDATE,
p_date_to => NULL,
p_address_type => NULL,
p_comments => NULL,
p_address_line1 => v_emp.p_address_line1,
p_address_line2 => v_emp.p_address_line2,
p_address_line3 => v_emp.p_address_line3,
p_town_or_city => v_emp.p_address_line4,
p_region_1 => NULL,
p_region_2 => NULL,
p_region_3 => NULL,
p_postal_code => v_emp.p_post_code,
p_country => v_emp.p_nationality,
p_telephone_number_1 => NULL,
p_telephone_number_2 => NULL,
p_telephone_number_3 => NULL,
p_addr_attribute_category => NULL,
p_addr_attribute1 => NULL,
p_addr_attribute2 => NULL,
p_addr_attribute3 => NULL,
p_addr_attribute4 => NULL,
p_addr_attribute5 => NULL,
p_addr_attribute6 => NULL,
p_addr_attribute7 => NULL,
p_addr_attribute8 => NULL,
p_addr_attribute9 => NULL,
p_addr_attribute10 => NULL,
p_addr_attribute11 => NULL,
p_addr_attribute12 => NULL,
p_addr_attribute13 => NULL,
p_addr_attribute14 => NULL,
p_addr_attribute15 => NULL,
p_addr_attribute16 => NULL,
p_addr_attribute17 => NULL,
p_addr_attribute18 => NULL,
p_addr_attribute19 => NULL,
p_addr_attribute20 => NULL,
p_add_information13 => NULL,
p_add_information14 => NULL,
p_add_information15 => NULL,
p_add_information16 => NULL,
p_add_information17 => NULL,
p_add_information18 => NULL,
p_add_information19 => NULL,
p_add_information20 => NULL,
-- p_party_id => NULL,
p_party_id => ip_p_party_id,
p_address_id => ip_p_address_id,
p_object_version_number => ip_p_object_version_number
);
-- DBMS_OUTPUT.PUT_LINE ('Address Updation/Insertion has been successful!');
-- EXIT WHEN command_prin IS NULL;
-- command_prin := SUBSTR (command_prin, 251);
END;
END;
-- ******************************
-- End of customer related details
-- ******************************
-- ******************************
END IF;
END LOOP;
-- DBMS_OUTPUT.PUT_LINE ('Records read : ' || v_rec_cnt);
--EXCEPTION
-- WHEN OTHERS THEN
-- ROLLBACK;
-- Output Error Message
-- v_err_num := TO_CHAR(SQLCODE);
-- v_err_msg := SUBSTR(SQLERRM,1,250);
-- v_err_line := 'Oracle error (seqno=' || v_err_seq || ') ' ||
-- v_err_num ||' occurred processing record '||
-- TO_CHAR(v_rec_cnt + 1) ||' : '||v_err_msg;
-- DBMS_OUTPUT.PUT_LINE(v_err_line);
END LOOP;
COMMIT;
END;
--END;
/
EXIT;
======================================
much, much obliged..
Steven