Re: PL/SQL help needed

As PaulQ indicated, the problem is that your update statement does not refer back to the cursor variable, so lines like:

> WHERE p.policy_number = policy_number

read:

    where column_in_table = column_in_table.

To fix, simply (a) alias the column names in the original cursor, or (b) reference the cursor variable name in the WHERE clause:

> WHERE p.policy_number = policy_number.policy_number
> AND P.policy_date_time = policy_number.policy_date_time;

Before executing the Pl/SQL from SQL*Plus

set serveroutput on size 100000

Then in the loop

    dbms_output.put_line(policy_number.policy_number || ' ' policy_nuber.policy_date_time);

--

Jonathan Lewis
Yet another Oracle-related web site: www.jlcomp.demon.co.uk

James Garrison wrote in message <379a574b.429212902_at_news.idworld.net>...
>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:
>
>Delcare
>
>CURSOR some_policies IS
> SELECT p.policy_number, p.policy_date_time
> FROM policy p, register_cur rc
> WHERE p.policy_number = rc.policy_number
> AND p.renewal_code = 1
> AND rc.status_1 = 5;
>
>**********Running the above select statement from the sql> command
>line correctly returns 2 rows*************
>
>BEGIN
> FOR policy_number IN some_policies LOOP
> UPDATE policy p
> SET non_renewal_reason = 'I'
> WHERE p.policy_number = policy_number
> AND P.policy_date_time = policy_date_time;
> COMMIT;
>
>*********This updates every row in the table!************
>
> DBMS_OUTPUT.PUT_LINE (SQL%ROWCOUNT || ' policies.');
>
>**This gives a row count of every row in the table! It should be 2***
>
> DBMS_OUTPUT.PUT_LINE ('Policy number ' || v_policy_no || ' .');
>
>******How can I map policy number into v_policy_no????*************
>
> END LOOP;
>END;
>
>I have two problems: 1. Why is every row getting returned when I open
>the cursor. 2. How can I display the policy numbers affected by the
>update.
>
>TIA. Please help asap, let me know if you need more detail on the
>problem. The script is much longer than this snippet.
>
Received on Sun Jul 25 1999 - 03:43:47 CDT