Re: Newbie to cursors...

Comments embedded.
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...

How can it when you're trying to loop through absolutely no data?

> 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

This immediately returns NO_DATA_FOUND and exits. You never get to the statements which populate the table.

> 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

You would be better off using lrequest in your cursor and populating the table from that, however your BEST course of action would be:

         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";

from a sqlplus session, and avoiding the overkill of PL/SQL altogether.

David Fitzjarrell Received on Thu Jan 04 2007 - 16:32:08 CST