Re: PL/SQL help needed

From: Kenneth C Stahl <BluesSax_at_Unforgettable.com>
Date: Tue, 27 Jul 1999 11:01:11 -0400
Message-ID: <379DC9B7.8A652258@Unforgettable.com>

James Garrison wrote:

> In my declare section I've defined a cursor. If I cut and paste the
> select statement into SQLPLUS it returns the correct number of rows.
> When I run my script opening the cursor with a FOR..IN..LOOP it
> returns every row in the table. I'm trying to use
> DBMS_OUTPUT.PUT_LINE statements to diagnose the error but am unable to
> get it to work correctly. Here's an example:
>
>

Yee gads, how did you ever come up with this code?

If you want to update specific rows, do it like this:

Declare
CURSOR some_policies IS
SELECT p.policy_number,

               p.policy_date_time
               p.rowid
FROM   policy p,
              register_cur rc

WHERE p.policy_number = rc.policy_number AND p.renewal_code = 1
AND rc.status_1 = 5;

BEGIN
    FOR I IN some_policies LOOP

        UPDATE policy
        SET    non_renewal_reason = 'I'
        WHERE rowid = i.rowid;
        COMMIT;

      DBMS_OUTPUT.PUT_LINE (SQL%ROWCOUNT || ' policies.');
      DBMS_OUTPUT.PUT_LINE ('Policy number ' || v_policy_no || ' .');
                END LOOP;

END; Ken Received on Tue Jul 27 1999 - 10:01:11 CDT