ORA-06502 - Cursor Problem

[Vlad]

I am developing a script and having problems understanding cursors.
When I run the script below I get

ORA-06502: PL/SQL: numeric or value error
ORA-06512: at line 27

Line 27 is FETCH T1Cursor INTO filename,
filetext;

Am I missing something obvious?

I am using oracle 10.

TIA

V

DECLARE
filename VARCHAR2(32760);
filetext VARCHAR2(32760);
CURSOR T1Cursor IS
SELECT ccn.NAME||'.xml' , ccs.script_text
FROM cmn_custom_scripts ccs,
bpm_def_step_actions bdsa,
cmn_captions_nls ccn,
bpm_def_steps bds,
bpm_def_stages bdstg,
bpm_def_process_versions bdpv,
bpm_def_processes bdp
WHERE ccs.ID = bdsa.script_id
AND bdsa.ID = ccn.pk_id
AND bdsa.step_id = bds.ID
AND bds.stage_id = bdstg.ID
AND bdstg.process_version_id = bdpv.ID
AND bdpv.process_id = bdp.ID
AND ccn.table_name = 'BPM_DEF_STEP_ACTIONS'
AND bdsa.type_code = 'BPM_SAT_CUSTOM'
AND ccs.language_code = 'gel'
AND ccn.language_code = 'en'
ORDER BY bdp.id;
BEGIN
OPEN T1Cursor;
LOOP/* Retrieve each row of the result of the above
query into PL/SQL variables: */
FETCH T1Cursor INTO filename, filetext; /* If
there are no more rows to fetch, exit the loop: */
EXIT WHEN T1Cursor%NOTFOUND;
END LOOP;
CLOSE T1Cursor;
END;

[sybrandb]

This error will occur if ccn.NAME||'.xml' , ccs.script_text exceed the
hardcoded limit of 32760.

Generally speaking you should not hardcode, but use
<table_name>.<column_name>%type to avoid ora-6502.

Also why are you fetching one record at a time?
Why don't you just
select ... bulk collect into <collection>?
You are applying 8i techniques to 10g!

--
Sybrand Bakker
Senior Oracle DBA

[gmpadua]

I agree with Sybrand. Check the length of the column being returned by
you cursor.

Cheers,
Garry Padua
Senior Oracle DBA