Re: Newbie to cursors...

i5ha_at_yahoo.com wrote:
> Hi everyone,
> I'm quite new to Oracle and am trying to do some data warehousing
> tasks. I've written a few cursors (sample shown below) but none of them
> do what they are supposed to do! N I just can't debug them, can anyone
> please help?
>
> --This cursor is supposed to populate my fact table Cover_fact, which
> is initially empty...
> Declare
> Cursor c_fact is
> Select Session_id, Lrequest_id, Practice_id, Locum_id, Time_code,
> Status
> From Cover_fact;
> Begin
> For cover_rec in c_fact LOOP
> INSERT into cover_fact(Session_id, Lrequest_id, Practice_id,
> Locum_id, Time_code, Status)
> SELECT Session_id, Lrequest_id, Practiceid, Locum_id, Tseq.nextval,
> Status
> FROM session2, lrequest
> WHERE session2.Lrequest_id = lrequest."LocumRequestID";
> END LOOP;
> Commit;
> End;
>
> Thaanks

your combination of your for and a select looks equivalent to Session_id, Lrequest_id, Practiceid, Locum_id, Tseq.nextval, Status
FROM session2, lrequest, cover_fact
assuming session_id etc refer to cover_fact.

This results in 3 remarks
- session_id etc are elements in cover_rec. They should have read cover_rec.session_id, cover_rec.practice_id etc - should you always do everything you can do in sql, do in sql. You now have a combination of Pl/sql and SQL, where 1 sql statement is sufficient. Your code is BAD and won't scale at all. - Your code boils down to a so-called CARTESIAN PRODUCT (if one select returns n rows and a second select returns m rows, if you combine them without a proper where clause, you will get n x m rows) This is EVEN WORSE!!! I would seriously reconsider this code, provided you don't want to be swamped by severe performance problems.

-- 
Sybrand Bakker
Senior Oracle DBA