Re: Cursors using SQL plus

From: Galen Boyer <galenboyer_at_hotpop.com>
Date: 16 Apr 2002 23:39:17 -0500
Message-ID: <u3cxuzs64.fsf@rcn.com>

On 16 Apr 2002, h_tompson_at_hotmail.com wrote:
> Hi all,
>
> I have really quick question.
>
> I'm debuging a hugh cursor in a piece of badly written code and
> what I am finding is that I'm spending too much time compiling
> the program and running it.
>
> What I want to do is to be able to run the cursor from the sql plus
> window, and see that data that comes out line by line.

There are debugging environments. I think Toad has a pretty nice one.

The other thing to do is put some
dbms_sql.put_line('Some Description ', variable-to-show) lines in strategic places and see how things are getting set.

Since you want to do this with sqlplus, make sure to "set serveroutput on".

> Is this possible? How?
>
> Also, since I have your attention:
> In the same cursor I want to lock the selected data using a FOR UPDATE
> OF clause. My documentation tells me this isn't allowed with cursors
> (we're running oracle 8.0.5 on HP-UX) but is there any other way?

Consider:

        SQL> select * from t1;

              FLD1
        ----------
                 1
                 2
                 3

        SQL> DECLARE
             cursor x is select fld1 from t1 for update;
             y number;
             BEGIN
             OPEN x;
             LOOP
                FETCH x into y;
                EXIT WHEN x%NOTFOUND;
                update t1 set fld1 = y + 100 where current of x;
             END LOOP;
             END;
        /    

        PL/SQL procedure successfully completed.

        SQL> select * from t1;

              FLD1
        ----------
               101
               102
               103

        Elapsed: 00:00:00.90

Hm... Seemed like the "for update" works for cursors.

So, just to make sure, I truncated and then inserted 1/2MIL rows into table t1. Then I ran the same PL/SQL except setting to "y + 1000000". (This took a couple of minutes) But, immediately after starting the PL/SQL, I ran "update t1 set fld1 = fld1 + 1000000 where fld1 = 10000;

This update executed and I was able to query for the row "where fld1 = 110000" while the PL/SQL was executing. Hm... Seems like you might be correct. Lets keep going.

I also ran an update "where fld1 = 1". This one waited until the loop finished.

So, I did some reading
http://technet.oracle.com/doc/oracle8i_816/appdev.816/a76939/adg08sql.htm#8123

The thing that seems quite relevant here is the snippet of: ,----

| If a SELECT... FOR UPDATE statement is used when defining a cursor, then
| the rows in the return set are locked before the first fetch, when the
| cursor is opened; rows are not individually locked as they are fetched
| from the cursor.

`----

So, I executed the following:

        SQL> insert into t1 select rownum from merchandise_hierarchy_tbl where rownum < 50000;

        SQL> commit;

        SQL> DECLARE
             cursor x is select fld1 from t1 for update;
             y number;
             BEGIN
             OPEN x;
             CLOSE x;
             END;
        /    

I found this took about 3 seconds.  So, then I executed
        SQL> DECLARE
             cursor x is select fld1 from t1 for update;
             y number;
             BEGIN
             OPEN x;
             LOOP
                FETCH x into y;
                EXIT WHEN x%NOTFOUND;
                update t1 set fld1 = y + 1000000 where current of x;
             END LOOP;
             END;
        /    

In another sqlplus session, I waited beyond 3 seconds after the kickoff of the above PL/SQL and then executed this:

        SQL> update t1 set fld1 = fld1 + 50000 where fld1 = 50000;

I found that this update now waited until the PL/SQL finished.

So, it seems that the "cursor x is select fld1 from t1 for update" line takes a little time to find all the rows, but once it does, it locks them for update.

-- 
Galen deForest Boyer
Sweet dreams and flying machines in pieces on the ground.

Received on Tue Apr 16 2002 - 23:39:17 CDT