3 Replies Latest reply: Aug 26, 2013 12:23 AM by Vite DBA RSS

    Passing Parameter from one cursor to another

    user527356

      Hi All,

       

      I have two cursor. One is for getting dates for one month and other is for doing some calculations based on date. Both these cursor are parameterized.

      When I am trying to get values from them it seems control is not going into second loop (for second cursor). Pls help me to understand the reason.

      Below is the code I am using.

       

      Cursor c_date(b_mydate DATE)
       ----getting dates for month of given date
       Cursor c_cal(cont_id VARCHAR2, b_date DATE, c_code VARCAHR2)
       ---doing SUM of certain columns based on parameters
       
       For c_d1 IN c_date(d_date) LOOP
            d_date := c_d1.dt;
       For r_cal IN c_cal(id, date1, code) LOOP
        IF date1< TRUNC(ld_sysdate) THEN
       sum := sum + r_cal.act_tot;
       ELSEIF ....
       ---- get another value
       ....
       END IF;
       END LOOP;
       END LOOP;
      

       

      I placed dbms_output_put_line in both the loops, it prints value in outer loop but never print anything in inner loop. I am not sure what mistake I am making.

       

      Thanks in advance.

       

      Anil

        • 1. Re: Passing Parameter from one cursor to another
          Vite DBA

          Hi Anil,

           

          one of the strongest recommendations you will hear in these forums is that you should use the power of SQL wherever possible in preference to PL/SQL. That is to say, if something can be done with reasonable ease in a SQL statement, then this is nearly always more preferable to using cursors and looping in PL/SQL. This holds true from both a performance and a maintainability aspect.

           

          In your case, it is hard to tell, as you've not supplied any of the actual cursors or summing logic. It does seem likely though that the two cursors could be combined into one query by joining the date logic with the values tables and then the summation logic could be incorporated through the use of SQL SUM and GROUP BY logic.

           

          Regards

          Andre

          • 2. Re: Passing Parameter from one cursor to another
            Purvesh K


            Lets see what you are missing here:

            1. Oracle version (Output from select * from v$version).

            2. The Exact code that is giving you error. You have missed the cursor definition and the initialization logic for variables id, code, date1.

            3. Your business requirement and the Table definition with test data to reproduce the output.

             

            The cause of the issue may be that the cursor c_cal may not be having data to retireive it. hence, to debug just execute the exact query of c_cal cursor with the variables that are set. You can get the exact value of variables by using dbms_output.

             

            Now, just fixing this is not going to fix the exact issue that you have due to wrong use of code constructs. You are maximising the PL/SQL code and minimising the SQL, which in ideal situation should be exact opposite.

             

            Below may be a suitable replacement for you:

             

            declare

              cursor c_cal is

              select cal_date, sum(act_tot) act_tot

                 from cal_total_table

               where cal_date in (select distinct date_Field from date_table where some_conditions_may_follow);

            begin

              for idx in c_cal loop

                dbms_output.put_line('For Date ' || idx.cal_date || ' Total is ' || idx.act_total);

              end loop;

            end;

             

            As you can see here, the amount of code and the time and efforts spent in looping is reduced to a considerable extent. thus benefitting you in terms of performance and the maintainability of code. If you can supply here the exact code the expected outcome, you might be able to get a better and working version of code but for that you need to supply us with the details missing in your post.

            • 3. Re: Passing Parameter from one cursor to another
              Billy~Verreynne

              As said - your code is not optimal and your approach very much flawed.

               

              There should be a SINGLE SQL statement used - and not one SQL cursor used to drive another.

               

              Not only is this slow performance wise - it is also dangerous consistency wise. Why? Because the truth at the time cursor2 executes, is different than the truth when cursor1 was executed.

               

              Oracle provides consistent reads. This means that a cursor will see (and output) a consistent and correct view of the data. A transaction commits and data changes. This means that the next cursor will now see a new (current) consistent view of the data. The truth can have changed between the two cursors.

               

              Simple example:

              SQL> -- sample table
              SQL> create table tab1(
                2          object_id primary key,
                3          object_type,
                4          object_name
                5  ) organization index
                6  nologging as
                7  select object_id, object_type, object_name from all_objects where rownum < 101;
              
              Table created.
              
              SQL> -- open a consistent read cursor on tab1
              SQL> var c1 refcursor
              SQL> begin
                2          open :c1 for
                3          select object_type, count(*) from tab1 group by object_type;
                4  end;
                5  /
              
              PL/SQL procedure successfully completed.
              
              SQL> -- delete all data from tab1
              SQL> delete from tab1;
              
              100 rows deleted.
              
              SQL> commit;
              
              Commit complete.
              
              SQL> -- no rows left in tab1
              SQL> select count(*) from tab1;
              
                COUNT(*)
              ----------
                       0
              
              SQL> -- instruct the cursor to execute, fetch and display rows
              SQL> -- (it still sees the truth of what was tab1 at the time
              SQL> -- the cursor was created)
              SQL> print c1
              
              OBJECT_TYPE                      COUNT(*)
              ------------------------------ ----------
              EDITION                                 1
              SEQUENCE                                1
              PACKAGE                                 3
              TABLE                                   4
              SYNONYM                                85
              VIEW                                    6
              
              6 rows selected.
              
              SQL>

               

              So when you want consistency in processing, ALWAYS remember that cursor2 (inside the loop) can and likely will see a DIFFERENT version of data than cursor1 (driving the loop).

               

              Which is why using a SINGLE cursor ,and JOINING data sets, is a far faster, and far safer, approach.