Re: Using Pl/SQL temp table

The use of X in this case is correct, if I isolate the code and just perform the creation and populating of the PL/SQl table - it does work and it inserted the amount of records = the amount of records in emp. So x is being incremented correctly.

But somehow, as you - Ooo hell, never mind.. I just redid the whole thing putting debugging line to find out where the program crashes, what you said triggered something Andy and I found out the I have set the dept_job(x) incorrectly, it's suppose to be dept_job(k) ... following the increment of the loop itself. DOH!! Heheh

Many thanks.!!!
Arthur

On Sat, 27 Jan 2001 19:53:20 +0000, Andy Hardy <newsjan01_at_ahardy.demon.co.uk> wrote:

>In article <3a72fcfb.34527777_at_news.iinet.net.au>, Arthur
><mechhunter_at_rocketmail.com> writes
>
>Your use of the X is a little inconsistent:
>
>> 7 CURSOR emp_cur IS
>> 8 SELECT job, deptno
>> 9 FROM emp
>> 10 ORDER BY deptno;
>> 11
>> 12 x NUMBER := 0;
>
>Starts at zero, but loop assumes that it starts at 1
>
>> 22
>> 23 FOR k IN 1..x LOOP
>> 24 IF v_char = dept_job(x) THEN EXIT;
>> 25 ELSIF
>> 26 (v_char != dept_job(x) and k >= x-1) THEN
>> 27 RAISE_APPLICATION_ERROR(-20500, 'Not a valid
>>job for this department.');
>> 28 END IF;
>> 29 END LOOP;
>> 30 END chk_dept_job;
>> 31 --END PROCEDURE
>> 32
>> 33 BEGIN
>> 34 FOR emp_rec IN emp_cur LOOP
>> 35 dept_job(x) := to_char(emp_rec.deptno)||(emp_rec.job);
>> 36 x:= x+1;
>> 37 END LOOP;
>> 38
>> 39* END chk_pack;
>
>Maybe you have only one entry in the emp table? If so, it's value would
>be stored in dept_job(0), but the loop would look at dept_job(1) which
>does not exist?
>
>Andy
>--
>Andy Hardy. PGP ID: 0xA62A4849
>===============================================================
Received on Sat Jan 27 2001 - 22:21:09 CST