Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

Troubleshooting
Research
Professional Opinions
Ask a Question
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

troubleshooting Question

ORA-01555 caused by SQL statement below "SELECT .... FROM ...."

Avatar of hc2342uhxx3vw36x96hq
hc2342uhxx3vw36x96hqFlag for United States of America asked on
Oracle Database
17 Comments1 Solution3605 ViewsLast Modified:
Please suppose that the cursor read_data_to_copy work on OVER 2 million records.
Often, but not always, I obtain the following error:
ORA-01555 caused by SQL statement below
SELECT alpha, beta, gamma FROM source_table_1;
Please help me to optimize the procedure in order to avoid that Oracle error.
PROCEDURE replicate_table
IS
   auxiliary_variable      NUMBER;
   count_dest_table        NUMBER;
 
   CURSOR read_data_to_copy
   IS
      SELECT alpha, beta, gamma
        FROM source_table_1;
 
   read_data_to_copy_rec   read_data_to_copy%ROWTYPE;
BEGIN
   count_dest_table := 0;
 
   FOR read_data_to_copy_rec IN read_data_to_copy
   LOOP
      BEGIN
         auxiliary_variable := NULL;
 
         SELECT alpha
           INTO auxiliary_variable
           FROM source_table_2
          WHERE epsilon = read_data_to_copy_rec.alpha;
 
         INSERT INTO destination_table
              VALUES (auxiliary_variable, read_data_to_copy_rec.beta,
                      read_data_to_copy_rec.gamma);
 
         count_dest_table := count_dest_table + 1;
         COMMIT;
      EXCEPTION
         WHEN OTHERS
         THEN
            ROLLBACK;
      END;
   END LOOP;
END replicate_table;