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?

I'll have a try

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

declare a cursor variable to hold rows selected from cover_fact.

> Begin
> For cover_rec in c_fact LOOP

no rows in this table, so the loop never happens since the cursor is empty

> 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;
In your example the straight sql statement would do fine. I'd also have avoided the case sensitive column name "LocumRequestID" when defining the table - I bet an "easy to use GUI" like TOAD did this for you.

Cursors are used to hold sets of records for subsequent operation. They aren't used as a target for something to be assigned to (I think that is why you think you need them).

If you had to use cursors - say for homework - then I'd declare a cursor based on the second select statement and process that. The straight insert...select will be faster and clearer to read though.

-- 
Niall Litchfield
Oracle DBA
http://www.orawin.info/services