Ajax cascading Select list and ORA-06502

Vin Steele

I have built an Ajax cascading select list based on Denes Kubicek's helpful example (Thank you Denes)

I am using APEX 3.1.2 running on Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

My problem is that when I have a large number of items in the pulldown, I generate an ORA-06502 error. The pulldown is created, and seems to have all the members, but does not sort them as I intended.
The application is a display of State, County, and City, based on the GNIS values provided by US Geological Survey. The case that triggered the problem is that of the County of Chester Pennsylvania, which has 424 Cities and Towns. I have an error capture routine (Thank you Steven Feuerstein) which indicates that the problem occurs in the SYS.OWA_UTIL package.

Here is my code:

DECLARE
state_in VARCHAR2(10);
county_in VARCHAR2(200);
BEGIN
state_in := 'PA'; -- This was hardcoded for the purposes of this example
county_in := 'CHESTER'; -- This was hardcoded for the purposes of this example
OWA_UTIL.mime_header ('text/xml', FALSE);
HTP.p ('Cache-Control: no-cache');
HTP.p ('Pragma: no-cache');
OWA_UTIL.http_header_close;
HTP.prn ('<select>');
HTP.prn ('<option value="' || 1 || '">' || '- No City Selected -'
|| '</option>'
);

FOR c IN (
SELECT DISTINCT
feature_name AS display_value,
feature_name AS return_value
FROM gnis_profile
WHERE gnis_profile.state_alpha = state_in
AND gnis_profile.county = county_in
ORDER BY
feature_name
)
LOOP
HTP.prn ('<option value="' || c.return_value || '">' || c.display_value || '</option>');
END LOOP;

HTP.prn ('</select>');
EXCEPTION
WHEN OTHERS THEN
OHUB.err_pkg.RECORD_AND_CONTINUE();
END;
/

My error capture routine shows this from the stack:
-6502
ORA-06502: PL/SQL: numeric or value error
ORA-06512: at "SYS.OWA_UTIL", line 354
ORA-06512: at "SYS.OWA_UTIL", line 402
ORA-06512: at line 7
14-OCT-08 05.11.01.679912 PM

Any help with this would be gratefully appreciated.

M Tajuddin

Hi Vin Steele ,
I think there is a easy way to accomplish your work. Check out my demo page about cascading list below -

http://apex.oracle.com/pls/otn/f?p=51417:17:1921620372503691:::::

username: demo
password: demo1

Hope this helps,

M Tajuddin
http://tajuddin.whitepagesbd.com

Denes Kubicek

Tajuddin,

I don't think that will make a difference. The question is related to the problem of large select lists and that one will remain regardless of the method you choose to render a select list.

Vin,

In your case, I would go with a popup rather than with a select list. Here, some examples:

http://apex.oracle.com/pls/otn/f?p=31517:165

http://apex.oracle.com/pls/otn/f?p=31517:125

Denes Kubicek
-------------------------------------------------------------------
http://deneskubicek.blogspot.com/
http://www.opal-consulting.de/training
http://apex.oracle.com/pls/otn/f?p=31517:1
-------------------------------------------------------------------

Vin Steele

Thank you Denes.