Home » SQL & PL/SQL » SQL & PL/SQL » PLSQL script not collecting temp table fields - variables problem? (Oracle Financials 11i, Sun Solaris platform, ORACLE RDBMS : 10.1)
PLSQL script not collecting temp table fields - variables problem? [message #383571] Thu, 29 January 2009 03:31 Go to next message
MrDuke
Messages: 41
Registered: July 2007
Location: Coventry, UK
Member
I've 'written' a script to extract data from a temp table and load it directly into the associated Oracle tables via HRMS's
API packages.. but when I put the DBMS_OUTPUT.PUT_LINE's in I see that although it seems to read first API OK it doesn't collect the information from their relevant fields in the temp table.. Can anyone help please please..?

======================== code ==========================
SET serveroutput ON SIZE 1000000
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_*** 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 per_all_people_f.person_id%TYPE;
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;
l_assign_payroll_warning BOOLEAN;
l_address_id NUMBER;
l_object_version_number NUMBER;
return_code NUMBER;
return_message VARCHAR2 (2000);
command_prin VARCHAR2 (4000);

-- ***********************************
-- 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_***, 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
-- *****************************************
-- v_err_seq := 1;
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.***, 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 per.person_id = padd.person_id;

emp_rec c_check_employee%ROWTYPE;
BEGIN
--v_err_seq := 2;
command_prin := SQLERRM;

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
-- RETURN 'Employee does not exist, continue import..';
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;

-- v_err_seq := 3;

-- ***********************************
-- Create new PER_ALL_PEOPLE_F and PER_ADDRESSES record from
-- info in table record
-- ***********************************
IF insert_flag = 'I'
THEN
BEGIN -- Importing Employee Procedure --
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, --FALSE,
p_hire_date => p_hire_date,
p_business_group_id => p_business_group_id,
p_date_of_birth => p_date_of_birth,
p_email_address => p_email_address,
p_first_name => p_first_name,
p_middle_names => p_middle_names,
p_last_name => p_last_name,
p_*** => p_***,
p_ni_number => p_national_identifier,
p_employee_number => l_employee_number,
p_person_id => l_person_id,
p_title => 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 (' ');
DBMS_OUTPUT.PUT_LINE (' ');
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.PUT_LINE ('..SQLCodeErrors:- ' || SQLCODE);
DBMS_OUTPUT.PUT_LINE (' ');
DBMS_OUTPUT.PUT_LINE ('Person ID:-' || p_person_id || l_person_id);
DBMS_OUTPUT.PUT_LINE ('Assignmnt Seq - '|| l_assignment_sequence);
DBMS_OUTPUT.PUT_LINE ('l_***_no - ' ||l_assignment_number);

-- DBMS_OUTPUT.PUT_LINE ('Record failed to load.. ' || SQLERRM);
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 => 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 => p_hire_date,
p_date_from => SYSDATE,
p_date_to => NULL,
p_address_type => NULL,
p_comments => NULL,
p_address_line1 => p_address_line1,
p_address_line2 => p_address_line2,
p_address_line3 => p_address_line3,
p_town_or_city => p_address_line4,
p_region_1 => NULL,
p_region_2 => NULL,
p_region_3 => NULL,
p_postal_code => p_post_code,
p_country => 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;
-- v_err_seq := 4;

-- ******************************
-- End of customer related details
-- ******************************

-- ******************************
END IF;
END LOOP;
-- DBMS_OUTPUT.PUT_LINE ('Records read : ' || v_rec_cnt);
-- v_err_seq := 5;

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

======================================================

many thanks to all...

Steven
Re: PLSQL script not collecting temp table fields - variables problem? [message #383574 is a reply to message #383571] Thu, 29 January 2009 03:39 Go to previous messageGo to next message
cookiemonster
Messages: 12406
Registered: September 2008
Location: Rainy Manchester
Senior Member
If you're going to post that much code can you please wrap it in code tags to make it easier to read - the orafaq forum guide tells you how.
Can you copy and paste an execution of this from sqlplus so we can see for our selves what the dbms_ouput's say.
Re: PLSQL script not collecting temp table fields - variables problem? [message #383575 is a reply to message #383571] Thu, 29 January 2009 03:42 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
If you had formatted you PL/SQL code and applied code tags, it would have looked like this:
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_xxx                         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                   per_all_people_f.person_id%TYPE;
   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;
   l_assign_payroll_warning      BOOLEAN;
   l_address_id                  NUMBER;
   l_object_version_number       NUMBER;
   return_code                   NUMBER;
   return_message                VARCHAR2 (2000);
   command_prin                  VARCHAR2 (4000);

-- ***********************************
-- 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_xxx, 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
-- *****************************************
-- v_err_seq := 1;
   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.xxx, 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    per.person_id = padd.person_id;

   emp_rec                       c_check_employee%ROWTYPE;
BEGIN
--v_err_seq := 2;
   command_prin := SQLERRM;

   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
-- RETURN 'Employee does not exist, continue import..';
            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;

-- v_err_seq := 3;

         -- ***********************************
-- Create new PER_ALL_PEOPLE_F and PER_ADDRESSES record from
-- info in table record
-- ***********************************
         IF insert_flag = 'I'
         THEN
            BEGIN                          -- Importing Employee Procedure --
               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, --FALSE,
                      p_hire_date                      => p_hire_date,
                      p_business_group_id              => p_business_group_id,
                      p_date_of_birth                  => p_date_of_birth,
                      p_email_address                  => p_email_address,
                      p_first_name                     => p_first_name,
                      p_middle_names                   => p_middle_names,
                      p_last_name                      => p_last_name,
                      p_xxx                            => p_xxx,
                      p_ni_number                      => p_national_identifier,
                      p_employee_number                => l_employee_number,
                      p_person_id                      => l_person_id,
                      p_title                          => 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 (' ');
                  DBMS_OUTPUT.put_line (' ');
               EXCEPTION
                  WHEN OTHERS
                  THEN
                     DBMS_OUTPUT.put_line ('..SQLCodeErrors:- ' || SQLCODE);
                     DBMS_OUTPUT.put_line (' ');
                     DBMS_OUTPUT.put_line (   'Person ID:-'
                                           || p_person_id
                                           || l_person_id
                                          );
                     DBMS_OUTPUT.put_line (   'Assignmnt Seq - '
                                           || l_assignment_sequence
                                          );
                     DBMS_OUTPUT.put_line ('l_***_no - '
                                           || l_assignment_number
                                          );
-- DBMS_OUTPUT.PUT_LINE ('Record failed to load.. ' || SQLERRM);
                     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 => 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 => p_hire_date,
                        p_date_from                    => SYSDATE,
                        p_date_to                      => NULL,
                        p_address_type                 => NULL,
                        p_comments                     => NULL,
                        p_address_line1                => p_address_line1,
                        p_address_line2                => p_address_line2,
                        p_address_line3                => p_address_line3,
                        p_town_or_city                 => p_address_line4,
                        p_region_1                     => NULL,
                        p_region_2                     => NULL,
                        p_region_3                     => NULL,
                        p_postal_code                  => p_post_code,
                        p_country                      => 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;
-- v_err_seq := 4;

         -- ******************************
-- End of customer related details
-- ******************************

         -- ******************************
         END IF;
      END LOOP;
-- DBMS_OUTPUT.PUT_LINE ('Records read : ' || v_rec_cnt);
-- v_err_seq := 5;

   --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;
/
It doesn't collect the information from the temp table, you say. Are we talking about an Oracle global temporary table, a table with volatile data? Is it possible that the data is transaction based: on commit delete? Or are you accessing the table from another session?

MHE
Re: PLSQL script not collecting temp table fields - variables problem? [message #383600 is a reply to message #383575] Thu, 29 January 2009 04:49 Go to previous messageGo to next message
MrDuke
Messages: 41
Registered: July 2007
Location: Coventry, UK
Member
Ive just sussed it - I had'nt put the 'v_emp' at the front of the fields from the temp table to pick then up! we continue..
Re: PLSQL script not collecting temp table fields - variables problem? [message #383611 is a reply to message #383600] Thu, 29 January 2009 05:31 Go to previous message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
Thanks for the feedback!

MHE
Previous Topic: Bulk Binding Query (merged)
Next Topic: group the data on weeks starting from sunday
Goto Forum:
  


Current Time: Tue Dec 06 04:36:07 CST 2016

Total time taken to generate the page: 0.08244 seconds