Re: Newbie to cursors...

i5ha_at_yahoo.com schrieb:
> 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
>

Cursors don't populate anything, they are just pointers to the result set. You can populate a table by means of INSERT/MERGE statements, they can be done within the LOOP, LOOP means an iteration, usually iteration will be done either by changing a LOOP counter or until some condition will be satisfied. CURSOR LOOP is a kind of LOOP where iterated will be within the result set, the number of iterations equals the number of rows in the result set in this case. If you define your CURSOR as SELECT   FROM table which is initially empty, the number of rows in this result set is zero, hence the number of iterations is the same, hence no of operators within the LOOP will be executed, hence no tables will be populated.
BTW, there are some things which you may take in considerations: 1) Using of plsql for tasks which can be easily done with sql is bad practice
2) Case sensitive column names is bad practice 3) Not reading free accessible and in most cases very good documentation at http://tahiti.oracle.com/ is bad practice

Best regards

Maxim Received on Thu Jan 04 2007 - 15:31:34 CST