13 Replies Latest reply: Feb 25, 2013 2:48 PM by 993050 RSS

    Bulk Collect

    993050
      Hi,

      I need to create a procedure to update the correct sku value.
      I need to take the order number from a given driven table where the flag is Y and then compare the order number in the main table.Then i need to take the sku value from the main table for the corresponding order number.Now compare the sku value with the from_sku field in the mapping table and update the main table by retrieving the corresponding to_sku value in the mappin table

      Please fnd my below code.now i need to use bulk collect as we will be dealing with huge data.Please let me know how to refine my code

      Declare
      o_error_message VARCHAR2(225);

      BEGIN

      FOR r_allocconv in (select a.sku,A.order_no from ALLOC_HEADER_CONV a,ordloctest l where A.ORDER_NO=L.ORDER_NO and l.flag='Y') LOOP

      For r_mapping in (select M.to_sku,M.FROM_SKU from rm_cd_sku_convtest M,ALLOC_HEADER_CONV A WHERE A.SKU=M.FROM_SKU) loop

           If r_allocconv.sku=r_mapping.FROM_SKU then

           update alloc_header_conv set r_allocconv.sku=C_mapping.To_SKU where order_no=r_allocconv.order_no;

           End if;

           COMMIT;
      END LOOP;

      END LOOP;

      EXCEPTION
      WHEN OTHERS THEN
      o_error_message := SQLERRM || ' from other';
      dbms_output.put_line(o_error_message);
      END;


      Thanks,
      Anu
        • 1. Re: Bulk Collect
          sb92075
          990047 wrote:
          Hi,

          I need to create a procedure to update the correct sku value.
          I need to take the order number from a given driven table where the flag is Y and then compare the order number in the main table.Then i need to take the sku value from the main table for the corresponding order number.Now compare the sku value with the from_sku field in the mapping table and update the main table by retrieving the corresponding to_sku value in the mappin table

          Please fnd my below code.now i need to use bulk collect as we will be dealing with huge data.Please let me know how to refine my code

          Declare
          o_error_message VARCHAR2(225);

          BEGIN

          FOR r_allocconv in (select a.sku,A.order_no from ALLOC_HEADER_CONV a,ordloctest l where A.ORDER_NO=L.ORDER_NO and l.flag='Y') LOOP

          For r_mapping in (select M.to_sku,M.FROM_SKU from rm_cd_sku_convtest M,ALLOC_HEADER_CONV A WHERE A.SKU=M.FROM_SKU) loop

               If r_allocconv.sku=r_mapping.FROM_SKU then

               update alloc_header_conv set r_allocconv.sku=C_mapping.To_SKU where order_no=r_allocconv.order_no;

               End if;

               COMMIT;
          END LOOP;

          END LOOP;

          EXCEPTION
          WHEN OTHERS THEN
          o_error_message := SQLERRM || ' from other';
          dbms_output.put_line(o_error_message);
          END;


          Thanks,
          Anu
          >
          Hi,

          I need to create a procedure to update the correct sku value.
          I need to take the order number from a given driven table where the flag is Y and then compare the order number in the main table.Then i need to take the sku value from the main table for the corresponding order number.Now compare the sku value with the from_sku field in the mapping table and update the main table by retrieving the corresponding to_sku value in the mappin table

          Please fnd my below code.now i need to use bulk collect as we will be dealing with huge data.Please let me know how to refine my code

          Declare
          o_error_message VARCHAR2(225);

          BEGIN

          FOR r_allocconv in (select a.sku,A.order_no from ALLOC_HEADER_CONV a,ordloctest l where A.ORDER_NO=L.ORDER_NO and l.flag='Y') LOOP

          For r_mapping in (select M.to_sku,M.FROM_SKU from rm_cd_sku_convtest M,ALLOC_HEADER_CONV A WHERE A.SKU=M.FROM_SKU) loop

               If r_allocconv.sku=r_mapping.FROM_SKU then

               update alloc_header_conv set r_allocconv.sku=C_mapping.To_SKU where order_no=r_allocconv.order_no;

               End if;

               COMMIT;
          END LOOP;

          END LOOP;

          EXCEPTION
          WHEN OTHERS THEN
          o_error_message := SQLERRM || ' from other';
          dbms_output.put_line(o_error_message);
          END;


          Thanks,
          Anu
          row by row is slow by slow.
          should be done as single SQL statement
          COMMIT inside loop make processing SLOWER & can result in ORA-01555 Snapshot Too Old error

          EXCEPTION handler is a bug waiting to bite unsuspecting user.
          • 2. Re: Bulk Collect
            rp0428
            Welcome to the forum!

            Whenever you post provide your 4 digit Oracle version (result of SELECT * FROM V$VERSION)
            >
            I need to create a procedure to update the correct sku value.
            >
            No - you do NOT need to create a procedure to do that.
            >
            I need to take the order number from a given driven table where the flag is Y and then compare the order number in the main table.Then i need to take the sku value from the main table for the corresponding order number.Now compare the sku value with the from_sku field in the mapping table and update the main table by retrieving the corresponding to_sku value in the mappin table
            >
            Write a query that joins those tables together and does the update. You didn't post your table DDL so we have no idea whay columns you are using or how to join your tables.
            >
            Please fnd my below code.now i need to use bulk collect as we will be dealing with huge data.
            >
            No - you do NOT need to use bulk collect just because you are 'dealing with huge data'. Oracle can perform UPDATE queries on 'huge data' just fine.
            >
            Please let me know how to refine my code
            >
            Your code doesn't need refined. Throw it away and replace it with an UPDATE query.

            If you have any problems with the UPDATE query post the query you tried and we can offer help with it.

            Always use SQL to do the job whenever possible. You haven't posted anything that indicates that SQL cannot do the job you need done.
            • 3. Re: Bulk Collect
              993050
              hi

              thanks,

              i need to call this procedure from a package and i need to create the same for so many tables.

              DECLARE
              L_order_no alloc_header.order_no%type;
              L_sku alloc_header.sku%type;
              P_order_no ordloctest.order_no%type;
              p_flag ordloctest.flag%type;
              m_to_sku rm_cd_sku_convtest.to_sku%type;
              m_from_sku rm_cd_sku_convtest.FROM_sku%type;
              o_error_message VARCHAR2(225);

              cursor C_allocconv is
              select a.sku,A.order_no
              from ALLOC_HEADER a,ordloctest l
              where A.ORDER_NO=L.ORDER_NO
              and l.flag='Y';

              cursor c_mapping is
              select M.to_sku,M.FROM_SKU from rm_cd_sku_convtest M,ALLOC_HEADER A WHERE A.SKU=M.FROM_SKU;

              BEGIN
              FOR r_allocconv in C_allocconv LOOP
              L_sku := r_allocconv.sku;
              l_order_no := r_allocconv.order_no; open C_mapping;
              fetch C_mapping INTO m_to_sku,m_from_sku;
              if C_mapping%NOTFOUND then
              o_error_message := SQLERRM || ' from SQL';
              dbms_output.put_line(o_error_message);
              end if;
              close C_mapping;

              if(l_sku=m_from_sku)then

              update alloc_header_conv set sku = m_to_sku where order_no=L_order_no;
              COMMIT;

              end if;

              END LOOP;
              EXCEPTION
              WHEN OTHERS THEN
              o_error_message := SQLERRM || ' from other';
              dbms_output.put_line(o_error_message);
              END;


              I need to use change the code by using bulk colleck kindly help
              • 4. Re: Bulk Collect
                rp0428
                >
                i need to call this procedure from a package and i need to create the same for so many tables.
                >
                No - you do NOT need to use a procedure. As I said before
                >
                You haven't posted anything that indicates that SQL cannot do the job you need done.
                • 5. Re: Bulk Collect
                  993050
                  if i dont need to use the procedure,do you mean to say i can use this dirctly in my package?
                  • 6. Re: Bulk Collect
                    sb92075
                    How do I ask a question on the forums?
                    SQL and PL/SQL FAQ
                    • 7. Re: Bulk Collect
                      rp0428
                      >
                      if i dont need to use the procedure,do you mean to say i can use this dirctly in my package?
                      >
                      You can't automate what you can't do manually.

                      The first step is to write the UPDATE query that does the job.

                      Once you have that you can determine the next step such as how to execute that query.
                      • 8. Re: Bulk Collect
                        Billy~Verreynne
                        990047 wrote:

                        Please fnd my below code.now i need to use bulk collect as we will be dealing with huge data.
                        The code already uses implicit bulk fetching due to PL/SQL code optimisation. Explicit bulk collection is not going to make it faster. Why not? Because the approach implemented by that code is flawed.

                        The code implements nested cursor loop - which means it is emulating a nested loop join. And doing it poorly. Does not matter how good a programmer you think you are. You cannot code SQL joins in PL/SQL (or Java, etc) as well as the SQL engine can.

                        You are also pulling data from 2 tables into PL/SQL code. And then pushing that data back to the SQL engine to be updated. The data travels from the SQL engine all the way to the PL/SQL engine, and then all the way back again. How is it sensible to send data on such a detour? Surely keeping the data in the SQL engine and processing it there, without detours, will be a lot faster?

                        Then there's this:
                        EXCEPTION
                        WHEN OTHERS THEN
                        o_error_message := SQLERRM || ' from other';
                        dbms_output.put_line(o_error_message);
                        END;
                        Absolute junk. This is not how one use an exception handler.

                        Scrap and trash the code. Then have a look in the Oracle® Database SQL Language Reference for the UPDATE statement. A single update statement can do the join and the update - which means a single cursor will be created to both read (select) and write (update) the data. Without sending the data on a merry roundabout detour that kills performance.

                        Also research on what the purpose of an exception handler is (there are 3 reasons). And until you clearly understand those reasons, do not write an exception handler as you will be doing more damage than good with it.
                        • 9. Re: Bulk Collect
                          dilipkumar10285
                          i think you can try something like this. Personally i have not tested it since you have not given any DDL statements to generate temp data.

                          also try to learn from expert advice. I always learn from their advice and my findings
                          PROCEDURE PROC_UPD_ALL_HDR_CNV AS
                          
                          BEGIN
                          UPDATE 
                          (SELECT A.SKU,R.TO_SKU FROM 
                               ALLOC_HEADER_CONV A,
                               ORDLOCTEST O,
                               RM_CD_SKU_CONVTEST R
                               WHERE 
                                    A.ORDER_NO=O.ORDER_NO 
                                    AND O.FLAG='Y' 
                                    AND R.FROM_SKU = A.SKU)
                          SET SKU = TO_SKU;
                          
                          COMMIT;
                          
                               EXCEPTION
                                    WHEN OTHERS THEN
                                    O_ERROR_MESSAGE := SQLERRM || ' FROM OTHER';
                                    DBMS_OUTPUT.PUT_LINE(O_ERROR_MESSAGE);
                          END;
                          hope this helps.

                          Cheers
                          Dilipkumar
                          • 10. Re: Bulk Collect
                            Stew Ashton
                            dilipkumar vishwakarma wrote:
                            also try to learn from expert advice. I always learn from their advice and my findings
                            That is a very good point.

                            Why don't you follow your own advice? You just posted this code that Billy Verreynne explained is bad.
                             
                                 EXCEPTION
                                      WHEN OTHERS THEN
                                      O_ERROR_MESSAGE := SQLERRM || ' FROM OTHER';
                                      DBMS_OUTPUT.PUT_LINE(O_ERROR_MESSAGE);
                            END;
                            That exception handler is so bad in so many ways.

                            It hides from the calling program that there was an exception.

                            It stops Oracle from doing what Oracle normally does when PL/SQL ends with an exception.

                            It loses information, such as the actual line number where the error occurred.

                            The day you stop writing that code, the world will be a better place. Now is your chance to improve the world!
                            • 11. Re: Bulk Collect
                              993050
                              Hi

                              Thanks,

                              When i run the below code suggested by you ,am getting this error kindly suggest

                              ORA-01779: cannot modify a column which maps to a non key-preserved table FROM
                              OTHER

                              BEGIN
                              UPDATE
                              (SELECT A.SKU,R.TO_SKU FROM
                              ALLOC_HEADER_CONV A,
                              ORDLOCTEST O,
                              RM_CD_SKU_CONVTEST R
                              WHERE
                              A.ORDER_NO=O.ORDER_NO
                              AND O.FLAG='Y'
                              AND R.FROM_SKU = A.SKU)
                              SET SKU = TO_SKU;
                              • 12. Re: Bulk Collect
                                Venkadesh Raja
                                990047 wrote:
                                Hi

                                Thanks,

                                When i run the below code suggested by you ,am getting this error kindly suggest

                                ORA-01779: cannot modify a column which maps to a non key-preserved table FROM
                                OTHER

                                BEGIN
                                UPDATE
                                (SELECT A.SKU,R.TO_SKU FROM
                                ALLOC_HEADER_CONV A,
                                ORDLOCTEST O,
                                RM_CD_SKU_CONVTEST R
                                WHERE
                                A.ORDER_NO=O.ORDER_NO
                                AND O.FLAG='Y'
                                AND R.FROM_SKU = A.SKU)
                                SET SKU = TO_SKU;
                                Inside the SELECT statement JOIN WITH PRIMARY KEY COLUMN
                                • 13. Re: Bulk Collect
                                  993050
                                  Hi,

                                  I have already used the primary key in the select statement,but still the same error.Kindly suggest