3 Replies Latest reply on Jan 17, 2019 10:53 PM by Paulzip

    Updating records of a PL/SQL cursor

    3673816

      Hello forum.

       

      I have a pl/sql cursor on a table.

      As I loop through each record of the cursor, I need to update some columns in that record. I do that. Then I commit.

      When I loop through the next record of the cursor, I need to environment to be aware of the updates I made in the previous iteration of the loop (step above).

       

      My understanding is that opening a cursor is like capturing a snapshot of the data in memory. I can update the record I am looping through itself either via the ROWID or with the CURRENT OF clause, but I don't think my next iteration of the loop will be aware of the change I made in the previous iteration, because it reads the snapshot of the data that was taken when the cursor was first opened (i.e. before I made the update in the previous iteration).

       

      Is there a way to make this work?

       

      Thank you

      Boris

        • 1. Re: Updating records of a PL/SQL cursor
          John Thorton

          3673816 wrote:

           

          Hello forum.

           

          I have a pl/sql cursor on a table.

          As I loop through each record of the cursor, I need to update some columns in that record. I do that. Then I commit.

          When I loop through the next record of the cursor, I need to environment to be aware of the updates I made in the previous iteration of the loop (step above).

           

          My understanding is that opening a cursor is like capturing a snapshot of the data in memory. I can update the record I am looping through itself either via the ROWID or with the CURRENT OF clause, but I don't think my next iteration of the loop will be aware of the change I made in the previous iteration, because it reads the snapshot of the data that was taken when the cursor was first opened (i.e. before I made the update in the previous iteration).

           

          Is there a way to make this work?

           

          Thank you

          Boris

          Your understanding is not correct.

          CURSOR contains NO data.

           

          cursor

          A handle or name for a private SQL area in the PGA. Because cursors are closely associated with private SQL areas, the terms are sometimes used interchangeably.

           

          After COMMIT is issued the changed row data will be presented when any new SELECT statement is issued by any session.

           

          What problem are you really trying to solve?

           

          Please click on URL below & respond accordingly

           

          How do I ask a question on the forums?.

          • 2. Re: Updating records of a PL/SQL cursor
            Frank Kulash

            Hi, Boris,

            3673816 wrote:

             

            Hello forum.

             

            I have a pl/sql cursor on a table.

            As I loop through each record of the cursor, I need to update some columns in that record. I do that. Then I commit.

            When I loop through the next record of the cursor, I need to environment to be aware of the updates I made in the previous iteration of the loop (step above).

             

            My understanding is that opening a cursor is like capturing a snapshot of the data in memory. I can update the record I am looping through itself either via the ROWID or with the CURRENT OF clause, but I don't think my next iteration of the loop will be aware of the change I made in the previous iteration, because it reads the snapshot of the data that was taken when the cursor was first opened (i.e. before I made the update in the previous iteration).

             

            Is there a way to make this work?

             

            Thank you

            Boris

            Whenever you have a problem, please post a little sample data (CREATE TABLE and INSERT statements, relevant columns only) from all tables involved, so that the people who want to help you can re-create the problem and test their ideas.
            Also post the exact results you want from that data, and an explanation of how you get those results from that data, with specific examples.
            It always helps to show what you've tried, that is, the code that seems to come closest to what you want.
            If you're asking about a DML statement, such as UPDATE, then the CREATE TABLE and INSERT statements you post should re-create the tables as they are before the DML, and the results  will be the contents of the changed table(s) when everything is finished.

            Always say which version of Oracle you're using (for example, 12.2.0.1.0).
            See the Forum FAQ: Re: 2. How do I ask a question on the forums?

             

            The problem isn't clear.  Are you saying that, in order to do the UPDATE on row N, you need to know what was done on row N-1?  If so, you can set some variables at the end of the cursor loop, and reference them during the next iteration.

             

            Do you really need PL/SQL for this job?  Pure SQL has several features (analytic functions, recursive WITH clauses, MATCH RECOGNIZE, ...) such that what happens on one row depends on what is found on other rows.

            • 3. Re: Updating records of a PL/SQL cursor
              Paulzip

              3673816 wrote:

               

              [snip..] but I don't think my next iteration of the loop will be aware of the change I made in the previous iteration, because it reads the snapshot of the data that was taken when the cursor was first opened (i.e. before I made the update in the previous iteration).

               

              Is there a way to make this work?

               

              Thank you

              Boris

              Committing in a cursor loop may get you a ORA-01555, Snapshot too old error.  Ideally don't loop / commit, just do things in plain SQL.  If you must chunk execution up, then I'd suggest looking at DBMS_PARALLEL_EXECUTE.  If you really must use a cursor loop, you need an outer loop mechanism. E.g.

               

              declare

                cursor c1(p_rows_to_process in number) is

                  select *

                  from (

                    select rowid as row_id

                    from  my_table

                    where  some_col_done = 'N'  -- or some complicated predicate

                  )

                  where rownum <= p_rows_to_process;

                l_rows_processed pls_integer := 0;

                l_chunk          number := 1000;

              begin

                loop

                  l_rows_processed := 0;

                  for r1 in c1(l_chunk)

                  loop

                    update my_table

                    set    some_col_done = 'Y'

                    where  rowid = r1.row_id;

                    l_rows_processed := l_rows_processed + 1;

                  end loop;

                  commit; --commit chunk

                  dbms_output.put_line('Processed: ' || l_rows_processed);

                  exit when l_rows_processed < l_chunk;

                end loop;

              end;

              /

               

              You could also use bulk collect / limit approach, but I'll leave that to you to investigate.