Home » SQL & PL/SQL » SQL & PL/SQL » PLSQL script just hanging - please tell me why.. (Oracle Financials 11i, Sun Solaris platform, ORACLE RDBMS : 10.1)
PLSQL script just hanging - please tell me why.. [message #383798] Fri, 30 January 2009 05:20 Go to next message
MrDuke
Messages: 41
Registered: July 2007
Location: Coventry, UK
Member
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

[EDITED by LF: applied [code] tags]

[Updated on: Fri, 30 January 2009 05:56] by Moderator

Report message to a moderator

Re: PLSQL script just hanging - please tell me why.. [message #383799 is a reply to message #383798] Fri, 30 January 2009 05:30 Go to previous messageGo to next message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
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; 


Now identify the cause why it is "hanging".

Regards

Raj
Re: PLSQL script just hanging - please tell me why.. [message #383800 is a reply to message #383798] Fri, 30 January 2009 05:36 Go to previous messageGo to next message
cookiemonster
Messages: 12422
Registered: September 2008
Location: Rainy Manchester
Senior Member
As I told you last time - if you're going to post that much code please use CODE TAGS!
What do you think the first loop is doing?
Re: PLSQL script just hanging - please tell me why.. [message #383801 is a reply to message #383798] Fri, 30 January 2009 05:38 Go to previous messageGo to next message
cookiemonster
Messages: 12422
Registered: September 2008
Location: Rainy Manchester
Senior Member
Beaten to it.

See how much more readable the formatted version is?
Re: PLSQL script just hanging - please tell me why.. [message #383807 is a reply to message #383801] Fri, 30 January 2009 06:07 Go to previous messageGo to next message
Littlefoot
Messages: 20901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
As of formatting issues: MrDuke's code was properly formatted (note /* Formatted on 2009/01/30 11:11 (Formatter Plus v4.8.7) */), but he didn't enclose it into the [code] tags. Perhaps instructions in The Guide aren't clear enough; we'll see how to improve them.
Re: PLSQL script just hanging - please tell me why.. [message #383808 is a reply to message #383798] Fri, 30 January 2009 06:09 Go to previous message
MrDuke
Messages: 41
Registered: July 2007
Location: Coventry, UK
Member
Code in them wiggly brackets - noted for next time. Apologies..

Thanks people for your swift responses - it was the outer loop - completely unecessary... Removed it and my records loaded in quicker than Woolies members in a dole queue..

cheers, Steven
Previous Topic: Forms
Next Topic: executing ORADEBUG inside stored procedure
Goto Forum:
  


Current Time: Fri Dec 09 14:00:47 CST 2016

Total time taken to generate the page: 0.20942 seconds