OraFAQ Forum: SQL & PL/SQL » ORA-06502: PL/SQL: numeric or value error using Bulk

Home » SQL & PL/SQL » SQL & PL/SQL » ORA-06502: PL/SQL: numeric or value error using Bulk (Oracle 10g)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

ORA-06502: PL/SQL: numeric or value error using Bulk [message #296113]

Thu, 24 January 2008 11:47

amorphous4u
Messages: 34
Registered: December 2007
Location: Boston, US

Member

ORA-06502: PL/SQL: numeric or value error

I 'm getting the above-referenced runtime error; dynamic SQL, fetching into a set of nested tables using BULK COLLECT. Can 't find anything in docs or on MetaLink or Google;Is due
to exceeding array size? I have tried LIMIT clause in FETCH ... BULK COLLECT INTO, but no luck.
Anyone familiar with this error please guide me.
I have attached the peice of code as file attachment.

Thanks and Regards,
Amit

Attachment: Code.sql
(Size: 1.34KB, Downloaded 1274 times)

[Updated on: Thu, 24 January 2008 11:50]

Report message to a moderator

Re: ORA-06502: PL/SQL: numeric or value error using Bulk [message #296130 is a reply to message #296113]

Thu, 24 January 2008 15:01

Barbara Boehmer
Messages: 9106
Registered: November 2002
Location: California, USA

Senior Member

Many forum users are unable to download files. Please post your code (using code tags to preserve formatting) instead of attaching it as a file. Also, please post a copy and paste of an actual run of both the compilation of your code with the exception section commented out and how you are calling the code, complete with the error message. This is so that it will correctly show the corresponding line that is causing the error. Also, please post statements for creating the corresponding table and inserting some sample data, so that others can test your code. Typically you get the error that you are seeing when you are attempting to select the return value into a varchar or varchar2 variable instead of a clob variable.

Report message to a moderator

Re: ORA-06502: PL/SQL: numeric or value error using Bulk [message #296183 is a reply to message #296113]

Fri, 25 January 2008 00:50

Michel Cadot
Messages: 68760
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

In addition, please post your Oracle version with 4 decimals.
Also read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code.
Use the "Preview Message" button to verify.

Regards
Michel

Report message to a moderator

Re: ORA-06502: PL/SQL: numeric or value error using Bulk [message #297626 is a reply to message #296183]

Fri, 01 February 2008 16:27

amorphous4u
Messages: 34
Registered: December 2007
Location: Boston, US

Member

Hi,

I am attaching the code as below:

FUNCTION getTopUserGroupsPerFirm(v_FirmGUID IN VARCHAR, v_ContentSet IN VARCHAR, d_StartDate IN DATE, d_EndDate IN DATE)
RETURN CLOB
IS
TYPE data_curs_tab IS TABLE OF SYS.XMLTYPE; -- Added for declaring XMLTYPE Datatype

t_data_val data_curs_tab; --Variable for data_curs_tab

CURSOR data_curs IS
SELECT XMLForest(ut.user_group_guid as "UserGroupGUID",ut.usergroupname as "UserGroupName", Count(*) as "NumOfTransactions", SUM(ut.internal_price) as "DollarsSpent") as xml_row
FROM usertransaction ut
WHERE ut.firm_guid = v_firmGuid
AND (EVENT_DATETIME between d_StartDate and d_EndDate )
AND ut.content_type = v_ContentSet
GROUP by ut.user_group_guid,ut.usergroupname
ORDER BY SUM(ut.internal_price) DESC;
v_result CLOB;
v_nodename VARCHAR(64);
v_count NUMBER;
BEGIN
v_nodename := 'TopUserGroups_' || v_ContentSet;
OPEN data_curs;
FETCH data_curs bulk collect INTO t_data_val;
CLOSE data_curs;

FOR i IN t_data_val.first..t_data_val.last LOOP
v_result := v_result
|| '<UserGroup>'
|| t_data_val(i).getClobVal()
|| '</UserGroup>';
END LOOP;

RETURN '<' || v_nodename || '>' || v_result || '</' || v_nodename || '>';
EXCEPTION
WHEN OTHERS THEN
log_oracle_error(SQLCODE,SQLERRM,'Function UsageSummary_Test.getTopUserGroupsPerFirm throws an Exception',SYSDATE,NULL);
RETURN '';
END;

This is the function present in the Package which is throwing exception as
ORA-06502: PL/SQL: numeric or value error

Can you please help me out?

Thanks and Regards,
Amit Verma

Report message to a moderator

Re: ORA-06502: PL/SQL: numeric or value error using Bulk [message #297632 is a reply to message #297626]

Fri, 01 February 2008 17:45

Barbara Boehmer
Messages: 9106
Registered: November 2002
Location: California, USA

Senior Member

Although you posted the code, you did not preserve the formatting with code tags or provide create table and insert statements for testing, or include a copy and paste of the run, or show how you are calling it or anything else. As stated previously, most likely the problem is that you are attempting to return the results into a varchar2 variable instead of a clob, so you would need to change that in the calling code that you have not shown, not the code that you have posted. Please read the forum guide that you were previously referred to and try again.

Report message to a moderator

Re: ORA-06502: PL/SQL: numeric or value error using Bulk [message #297634 is a reply to message #297632]

Fri, 01 February 2008 18:25

amorphous4u
Messages: 34
Registered: December 2007
Location: Boston, US

Member

I am calling this function with the help of Toad and passing parameters while running it. And i am getting the error in exception. In the exception it is calling an autonomous transaction which is inserting the SQLERRM into the log table. There I come to know about the error. And this function is returning the value as CLOB only.
Whenever the cursor value is returning no rows at that point of time this error is occuring where as in case of data available in cursor is giving me right values.

Report message to a moderator

Re: ORA-06502: PL/SQL: numeric or value error using Bulk [message #297637 is a reply to message #297634]

Fri, 01 February 2008 20:16

Barbara Boehmer
Messages: 9106
Registered: November 2002
Location: California, USA

Senior Member

You need to test one thing at a time, so that you can narrow down where the error is coming from. You need to test it from SQL*Plus, not Toad, so that you know that it is not something with Toad that is causing the error. You need to test it without the exception section, by commenting it out, so that you know that it is not the autonomous program in the exception section that is causing the problem. If you do all of that and still get the error, then you will get a message that shows at exactly what point on what line the error is occuring, so that you know what to look for. Once again, this is a common error, that is usually caused by selecting the result into a varchar2 variable, instead of a clob. So, what data type are you selecting the result into? Once again, you need to read and follow the forum guidelines, and post a copy and paste of the run of compilation and execution of your code, including the line numbers, error number, and error message, and create table and insert statements for sample data, if you want help, otherwise you are on your own and any future posts will likely be ignored.

Report message to a moderator

Re: ORA-06502: PL/SQL: numeric or value error using Bulk [message #297638 is a reply to message #297634]

Fri, 01 February 2008 20:21

Barbara Boehmer
Messages: 9106
Registered: November 2002
Location: California, USA

Senior Member

Additional information:

Although the return data type of the function is clob, that does not mean that you are returning it into a clob data type.

Also, since you seem to be having a problem only when there are no rows, another common problem is that using first and last can cause a problem. You might try 1 and count or some such thing.

If you would run the code from SQL*Plus without the exception section, it would be easy to see where the error is, so there would be no need to guess at common problems that might be occurring.

Report message to a moderator

Re: ORA-06502: PL/SQL: numeric or value error using Bulk [message #297996 is a reply to message #297638]

Mon, 04 February 2008 08:17

amorphous4u
Messages: 34
Registered: December 2007
Location: Boston, US

Member

I cannot use count here as the table is very bulky...and it takes atleast 5 minutes to complete one count check and this function will be called atleast 8 times in one run.

This is the run from the SQL* Plus

SQL> set serveroutput on;
SQL> CREATE OR REPLACE FUNCTION getTopUserGroupsPerFirm(v_FirmGUID IN VARCHAR, v_ContentSet IN VARCHAR, d_StartDate IN DATE, d_EndDate IN DATE)
2 RETURN CLOB
3 IS
4 TYPE data_curs_tab IS TABLE OF SYS.XMLTYPE; -- Added for declaring XMLTYPE Datatype
5
6 t_data_val data_curs_tab; --Variable for data_curs_tab
7
8 CURSOR data_curs IS
9 SELECT XMLForest(ut.user_group_guid as "UserGroupGUID",ut.usergroupname as "UserGroupName", Count(*) as "NumOfTransactions", SUM(ut.internal_price) as "DollarsSpent") as xml_row
10 FROM usertransaction ut
11 WHERE ut.firm_guid = v_firmGuid
12 AND (EVENT_DATETIME between d_StartDate and d_EndDate )
13 AND ut.content_type = v_ContentSet
14 GROUP by ut.user_group_guid,ut.usergroupname
15 ORDER BY SUM(ut.internal_price) DESC;
16 --data_val data_curs%ROWTYPE;-- Commented as not required after change.
17 v_result CLOB;
18 v_nodename CLOB;
19
20 BEGIN
21 v_nodename := 'TopUserGroups_' || v_ContentSet;
22 -- Change for Optimization by Amit Verma on Dec 31, 2007 Starts
23 -- Below code is commented for Optimization Change
24 /*FOR data_val IN data_curs
25 LOOP
26 v_result := v_result
27 || '<UserGroup>'
28 || data_val.xml_row.getClobVal()
29 || '</UserGroup>';
30 END LOOP;*/
31 -- Below Code is introduced for Optimization
32 OPEN data_curs;
33 FETCH data_curs bulk collect INTO t_data_val;
34 CLOSE data_curs;
35
36 FOR i IN t_data_val.first..t_data_val.last LOOP
37 v_result := v_result
38 || '<UserGroup>'
39 || t_data_val(i).getClobVal()
40 || '</UserGroup>';
41 IF v_result IS NULL THEN
42 v_result := '';
43 END IF;
44 END LOOP;
45
46 RETURN '<' || v_nodename || '>' || v_result || '</' || v_nodename || '>';
47 -- Change for Optimization by Amit Verma on Dec 31, 2007 Ends
48 --EXCEPTION
49 --WHEN OTHERS THEN
50 --log_oracle_error(SQLCODE,SQLERRM,'Function UsageSummary_Test.getTopUserGroupsPerFirm throws an Exception',SYSDATE,NULL);
51 -- RETURN '';
52 END;
53 /

Function created.

SQL> select getTopUserGroupsPerFirm('{20A626BA-D648-4C15-B772-7307F904E768}','FilingsUsage',to_date('10/27/2005','mm/dd/yyyy'),to_date('10/28/200 5','mm/dd/yyyy'))
2 from dual;
ERROR:
ORA-06502: PL/SQL: numeric or value error
ORA-06512: at "BANKDEV.GETTOPUSERGROUPSPERFIRM", line 36

no rows selected

Thanks and Regards,
Amit

Report message to a moderator

Re: ORA-06502: PL/SQL: numeric or value error using Bulk [message #298005 is a reply to message #297996]

Mon, 04 February 2008 09:30

S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom

Senior Member

I think this could be your error i am not sure but still I thought it's worth a try.
Search for limit clause. By the look of it you are collecting the entire result of your cursor into the variable which is not a good way of doing it unless and until you are fetching only few thousand still I would like to see a limit clause in your bulk collect and loop around it. It's the ideal way of processing.

HTH

Regards

Raj

Report message to a moderator

Re: ORA-06502: PL/SQL: numeric or value error using Bulk [message #298050 is a reply to message #297996]

Mon, 04 February 2008 15:58

Barbara Boehmer
Messages: 9106
Registered: November 2002
Location: California, USA

Senior Member

I don't think you understood me. If you use 1 and count instead of first and last, by changing this line:

FOR i IN t_data_val.first..t_data_val.last LOOP

to:

FOR i IN 1..t_data_val.count LOOP

it will solve your problem and eliminate the error when there are no rows in the cursor.

Report message to a moderator

Re: ORA-06502: PL/SQL: numeric or value error using Bulk [message #298051 is a reply to message #298050]

Mon, 04 February 2008 16:01

amorphous4u
Messages: 34
Registered: December 2007
Location: Boston, US

Member

Hi Barbara,

Thank you very much for your help. I used count only but in other ways.

IF t_data_val.COUNT > 0 THEN
FOR i IN t_data_val.first..t_data_val.last LOOP
v_result := v_result
|| '<UserGroup>'
|| t_data_val(i).getClobVal()
|| '</UserGroup>';
END LOOP;
RETURN '<' || v_nodename || '>' || v_result || '</' || v_nodename || '>';
ELSE
RETURN '<' || v_nodename || '>' || '</' || v_nodename || '>';
END IF;

The issue has now been solved and the code is working fine. Thanks for all your help.

Regards,
Amit

Report message to a moderator

Previous Topic:	Problem with Copying data from a Remote Database to Local Database
Next Topic:	No of 1's in a binary string

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Jun 19 18:18:52 CDT 2025