Home » SQL & PL/SQL » SQL & PL/SQL » Not Catching Exception for ORA-06502
Not Catching Exception for ORA-06502 [message #295760] Wed, 23 January 2008 05:58 Go to next message
amit.pandey
Messages: 64
Registered: August 2006
Location: Bangalore, India
Member
Hi All,

I've created a function which is mentioned below. This function is throwing "ORA-06502: PL/SQL: numeric or value error: character string buffer too small" error.

Although to handle this error a exception is defined, but don't know why it it not raising that exception.

Kindly help me.

CREATE OR REPLACE FUNCTION Report( v_project_ID IN VARCHAR2, v_delimiter IN VARCHAR2 DEFAULT ',' )
RETURN VARCHAR2
AUTHID CURRENT_USER
AS
v_order_status VARCHAR2(32767);
v_cnt NUMBER;

CURSOR cur_project_ords IS
SELECT project_id, order_status
FROM fadm.ORDTBL
WHERE ord_stat IN('Open','Complete')
AND project_id = v_project_id;

BEGIN
v_order_status := '';
v_cnt := 1;

FOR v_cursor_data IN cur_project_ords LOOP
IF v_cnt = 1 THEN
v_order_status := v_cursor_data.ord_stat;
ELSE
v_order_status := v_order_status ||v_delimiter|| trim(v_cursor_data.ord_stat);
END IF;

v_cnt := v_cnt + 1 ;
END LOOP;

RETURN v_order_status ;

EXCEPTION
WHEN VALUE_ERROR THEN
dbms_output.put_line ('Error encountered');
RETURN v_order_status ;
END Report;
/
Re: Not Catching Exception for ORA-06502 [message #295762 is a reply to message #295760] Wed, 23 January 2008 06:04 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
First, please read and follow 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.

Now, this error does not come from your data but from a runtime error so it is not trapped. You don't have a "value error", you have a too small buffer.

Regards
Michel

Re: Not Catching Exception for ORA-06502 [message #295776 is a reply to message #295762] Wed, 23 January 2008 07:16 Go to previous messageGo to next message
amit.pandey
Messages: 64
Registered: August 2006
Location: Bangalore, India
Member
Thanks Michel,

I'll certainly take care of formatting my post in future.
Can you please tell me how I can check and increase the buffer size.

Thanks & Regards
Amit
Re: Not Catching Exception for ORA-06502 [message #295777 is a reply to message #295776] Wed, 23 January 2008 07:18 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
At which line did you have the error?
You should post this as long as with the code with line numbers.

Regards
Michel
Re: Not Catching Exception for ORA-06502 [message #295782 is a reply to message #295777] Wed, 23 January 2008 07:32 Go to previous messageGo to next message
amit.pandey
Messages: 64
Registered: August 2006
Location: Bangalore, India
Member
Hi Michel,

I'm getting this error. Oracle is showing at Line no. 1.

SQL> select Report('C10178265') from dual;
select Report('C10178265') from dual
*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error: character string buffer too small
ORA-06512: at line 1


But I'm doubting at line no. 23 -->
v_order_status := v_order_status ||v_delimiter|| trim(v_cursor_data.ord_stat);

This line is in for loop.

Thanks
Amit

[Updated on: Wed, 23 January 2008 07:32]

Report message to a moderator

Re: Not Catching Exception for ORA-06502 [message #295787 is a reply to message #295782] Wed, 23 January 2008 07:50 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
So check the length before concatenating.

Regards
Michel
Re: Not Catching Exception for ORA-06502 [message #295794 is a reply to message #295787] Wed, 23 January 2008 08:22 Go to previous messageGo to next message
amit.pandey
Messages: 64
Registered: August 2006
Location: Bangalore, India
Member
Hi Michel,

When the v_order_status exceeds 10268 characters it is throwing that ORA-06502error.

If I change the return type as CLOB, it is working fine. But I've to used Varchar2(32767) and raise an error if the string exceeds 32767 chars, but while checking the length it is throwing error after 10268 chars.

Can you please suggest what can I do in this case.

Regards
Amit
Re: Not Catching Exception for ORA-06502 [message #295797 is a reply to message #295794] Wed, 23 January 2008 08:30 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I bet you don't add the length of the content of what you will concatenate.

Quote:
Can you please suggest what can I do in this case.

Use CLOB.

Regards
Michel
Re: Not Catching Exception for ORA-06502 [message #295843 is a reply to message #295760] Wed, 23 January 2008 13:34 Go to previous message
Barbara Boehmer
Messages: 8625
Registered: November 2002
Location: California, USA
Senior Member
There are a number of problems with the code that you posted.

1. What you posted does not even compile. You are concatenating values of ord_stat from your cursor when ord_stat isn't even one of the columns selected in your cursor. That is why you should post an actual copy and paste of an actual run. I assume that you meant to concatenate values of order_status instead.

2. You should not be trying to return a value in your exception clause, since you already know that it is too big to do so.

3. The exception is not being caught because it is not occurring within the function. It is occurring within the sql select statement, which is why it says line 1. It is line 1 of your sql select statement, not line 1 of your function. The problem is that sql, whether used from sql*plus or within pl/sql has a size limit of 4000 for the sql varchar2 datatype. If you use pl/sql syntax to assign the value returned by the function to a pl/sql varchar2 variable then there is no error when the length is between 4000 and 32767. However, when it exceeds 32767, then the error is raised within the function and caught. Please see the demonstration below.

SCOTT@orcl_11g> CREATE TABLE ordtbl
  2    (project_id    VARCHAR2 (10),
  3  	order_status  VARCHAR2 (2000),
  4  	ord_stat      VARCHAR2 (8))
  5  /

Table created.

SCOTT@orcl_11g> BEGIN
  2    FOR i IN 1 .. 10 LOOP
  3  	 INSERT INTO ordtbl (project_id, ord_stat, order_status) VALUES ('C10178265', 'Open',
  4  	 'ABCDEFGHIJKLMNOPQRSTUVWXYZ1234ABCDEFGHIJKLMNOPQRSTUVWXYZ1234ABCDEFGHIJKLMNOPQRSTUVWXYZ1234');
  5    END LOOP;
  6  END;
  7  /

PL/SQL procedure successfully completed.

SCOTT@orcl_11g> CREATE OR REPLACE FUNCTION Report
  2    ( v_project_ID IN VARCHAR2,
  3  	 v_delimiter  IN VARCHAR2 DEFAULT ',' )
  4    RETURN VARCHAR2
  5    AUTHID CURRENT_USER
  6  AS
  7    v_order_status	 VARCHAR2(32767);
  8    v_cnt		 NUMBER;
  9  
 10    CURSOR cur_project_ords IS
 11    SELECT project_id, order_status
 12    FROM   ORDTBL
 13    WHERE  ord_stat IN('Open','Complete')
 14    AND    project_id = v_project_id;
 15  
 16  BEGIN
 17    v_order_status := '';
 18    v_cnt := 1;
 19  
 20    FOR v_cursor_data IN cur_project_ords LOOP
 21  	 IF v_cnt = 1 THEN
 22  	   v_order_status := v_cursor_data.order_status;
 23  	 ELSE
 24  	   v_order_status := v_order_status ||v_delimiter|| trim(v_cursor_data.order_status);
 25  	 END IF;
 26  
 27  	 v_cnt := v_cnt + 1 ;
 28    END LOOP;
 29  
 30    RETURN v_order_status ;
 31  
 32  EXCEPTION
 33    WHEN VALUE_ERROR THEN
 34  	 dbms_output.put_line ('Error encountered');
 35  	 RETURN 'too big';
 36  END Report;
 37  /

Function created.

SCOTT@orcl_11g> SHOW ERRORS
No errors.
SCOTT@orcl_11g> -- sql select works when size is <= 4000 limit for sql varchar2 datatype
SCOTT@orcl_11g> select Report('C10178265') from dual
  2  /

REPORT('C10178265')
--------------------------------------------------------------------------------
ABCDEFGHIJKLMNOPQRSTUVWXYZ1234ABCDEFGHIJKLMNOPQRSTUVWXYZ1234ABCDEFGHIJKLMNOPQRST
UVWXYZ1234,ABCDEFGHIJKLMNOPQRSTUVWXYZ1234ABCDEFGHIJKLMNOPQRSTUVWXYZ1234ABCDEFGHI
JKLMNOPQRSTUVWXYZ1234,ABCDEFGHIJKLMNOPQRSTUVWXYZ1234ABCDEFGHIJKLMNOPQRSTUVWXYZ12
34ABCDEFGHIJKLMNOPQRSTUVWXYZ1234,ABCDEFGHIJKLMNOPQRSTUVWXYZ1234ABCDEFGHIJKLMNOPQ
RSTUVWXYZ1234ABCDEFGHIJKLMNOPQRSTUVWXYZ1234,ABCDEFGHIJKLMNOPQRSTUVWXYZ1234ABCDEF
GHIJKLMNOPQRSTUVWXYZ1234ABCDEFGHIJKLMNOPQRSTUVWXYZ1234,ABCDEFGHIJKLMNOPQRSTUVWXY
Z1234ABCDEFGHIJKLMNOPQRSTUVWXYZ1234ABCDEFGHIJKLMNOPQRSTUVWXYZ1234,ABCDEFGHIJKLMN
OPQRSTUVWXYZ1234ABCDEFGHIJKLMNOPQRSTUVWXYZ1234ABCDEFGHIJKLMNOPQRSTUVWXYZ1234,ABC
DEFGHIJKLMNOPQRSTUVWXYZ1234ABCDEFGHIJKLMNOPQRSTUVWXYZ1234ABCDEFGHIJKLMNOPQRSTUVW
XYZ1234,ABCDEFGHIJKLMNOPQRSTUVWXYZ1234ABCDEFGHIJKLMNOPQRSTUVWXYZ1234ABCDEFGHIJKL
MNOPQRSTUVWXYZ1234,ABCDEFGHIJKLMNOPQRSTUVWXYZ1234ABCDEFGHIJKLMNOPQRSTUVWXYZ1234A
BCDEFGHIJKLMNOPQRSTUVWXYZ1234


SCOTT@orcl_11g> BEGIN
  2    FOR i IN 1 .. 100 LOOP
  3  	 INSERT INTO ordtbl (project_id, ord_stat, order_status) VALUES ('C10178265', 'Open',
  4  	 'ABCDEFGHIJKLMNOPQRSTUVWXYZ1234ABCDEFGHIJKLMNOPQRSTUVWXYZ1234ABCDEFGHIJKLMNOPQRSTUVWXYZ1234');
  5    END LOOP;
  6  END;
  7  /

PL/SQL procedure successfully completed.

SCOTT@orcl_11g> -- sql select does not work when size is > 4000 limit for sql varchar2 datatype
SCOTT@orcl_11g> select Report('C10178265') from dual
  2  /
select Report('C10178265') from dual
       *
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error: character string buffer too small
ORA-06512: at line 1


SCOTT@orcl_11g> -- but assignment to plsql variable with size limit of 32767 works:
SCOTT@orcl_11g> DECLARE
  2    v_test VARCHAR2 (32767);
  3  BEGIN
  4    v_test := Report('C10178265');
  5  END;
  6  /

PL/SQL procedure successfully completed.

SCOTT@orcl_11g> BEGIN
  2    FOR i IN 1 .. 1000 LOOP
  3  	 INSERT INTO ordtbl (project_id, ord_stat, order_status) VALUES ('C10178265', 'Open',
  4  	 'ABCDEFGHIJKLMNOPQRSTUVWXYZ1234ABCDEFGHIJKLMNOPQRSTUVWXYZ1234ABCDEFGHIJKLMNOPQRSTUVWXYZ1234');
  5    END LOOP;
  6  END;
  7  /

PL/SQL procedure successfully completed.

SCOTT@orcl_11g> -- when plsql limit of 32767 is reached, exception is caught:
SCOTT@orcl_11g> DECLARE
  2    v_test VARCHAR2 (32767);
  3  BEGIN
  4    v_test := Report('C10178265');
  5  END;
  6  /
Error encountered

PL/SQL procedure successfully completed.

SCOTT@orcl_11g> 




Previous Topic: Query Taking Long Time To Fetch the Resultsets
Next Topic: data manupulation
Goto Forum:
  


Current Time: Sun Dec 04 12:57:52 CST 2016

Total time taken to generate the page: 0.14512 seconds