Handling the ROWTYPE_MISMATCH exception

You can trap the ROWTYPE_MISMATCH exception and then attempt to FETCH from the cursor variable using a different INTO clause. But even though you are executing the second FETCH statement in your program, you will still retrieve the first row in the result set of the cursor object's query. This functionality comes in handy for weak REF CURSOR types.

In the following example, a centralized real estate database stores information about properties in a variety of tables: one for homes, another for commercial properties, and so on. There is also a single, central table that stores addresses and building types (home, commercial, etc.). I use a single procedure to open a weak REF CURSOR variable for the appropriate table based on the street address. Each individual real estate office can then call that procedure to scan through the matching properties. Here are the steps:

1. Define my weak REF CURSOR type:

TYPE building_curtype IS REF CURSOR;

2. Create the procedure. Notice that the mode of the cursor variable parameter is IN OUT:

3. PROCEDURE open_site_list 4. (address_in IN VARCHAR2, 5. site_cur_inout IN OUT building_curtype)6. IS7. home_type CONSTANT INTEGER := 1;8. commercial_type CONSTANT INTEGER := 2;9. 10. /* A static cursor to get building type. */11. CURSOR site_type_cur IS12. SELECT site_type FROM property_master13. WHERE address = address_in;14. site_type_rec site_type_cur%ROWTYPE;15. 16. BEGIN17. /* Get the building type for this address. */18. OPEN site_type_cur;19. FETCH site_type_cur INTO site_type_rec;20. CLOSE site_type_cur;21. 22. /* Now use the site type to select from the right table.*/23. IF site_type_rec.site_type = home_type24. THEN25. /* Use the home properties table. */26. OPEN site_cur_inout FOR 27. SELECT * FROM home_properties28. WHERE address LIKE '%' || address_in || '%';29. 30. ELSIF site_type_rec.site_type = commercial_type31. THEN32. /* Use the commercial properties table. */33. OPEN site_cur_inout FOR 34. SELECT * FROM commercial_properties35. WHERE address LIKE '%' || address_in || '%';36. END IF;END open_site_list;

37. Now that I have my open procedure, I can use it to scan properties.

In the following example, I pass in the address and then try to fetch from the cursor, assuming a home property. If the address actually identifies a commercial property, PL/SQL will raise the ROWTYPE_MISMATCH exception on account of the incompatible record structures. The exception section then fetches again, this time into a commercial building record, and the scan is complete.

DECLARE /* Declare a cursor variable. */ building_curvar building_curtype; /* Define record structures for two different tables. */ home_rec home_properties%ROWTYPE; commercial_rec commercial_properties%ROWTYPE;BEGIN /* Get the address from the user. */ prompt_for_address (address_string); /* Assign a query to the cursor variable based on the address. */ open_site_list (address_string, building_curvar); /* Give it a try! Fetch a row into the home record. */ FETCH building_curvar INTO home_rec; /* If I got here, the site was a home, so display it. */ show_home_site (home_rec);EXCEPTION /* If the first record was not a home... */ WHEN ROWTYPE_MISMATCH THEN /* Fetch that same 1st row into the commercial record. */ FETCH building_curvar INTO commercial_rec; /* Show the commercial site info. */ show_commercial_site (commercial_rec);END;