Re: Newbie to cursors...

Thanks for the advice, things seem a bit clearer now. I do have to use a cursor for homework(u guessed right!) n my column names are funny because I exported my tables from Access to Oracle 10g. Anyway I'll try to sort these out.

Regards,
Manisha

Niall Litchfield wrote:
> 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
Received on Fri Jan 05 2007 - 03:24:12 CST