Skip to Main Content

APEX

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Ajax cascading Select list and ORA-06502

Vin SteeleOct 14 2008 — edited Oct 20 2008
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.

Comments

Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on Nov 17 2008
Added on Oct 14 2008
3 comments
275 views