Re: extracting records using a cursor within a cursor

swoop wrote:
> Hi,
>
> I have managed to correct my error on the SQL statement to get it
> working, so thanks very much for your help. It has updated my TGBPAYM
> table wit the required records.
>
> However, I have been asked to do it using cursors as I will need to add
> more to it later as this is just the start. If someone could
> point/guide me as to where I have gone wrong with it and would really
> appreciate it.
>
> I have made some changes to it but not sure if it's the right
> direction.
>
> Cheers.
>
> DECLARE
>
> CURSOR tgb_cur IS
>
> SELECT cmpy, supp, entry_date, sysref
> FROM tgbtran
> WHERE tran_type = 1
> AND ordr_rltd_flag = 'Y'
> AND yy = 2005;
>
> v_cmpy tgbtran.cmpy%type;
> v_supp tgbtran.supp%type;
> v_entry_date tgbtran.entry_date%type;
> v_sysref tgbtran.sysref%type;
>
> v_py_cmpy tgbpaym.cmpy%type;
> v_py_supp tgbpaym.supp%type;
> v_py_tran_entry_date tgbpaym.tran_entry_date%type;
> v_py_tran_sysref tgbpaym.tran_sysref%type;
>
> CURSOR tgbp_cur IS
>
> SELECT cmpy, supp, tran_entry_date, tran_sysref
> FROM tgbpaym
> WHERE v_cmpy <> v_py_cmpy
> AND v_supp <> v_py_supp
> AND v_entry_date <> v_py_tran_entry_date
> AND v_sysref <> v_py_tran_sysref;
>
> BEGIN
>
> OPEN tgb_cur;
> LOOP
> FETCH tgb_cur INTO v_cmpy,
> v_supp,
> v_entry_date,
> v_sysref;
> EXIT WHEN tgb_cur%notfound;
>
>
> OPEN tgbp_cur;
> FETCH tgbp_cur INTO v_py_cmpy,
> v_py_supp,
> v_py_tran_entry_date,
> v_py_tran_sysref;
>
> INSERT INTO utgbtran
> VALUES (v_cmpy, v_supp, v_entry_date, v_sysref);
>
> CLOSE tgbp_cur;
> END LOOP;
> CLOSE tgb_cur;
> END;
> /

You went wrong when you listened to whoever told you to use a cursor. This person needs to upgrade their Oracle skills.

Always use DML where possible and until there is a requirement for something that can't be done with simple DML using a cursor is not best practice.

But even if simple DML will not work ... the solution is bulk binding with BULK COLLECT and FORALL. There is no valid reason to open a cursor loop in a situation such as your sample.

-- 
Daniel A. Morgan
Relational theory is not something that is simply a nice-to-have.
http://www.psoug.org
damorgan_at_x.washington.edu
(replace x with u to respond)