Re: Procedure not populating data

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

To start with you are opening the cursor inside of the loop. Look at the examples at www.psoug.org, Morgan's Library, Cursors & Cursor Loops.

If you read your code you go into the loop ... open the cursor ... do some stuff ... loop back to the top and try to open the cursor again. It is not going to work.

Secondly if you are at 9i or later you should not be using cursor loops but rather array processing with bulk collect and FORALL. See examples under bulk binding in the library.

What you likely should do here is bulk collect then examine each record and call a sub-routine that uses static SQL, not a cursor, to do the real work.

[Quoted] Also, I'd suggest you drop dbms_output and load your errors into a table.

-- 
Daniel A. Morgan
http://www.psoug.org
damorgan_at_x.washington.edu
(replace x with u to respond)