Procedure not populating data

From: karen2000 <karen_stone_at_mail.com>
Date: 29 Nov 2005 16:46:58 -0800
Message-ID: <1133311618.182303.296810_at_g49g2000cwa.googlegroups.com>

[Quoted] [Quoted] Can someone please tell me why this procedure is not populating the Land_Id_Number in the Undeveloped_land and Lots table as expected.

Thank you in advance.

CREATE OR REPLACE PROCEDURE populate_land_id IS

   new_volume_no VARCHAR2 (10);

   new_folio_no     VARCHAR2 (10);
   ul_volume_no     undeveloped_land.volume_no%TYPE;
   ul_folio_no      undeveloped_land.folio_no%TYPE;

   lots_volume_no lots.volume_no%TYPE;    lots_folio_no lots.folio_no%TYPE;

   ul_land_id       VARCHAR2 (10);
   lots_land_id     VARCHAR2 (10);

• What value for LAND_ID CURSOR c_new_ul_land_id (p_volume_no VARCHAR2, p_folio_no VARCHAR2) IS SELECT s.land_id FROM land_id_test s WHERE (p_volume_no = new_volume_no AND p_folio_no = new_folio_no); CURSOR c_new_lots_land_id (p_volume_no VARCHAR2, p_folio_no VARCHAR2) IS SELECT s.land_id FROM land_id_test s WHERE (p_volume_no = new_volume_no AND p_folio_no = new_folio_no); BEGIN
• separate CT into volume_no and folio_no SELECT SUBSTR (cert_title, 1, INSTR (cert_title, '/') - 1) volume_no, RTRIM (SUBSTR (cert_title, INSTR (cert_title, '/') + 1)) folio_no INTO new_volume_no, new_folio_no FROM land_id_test;

   --insert land_id_number into the UNDEVELOPED_LAND and LOTS tables in LC schema

   WHILE (new_volume_no IS NOT NULL AND new_folio_no IS NOT NULL)    LOOP

      IF (new_volume_no = ul_volume_no AND new_folio_no = ul_folio_no)
      THEN
         OPEN c_new_ul_land_id (ul_volume_no, ul_folio_no);

         FETCH c_new_ul_land_id
          INTO ul_land_id;

         EXIT WHEN c_new_ul_land_id%NOTFOUND;

         INSERT INTO undeveloped_land
                     (land_id_number
                     )
              VALUES (ul_land_id
                     );
      ELSIF (new_volume_no = lots_volume_no AND new_folio_no =
lots_folio_no)
      THEN
         OPEN c_new_lots_land_id (lots_volume_no, lots_folio_no);

         FETCH c_new_lots_land_id
          INTO lots_land_id;

         EXIT WHEN c_new_lots_land_id%NOTFOUND;

         INSERT INTO lots
                     (land_id_number
                     )
              VALUES (lots_land_id
                     );
      END IF;

   END LOOP;    CLOSE c_new_ul_land_id;

   CLOSE c_new_lots_land_id;

   COMMIT;
EXCEPTION
   WHEN OTHERS
   THEN

      DBMS_OUTPUT.put_line ('error - ' || SQLERRM);
      ROLLBACK;

END;
/ Received on Wed Nov 30 2005 - 01:46:58 CET