Re: Procedure not populating data

karen2000 schrieb:
> 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;
> /
>

If your land_id_test table has more than one row, you should get ORA-01422 just by the first executed statement in your procedure. Is only an assumption, you didn't post any error messages and didn't state whether you had any.

Best regards

Maxim Received on Wed Nov 30 2005 - 19:01:35 CET