Using Pl/SQL temp table

From: Arthur <mechhunter_at_rocketmail.com>
Date: Sat, 27 Jan 2001 16:57:16 GMT
Message-ID: <3a72fcfb.34527777@news.iinet.net.au>

I am using package (the main package is ok) to try to insert a list of data into a temp table and then checking the validity of a query based on the temp table's data.
The temp table works and is create when I use the temp table code seperatly. However, when I execute the procedure, it is coming up with no data found, like the temp table is not getting created BEFORE the procedure is executed. I don't understand since I am following it BY THE BOOK and well, the manual says this is the way to code it and all. Can someone please enlighten me ? =o(

  1 CREATE OR REPLACE PACKAGE BODY chk_pack IS   2
  3 TYPE dept_job_temp IS TABLE OF VARCHAR2(50)   4 INDEX by BINARY_INTEGER;
  5 dept_job dept_job_temp;
  6
  7 CURSOR emp_cur IS
  8 SELECT job, deptno
  9 FROM emp
 10 ORDER BY deptno;
 11
 12 x NUMBER := 0;
 13
 14 --START PROCEDURE CHK_DEPT_JOB

 15     PROCEDURE chk_dept_job(v_deptno IN emp.deptno%TYPE,
 16             v_job   IN emp.job%TYPE)
 17             IS
 18             v_char  VARCHAR2(50);
 19
 20     BEGIN
 21             v_char := to_char(v_deptno)||v_job;
 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;

execute chk_pack.chk_dept_job(20,'CLERK')

ORA-01403: no data found
ORA-06512: at "SCOTT.CHK_PACK", line 24
ORA-06512: at line 1

Many thanks.
Arthur Received on Sat Jan 27 2001 - 10:57:16 CST