Forum Stats

  • 3,782,441 Users
  • 2,254,645 Discussions
  • 7,880,078 Comments

Discussions

Ajax cascading Select list and ORA-06502

Vin Steele
Vin Steele Member Posts: 91
edited Oct 20, 2008 1:44PM in APEX Discussions
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.
Tagged:

Answers

  • M Tajuddin
    M Tajuddin Member Posts: 505 Bronze Badge
    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
    Denes Kubicek Member Posts: 6,163 Bronze Crown
    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
    Vin Steele Member Posts: 91
    Thank you Denes.
This discussion has been closed.