Thank you so much Tom. It was really helpful and helped us to understand how the processing takes place. Thanks once again.

Hi Tom,

In a Procedure I am taking Bunch of values as owa_util.ident_Arr.

create or replace procedure procedure_name
(question_id owa_util.ident_Arr,
in_question owa_util.ident_Arr)

I am getting this Following error if length of the any of the input values, are more than 30 characters.

ORA-06502: PL/SQL: numeric or value error: character string buffer too small

If all the values i passed thru this array are less than 30 characters length, its executing fine.

can you please suggest us a solution?

Thank you Tom.
Bhavani

SQL>exec p_pkg.get_prc_all(50,:rc,:out_num,:msg_cd,:msg_str);
BEGIN p_pkg.get_prc_all(50,:rc,:out_num,:msg_cd,:msg_str); END;

*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error
ORA-06512: at "SCOTT.P_PKG", line 49
ORA-06512: at line 1



SQL>exec p_pkg.get_prc_all(50,:rc,:out_num,:msg_cd,:msg_str);

PL/SQL procedure successfully completed.

SQL>print :msg_cd

MSG_CD
----------------
SUCCESS

SQL*>print :str

STR
------------------------------------


/**********************************************************/

 1st time it is an error but 2nd , 3rd,4th ... it works fine
can you please tell me why ?

 in this stored proc. I don't have any temp variables or buffers that I hold data in it just opens a ref cursor and sends it back 

Thanks, 

I would actually need to see the code.  I can reproduce this -- but i've no idea if it is the same cause


ops$tkyte@ORA920> exec demo_pkg.p
BEGIN demo_pkg.p; END;

*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error: character to number conversion error
ORA-06512: at "OPS$TKYTE.DEMO_PKG", line 49
ORA-06512: at line 1


ops$tkyte@ORA920> exec demo_pkg.p
hello world

PL/SQL procedure successfully completed.



...
create or replace package body demo_pkg
as
    g_num number;

    procedure p
    is
    begin
        dbms_output.put_line( 'hello world' );
    end;

.....

begin
    g_num := 'a';
end;
/

 

Hi tom, thx for your reply.

but as i said, I do not have any variables
I am *NOT* moving any field into any variables
so there is no type conversion or not any kind of buffer overflow !!

Can you please help.I am trying to run the following code and get

DECLARE
*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error: character string buffer too small
ORA-06512: at line 75
----
set pages 10000;
set lines 600;
set serveroutput on size 1000000;
DECLARE
l_file UTL_FILE.file_type;
l_last_filename varchar2(255) := '0000000';
l_cust varchar2(45) := '00000Q.XLS';
l_custid varchar2(6) :=' ';
l_custnm varchar2(45) := ' ';
l_custid_prev varchar2(6) := ' ';
l_fcst_mth1 number(13,2) := 0.00;
l_fcst_mth2 number(13,2) := 0.00;
l_fcst_mth3 number(13,2) := 0.00;
l_fcst_qtr number(13,2) := 0.00;
CURSOR forecast_cur is
select v.cust_corp_id c1, substr(v.CUST_CORP_NM,1,5) c2,
SUM(decode(to_char(b.FCST_DT,'MON'),'JAN',b.FCST_AMT,null)) c3,
SUM(decode(to_char(b.FCST_DT,'MON'),'FEB',b.FCST_AMT,null)) c4,
SUM(decode(to_char(b.FCST_DT,'MON'),'MAR',b.FCST_AMT,null)) c5,
SUM(decode(to_char(b.FCST_DT,'MON'),'APR',B.FCST_AMT,null)) c6,
SUM(decode(to_char(b.FCST_DT,'MON'),'MAY',B.FCST_AMT,null)) c7,
SUM(decode(to_char(b.FCST_DT,'MON'),'JUN',B.FCST_AMT,null)) c8,
SUM(decode(to_char(b.FCST_DT,'MON'),'JUL',B.FCST_AMT,null)) c9,
SUM(decode(to_char(b.FCST_DT,'MON'),'AUG',B.FCST_AMT,null)) c10,
SUM(decode(to_char(b.FCST_DT,'MON'),'SEP',B.FCST_AMT,null)) c11,
SUM(decode(to_char(b.FCST_DT,'MON'),'OCT',B.FCST_AMT,null)) c12,
SUM(decode(to_char(b.FCST_DT,'MON'),'NOV',B.FCST_AMT,null)) c13,
SUM(decode(to_char(b.FCST_DT,'MON'),'DEC',B.FCST_AMT,null)) c14
from cso.FCST b,
cso.cust_corp_ppl_vw v
where b.cust_corp_id= v.cust_corp_id
and
to_char(b.FCST_DT, 'YYYY/MM') in ( '2003/01', '2003/02', '2003/03') and
v.cust_corp_id in ( '040116','027245','027278','034018','068970','036324', '000549')
group by v.cust_corp_id, v.CUST_CORP_NM;
BEGIN
l_cust := '0000001Q.XLS';
l_last_filename := '9999999Q.XLS';
IF (UTL_FILE.IS_OPEN(l_file)) THEN
UTL_FILE.FCLOSE(l_file);
END IF;
l_file := UTL_FILE.FOPEN('/tmp',l_cust,'w',32767);
l_custid := ' ';
l_custid_prev := ' ';
l_fcst_mth1 := 0.00;
l_fcst_mth2 := 0.00;
l_fcst_mth3 := 0.00;
l_fcst_qtr := 0.00;
FOR i in forecast_cur LOOP
IF forecast_cur%ROWCOUNT = 1 THEN
l_custid_prev := i.c1;
END IF;
END LOOP;
FOR i in forecast_cur LOOP
IF l_cust <> l_last_filename
THEN
UTL_FILE.PUT_LINE(l_file,'Jan|Feb|Mar|Q1');
END IF;
l_last_filename := l_cust;
l_custid := i.c1;
IF l_custid_prev = l_custid THEN
UTL_FILE.PUT_LINE(l_file, nvl(i.c2,0) ||'|'|| nvl(i.c3,0) ||'|'||nvl(i.c4,0)||'|');
l_fcst_mth1 := 0.00;
l_fcst_mth2 := 0.00;
l_fcst_mth3 := 0.00;
l_fcst_qtr := 0.00;

l_custid_prev := i.c1;
END IF;
END LOOP;
FOR i in forecast_cur LOOP
IF forecast_cur%ROWCOUNT = 1 THEN
l_custid_prev := i.c1;
END IF;
END LOOP;
FOR i in forecast_cur LOOP
l_cust := i.c2 || 'Q.XLS';
l_custid := i.c3;
IF l_cust <> l_last_filename THEN
IF (UTL_FILE.IS_OPEN(l_file)) THEN
IF forecast_cur%ROWCOUNT <> 1 AND l_custid <> l_custid_prev THEN
UTL_FILE.PUT_LINE(l_file,'Jan|Feb|Mar|Q1');
END IF;
l_custid_prev := i.c1;
UTL_FILE.FCLOSE(l_file);
END IF;
l_file := UTL_FILE.FOPEN('/tmp',l_cust,'w',32767);
UTL_FILE.PUT_LINE(l_file,'Jan|Feb|Mar|Q1');
END IF;
l_last_filename := l_cust;
END LOOP;
IF (UTL_FILE.IS_OPEN(l_file)) THEN
UTL_FILE.FCLOSE(l_file);
END IF;
END;
/
-----

Please forget my last post. Fixed the error.

Thanks

I have created this function that given 2 locations (centroids in decimal) it will return the distance in either miles or kilometers (geomapping distance). The problem is that it inconsistantly fails with a ora-06512 error:

ERROR at line 4:
ORA-06502: PL/SQL: numeric or value error
ORA-06512: at "SYS.STANDARD", line 452
ORA-06512: at "DIST", line 17

where line 17 is the return. I assume that its overflowing on the NUMBER datatype, but I'm not sure where

Here is the function.

Create or Replace Function dist
( from_lat IN number, --centroid_y
from_long IN number, --centroid_x
to_lat IN number, --centroid_y
to_long IN number, --centroid_x
units IN char) -- KM or MI
RETURN number IS
radians number := round(atan2(1,1) * 4/180,6); --atan2(1,1) * 4 = pi
-- convert 4 input values from degrees to radians
w_from_lat number := from_lat * radians;
w_from_long number := from_long * radians;
w_to_lat number := to_lat * radians;
w_to_long number := to_long * radians;
r number := case upper(units) when 'KM' then 6378 else 3963 end;
BEGIN

-- calculate the return value in miles or kilometers
return acos(
cos(w_from_lat)*cos(w_from_long)*cos(w_to_lat)*cos(w_to_long)
+ cos(w_from_lat)*sin(w_from_long)*cos(w_to_lat)*sin(w_to_long)
+ sin(w_from_lat)*sin(w_to_lat)) * r;
END;


Some Data points
CITY_TOWN CENTROID_Y CENTROID_X
"Darlingford" 49.2063 -98.4395
"Morden" 49.1925 -98.1085

Is there a spatial MDSYS function that is already created for this? I cannot find anything similar.

That code does not work, 

SQL> alter table CITY_TOWN add (geometry mdsys.sdo_geometry);

Table altered.

SQL> update CITY_TOWN a
  2    set a.geometry =
  3      mdsys.sdo_geometry(2001,null,
  4         mdsys.sdo_point_type(a.centroid_x,
  5                              a.centroid_y,
  6                            null),
  7         null, null)
  8   where a.centroid_y <>0;

3630 rows updated.

SQL> commit;

Commit complete.

SQL> insert into user_sdo_geom_metadata
  2  values ('CITY_TOWN','GEOMETRY',
  3     mdsys.sdo_dim_array(
  4        mdsys.sdo_dim_element
  5        ('X',-180,180,.00005),
  6        mdsys.sdo_dim_element
  7        ('Y',-90,90,.00005)), null)
  8  ;

1 row created.

SQL> commit;

Commit complete.

SQL> create index indx_city_town_geo on CITY_TOWN(geometry) indextype is mdsys.spatial_index;

Index created.

SQL> select A.CITY_TOWN FROM_CITY, B.CITY_TOWN TO_CITY
  2  from
  3  (select CITY_TOWN, geometry from CITY_TOWN where CITY_TOWN = 'Darlingford') A,
  4  CITY_TOWN B
  5  where MDSYS.LOCATOR_WITHIN_DISTANCE
  6      (b.geometry, a.geometry,
  7       'distance = 25,unit=mile') = 'TRUE';
select A.CITY_TOWN FROM_CITY, B.CITY_TOWN TO_CITY
*
ERROR at line 1:
ORA-29902: error in executing ODCIIndexStart() routine
ORA-13207: incorrect use of the [No UNIT support without SRID] operator
ORA-06512: at "MDSYS.SDO_INDEX_METHOD_9I", line 368
ORA-06512: at line 1

You cannot use the units parameter without specifying an SRID in the insert for the metadata.  Removing the Units does not return accurate results (it returns everything).

Oh ya, its unit not units.
 

In the example shown by Clarke Colombo there were some syntax errors.

(Z2.AVG_GEOM, TA.GEOMETRY,
     'distance = '||:dist||',units=mile') <---- here
                              = 'TRUE';

"Units" is incorrect syntax, it's "unit".  Plus the example will not work because in the meta data insert:

insert into user_sdo_geom_metadata
values ('B','GEOMETRY'
   mdsys.sdo_dim_array(
      mdsys.sdo_dim_element
      ('X',-180,180,.00005),
      mdsys.sdo_dim_element
      ('Y',-90,90,.00005)), null); 

the last null should be a SRID value if you want to use the unit parameter with MDSYS.LOCATOR_WITHIN_DISTANCE.

Thank you for pointing me in the right direction.  I will continue to follow it.  I would appreciate it if Clark could do some more examples (yes I read the docs, that's how I found out the example was not working and why).

Regardless, my inital problem of Numeric error was resolved due to the fact the the formula from my initial post created a value witihn the acos() of 1.00000000000000000001.  

Executing:
SQL> select acos(1.00000000000000000001) from dual;
select acos(1.00000000000000000001) from dual
             *
ERROR at line 1:
ORA-01428: argument '1.00000000000000000001' is out of range

So the numeric error was derived from an out of range formula. It shouldn't be possible that the formula returns a value >1 however I'm looking into it.
 

Hi Tom,
Look at this code:
PACKAGE test IS
PROCEDURE HOME;
END;

PACKAGE BODY TEST IS

PROCEDURE FORM1(N VARCHAR2 ) IS
BEGIN
HTP.P('HELLO '||N);
END;
PROCEDURE HOME IS
BEGIN
HTP.FORMOPEN('test.form1','POST');
HTP.FORMTEXT('N');
HTP.FORMSUBMIT;
HTP.FORMCLOSE;
END;

END;

now i enter the url:
</code> http://hashmat/pls/mail/test.home

the page is displayed with a text box and a button, i enter any value and press the submit button.the url chages to 

http://hashmat/pls/mail/test.form1 <code>
and the browser says 'THE PAGE CANNOT BE DISPLAYED'
Now if i make the form1 procedure public, again it does the same but if i refresh the browser it displays
HELLO WHATEVER VALUE.
can you tell me whats wrong
thanks

Hi Tom,
I have a cursor which select a long column but when I open it and loop through, after looping few times it throws numeric or value error.

code goes:


set long 500000
set serveroutput on
DECLARE
v_id_prod number;
BEGIN
FOR i IN (SELECT pa.pie_id_prod p_id_prod,pda.document document,pda.doc_class_cd doc_class_cd
FROM product_document_au pda,pie_association pa
WHERE pa.hed_id_prod=pda.id_prod)
LOOP
--DELETE FROM product_document_au where id_prod IN (select pie_id_prod from pie_association);
--commit;
--INSERT INTO product_document_au(id_prod,DOC_CLASS_CD,document,create_user,row_create_date)
--VALUES(c1rec.pie_id_prod,c2rec.doc_class_cd,c2rec.document,'PIEMKTUPD',sysdate);
--commit;
v_id_prod:=i.p_id_prod;
dbms_output.put_line(i.p_id_prod);
END LOOP;
exception
when others then
dbms_output.put_line('pie_id_prod '||v_id_prod||'err'||sqlerrm);
END;
/
when run:
pie_id_prod 358621errORA-06502: PL/SQL: numeric or value error

PL/SQL procedure successfully completed.

------------
the cursor is giving problems with the long data type
pda.document in this case
please help

Hi Tom,
Look at this code:
PACKAGE test IS
PROCEDURE HOME;
PROCEDURE FORM1(N VARCHAR2 default null );

END;

PACKAGE BODY TEST IS

PROCEDURE FORM1(N VARCHAR2 default null) IS
BEGIN
HTP.P('HELLO '||N);
END;
PROCEDURE HOME IS
BEGIN
HTP.FORMOPEN('test.form1','POST');
HTP.FORMTEXT('N');
HTP.FORMSUBMIT;
HTP.FORMCLOSE;
END;

END;

now i enter the url:

</code> http://hashmat/pls/mail/test.home

the page is displayed with a text box and a button, i enter any value and press 
the submit button.the url chages to 

http://hashmat/pls/mail/test.form1

and the browser displayes
HELLO WHATEVER VALUE.
does that mean i have to make all procedures public by declaring them in package spec? while my plan is make a single package, declare one procedure (main procedure)in package spec callable from browser and all other procedures/functions in package body only main procedure can call all other procedures. in the upper code, if someone enter

http://hashmat/pls/mail/test.form1 <code>
directly ,bypassing t1, the procedure executes successfully displaying 'HELLO'
can you clear my confusion?
thanks

Hi Tom,
Look at this code:
PACKAGE test IS
PROCEDURE HOME;
PROCEDURE FORM1(N VARCHAR2 default null );

END;

PACKAGE BODY TEST IS

PROCEDURE FORM1(N VARCHAR2 default null) IS
BEGIN
HTP.P('HELLO '||N);
END;
PROCEDURE HOME IS
BEGIN
HTP.FORMOPEN('test.form1','POST');
HTP.FORMTEXT('N');
HTP.FORMSUBMIT;
HTP.FORMCLOSE;
END;

END;

now i enter the url:

</code> http://hashmat/pls/mail/test.home

the page is displayed with a text box and a button, i enter any value and press 
the submit button.the url chages to 

http://hashmat/pls/mail/test.form1

and the browser displayes
HELLO WHATEVER VALUE.
does that mean i have to make all procedures public by declaring them in package spec? while my plan is make a single package, declare one procedure (main procedure)in package spec callable from browser and all other procedures/functions in package body only main procedure can call all other procedures. in the upper code, if someone enter

http://hashmat/pls/mail/test.form1 <code>
directly ,bypassing t1, the procedure executes successfully displaying 'HELLO'
can you clear my confusion?
thanks
I m using 9iR1 on Win2kPro

Hi Tom,
I am using 8.1.7 on unix box.I have created a package pk_qcmaint_constants which contains many constant variables all of VARCHAR2(100).I am refering these variables in one of my other package.Whenever I am refering any of these constant variables it is giving the following error :
ORA-06502: PL/SQL: numeric or value error: character string buffer too small.

I have checked the length also.It is also not allowing to print the values of this variables.
Can you please let me know the cause of this error.

Thanks in Advance
Thangadas.

give me an example.

sounds like one of them is "too small" for what it is being initialized to.

ops$tkyte@ORA9IR2> @test
ops$tkyte@ORA9IR2> create or replace package demo_pkg
  2  as
  3          g_foo constant varchar2(100) := rpad( 'x', 101, 'x' );
  4  end;
  5  /
 
Package created.
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> declare
  2          l_x varchar2(1000) := demo_pkg.g_foo;
  3  begin
  4          null;
  5  end;
  6  /
declare
*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error: character string buffer too small
ORA-06512: at "OPS$TKYTE.DEMO_PKG", line 3
ORA-06512: at line 2


and the error message should show you which one straight off. 

Hi Tom,
Thanks for your response for my above Query.Now this is the code that I am using .

CREATE OR REPLACE PACKAGE pk_constants
AS
C_REM_ITEM CONSTANT VARCHAR2(100) := ' Remove Item from Quote';
END pk_constants;

I have around 60 Constants defined like this in the above package.
All of them are of the same length and the data that is assigned is less than the length in all the cases.


I am referring the Variable that I have defined in the above package as Follows.

BEGIN
DBMS_OUTPUT.PUT_LINE('C_REM_ITEM ' ||pk_qcmaint_constants.C_REM_ITEM);
END;

When I run this block of Code for the first time I encountered the following Error

ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error: character string buffer too small
ORA-06512: at "APPS.PK_QCMAINT_CONSTANTS", line 10
ORA-06512: at line 2

When I run the block again it does not throw the error but the value of the constant is not getting printed i.e. the value is Null.

One more observation : If I comment some variables in my package then the values in the variables are getting printed.

Is there any restriction on the no of Constants defined in a package OR is it because of some other reason like restriction of the Shared pool.
Can you throw some light in this case.

Thanks
Thangadas.

Hi Tom,

We are migrating our application from 8i to 9i. We are facing the "Numeric or Value Error - String Buffer too small to fit" Error while storing a value into a
Record Type declared in a package using the INTO clause.

SELECT x,y, ...
INTO rbrok_setting_values
FROM ...

rbrok_setting_values is a record type declared in a Package.
This SELECT runs outside as a PL/SQL anonymous block but doesn't work in side a Stored Procedure. This rbrok_setting_values record type is an OUT parameter in the Procedure.

Pls note that the variable x in the select list is anchored to the appropriate column. Similarly the corresponding variable in the record type is also anchored to the same column. However, only a Single character is returned, inspite the column width being CHAR(4). Pls help.

Thanx

Kaushik

Hi Tom,

With Reference to the previous problem posted by me and your subsequent reply to it. I am posting the following statements which run perfectly well in 8i but throws an error when executed in 9i.

CREATE TABLE temp_problem(myproblem CHAR(4) DEFAULT ' ')
/

INSERT INTO temp_problem VALUES('1')
/

CREATE OR REPLACE PACKAGE mypack
AS
TYPE rbrokerage_preprocessor IS RECORD (
bpr_brkg_set_type temp_problem.myproblem%TYPE);
END mypack;
/

CREATE OR REPLACE PROCEDURE scheme_detail (
pcbrok_set_type IN temp_problem.myproblem%TYPE,
rbrok_setting_values OUT mypack.rbrokerage_preprocessor
)
AS
BEGIN
SELECT pcbrok_set_type
INTO rbrok_setting_values
FROM dual;

END;
/

---------------------------------------------------
-- after the creation of above objects
--execute in sqlprompt

DECLARE
cout mypack.rbrokerage_preprocessor;

begin
scheme_detail('4', cout);
end;

--after execution the error
--Numeric or Value Error: Character buffer string too small
--throws up

Thanx In Advance. Please help.

Kaushik.

I hate the char type, wish it never had been created.  It stinks for so many reasons.


forget all of the fancy %types -- parameters are always non-constrained types, always.  I've simplified your example to:

ops$tkyte@ORA9IR2> cREATE OR REPLACE PROCEDURE c ( p_char in char )
  2  AS
  3      l_char char(4);
  5  BEGIN
  6      dbms_output.put_line( length( p_char  ) );
  7      select p_char into l_char from dual;
  8      dbms_output.put_line( length( l_char  ) );
  9      for x in ( select p_char a from dual )
 10      loop
 11          dbms_output.put_line( length(x.a) );
 12      end loop;
 13  END;
 14  /
 
Procedure created.
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> exec c('1')
1
BEGIN c('1'); END;
 
*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error: character string buffer too small
ORA-06512: at "OPS$TKYTE.C", line 7
ORA-06512: at line 1

<b>what happened is the unconstrained length in 9i is being considered "4000" and the output char type is a char(4000) for the select into, we can actually see this in 8i:</b>

tkyte@ORA8IW> cREATE OR REPLACE PROCEDURE c ( p_char in char )
  2  AS
  3      l_char char(4);
  4  BEGIN
  5      dbms_output.put_line( length( p_char  ) );
  6      select p_char into l_char from dual;
  7      dbms_output.put_line( length( l_char  ) );
  8      for x in ( select p_char a from dual )
  9      loop
 10          dbms_output.put_line( length(x.a) );
 11      end loop;
 12  END;
 13  /

Procedure created.

tkyte@ORA8IW>
tkyte@ORA8IW> exec c('1')
1
4
4000

PL/SQL procedure successfully completed.

<b>the implicit length of that select CHAR from dual is 4000 -- even then, just in 8i they looked at the length of the recieving string and bound that length for output</b>

However, if you set blank_trimming on:

ops$tkyte@ORA9IR2> show parameter blank_trimming;
 
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
blank_trimming                       boolean     TRUE
ops$tkyte@ORA9IR2> @test
ops$tkyte@ORA9IR2> cREATE OR REPLACE PROCEDURE c ( p_char in char )
  2  AS
  3      l_char char(4);
  4          l_n    number;
  5  BEGIN
  6      dbms_output.put_line( length( p_char  ) );
  7      select p_char into l_char from dual;
  8      dbms_output.put_line( length( l_char  ) );
  9      for x in ( select p_char a from dual )
 10      loop
 11          dbms_output.put_line( length(x.a) );
 12      end loop;
 13  END;
 14  /
 
Procedure created.
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> exec c('1')
1
4
4000
 
PL/SQL procedure successfully completed.
 


it'll succeed.  Please contact support about the status of bug 2338072 regarding this (but the blank_trimming is a workaround for now) 

Great. That was pretty explanatory.
However, Pls tell me if blank_trimming = TRUE would lead to trimming in the WHERE clause in all the SELECT or UPDATE statements. Also tell me if this will create problems in the DECODE conditions.
For. e.g
SELECT ...
FROM mytable
WHERE column1 = 'ABC ';

OR
SELECT ...
FROM mytable
WHERE column1 = cvariable;

cvariable is of CHAR datatype.

Thanx in advance

Kaushik

blank_trimming:

TRUE

Allows the data assignment of a source character string or variable to a
destination character column or variable even though the source length is
longer than the destination length. In this case, however, the additional length
over the destination length is all blanks. This value complies with the semantics
of SQL92 Transitional Level and above.


it should affect only assignments.

ops$tkyte@ORA9IR2> show parameter blank
 
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
blank_trimming                       boolean     TRUE
ops$tkyte@ORA9IR2> create table t ( x varchar2(10), y char(6) );
 
Table created.
 
ops$tkyte@ORA9IR2> insert into t values ( 'ABC   ', 'ABC' );
 
1 row created.
 
ops$tkyte@ORA9IR2> select '"' || x || '"', '"' || y || '"' from t;
 
'"'||X||'"'  '"'||Y||
------------ --------
"ABC   "     "ABC   "
 
<b>the blanks are there -- both on the varchar2 when explicitly placed there and of course on the char</b>

ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> select * from t where x = 'ABC';
 
no rows selected

<b>the varchar2 wasn't trimmed</b>

ops$tkyte@ORA9IR2> select * from t where y = 'ABC'
  2  /
 
X          Y
---------- ------
ABC        ABC
 

<b>and the character string literal is promoted to a char 6 just like it always was</b>

ops$tkyte@ORA9IR2> select * from t where x = 'ABC   ';
 
X          Y
---------- ------
ABC        ABC

<b>the blanks are there..</b>
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> variable c char(6)
ops$tkyte@ORA9IR2> exec :c := 'ABC   ';
 
PL/SQL procedure successfully completed.
 
ops$tkyte@ORA9IR2> select * from t where x = :c;
 
X          Y
---------- ------
ABC        ABC
 
ops$tkyte@ORA9IR2> select * from t where y = :c;
 
X          Y
---------- ------
ABC        ABC

<b>likewise...</b>

 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> exec :c := 'ABC                      ';
 
PL/SQL procedure successfully completed.

<b>that is the difference -- notice:</b>
 
ops$tkyte@ORA9IR2> !
[tkyte@localhost tkyte]$ ora9iutf
[tkyte@localhost tkyte]$ plus
 
SQL*Plus: Release 9.2.0.4.0 - Production on Sat Dec 25 13:11:30 2004
 
Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.
 
 
Connected to:
Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.4.0 - Production
 
ops$tkyte@ORA9IUTF> show parameter blank
 
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
blank_trimming                       boolean     FALSE
ops$tkyte@ORA9IUTF> variable c char(6)
ops$tkyte@ORA9IUTF> exec :c := 'ABC                          ';
BEGIN :c := 'ABC                          '; END;
 
*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error: character string buffer too small
ORA-06512: at line 1
 
<b>without blank trimming, that fails...</b>
 

Thanks a lot for your very useful and elaborate reply.

Kaushik

procedure c (p_char in varchar2 default null,p_name in varchar2 default null)
as
l_char varchar2(80);
begin
htp.p(length(p_char));
htp.p(length(p_name));
end;

And I executed the procedure as following.
</code> https://anusid/IPDS/c?p_char=1234567890&p_name=ipds <code>
I increased the characters to p_name or p_char, sometimes it will work, some times it gives the following error.

Thu, 13 Jan 2005 18:12:32 GMT

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

I don't even 10 lines in my procedure, but it shows at line number 10.

If go compile the procedure, come back hit refresh on the same screen it will come up.

I gave a simple example, but we have problem through out the system, after we moved from 8 to 9.2.

We need your help....
Thank you

Hi,

value or numeric error will cause b'coz of 2 two reasons
1. mismatch of data type 2. inserted value too large is there any unique way to find out (not using sqlerrm) the type of occurence of value or numeric error, as I found the sqlcode for both occurences are same.

Thanx in advance

Tom,
I am trying to insert/Update in a table with so many columns, it's not possible to find out from this error mssg
which column is throwing this mssg
PL/SQL: numeric or value error: character string buffer too small ORA-06512: at line 8.

Is there any trick to handle or find column name?

Thanks,

Hi Tom,

My procedure code is as follows:

create or replace procedure SP_CCTS_CHANGE_CHECK (p_batch_num in varchar2
,p_eng_chg_num in varchar2
,p_part_num in varchar2
,p_prev_part_num in varchar2
,p_new_cat_seq_num in varchar2
,p_old_cat_seq_num in varchar2
,p_error_desc out varchar2
,p_status_ind out varchar2 )
as
cursor ccts_new_model_cur is
select eng_chg_num
,cat_seq_num
,part_num
,engine_model
,configuration
,serial_num
from ccts_sp_new_model_tbl
where eng_chg_num =p_eng_chg_num
and part_num = p_part_num
and cat_seq_num = p_new_cat_seq_num;
ccts_sp_new_model_tbl_rec ccts_sp_new_model_tbl%rowtype;
cat_seq_num_tbl_rec cat_seq_num_tbl%rowtype;
ccts_sp_tbl_rec ccts_sp_tbl%rowtype;
flag number(1) :=0;
cnt number(10);
v_engine_model varchar2(10);
v_configuration varchar2(10);
v_status_ind varchar2(10);
v_error_desc varchar2(50);
po_review_reason varchar2(1);
begin
loop
-- ********************************************************************************************************
--Checking for nulls and white spaces in old and new cat_seq_num parameters
-- ********************************************************************************************************
if nvl(p_old_cat_seq_num,'null value') != 'null value' or p_old_cat_seq_num = ' ' then
flag := 1;
dbms_output.put_line('one');
p_status_ind := trim('Error');
p_error_desc := trim('Old CSN fou');-- for change';
end if;
exit when flag = 1;
if nvl(p_new_cat_seq_num,'null value') = 'null value' or p_new_cat_seq_num = ' ' then
flag :=1;
p_status_ind := 'Error';
p_error_desc := 'New CSN missing for change';
end if;
exit when flag = 1;
-- ********************************************************************************************************
--Calling the SP_CCTS_CSN_DATA_CHECK procedure
-- ********************************************************************************************************
/*begin
SP_CCTS_CSN_DATA_CHECK (p_batch_num
,p_eng_chg_num
,p_part_num
,p_prev_part_num
,p_new_cat_seq_num
,p_old_cat_seq_num
,'C'
,po_review_reason
,v_error_desc
,v_status_ind);
exception
when others then
dbms_output.put_line('Error while executing the procedure SP_CCTS_CSN_DATA_CHECK ');
flag :=1;
end;
if v_status_ind = 'Error' then
flag :=1;
end if;*/
exit when flag = 1;
-- ****************************************************************************
-- Checking for changes in CCTS_SP_TBL
-- ****************************************************************************
begin
select * into ccts_sp_tbl_rec
from ccts_sp_tbl
where batch_num = p_batch_num
and eng_chg_num = p_eng_chg_num
and part_num = p_part_num
and old_cat_seq_num = p_old_cat_seq_num;
exception
when no_data_found then
flag :=1;
p_status_ind := 'Error';
p_error_desc := 'No Rows Found';
end;
exit when flag = 1;
if substr(ccts_sp_tbl_rec.vendor_part_num,1,1) = '*' then
flag :=1;
p_status_ind := 'Error';
p_error_desc := 'Changing Vendor Part to Space';
end if;
if substr(ccts_sp_tbl_rec.eng_chg_num,1,1) = '*' then
flag :=1;
p_status_ind := 'Error';
p_error_desc := 'Changing EC to Space';
end if;
exit when flag = 1;
-- ****************************************************************************
-- Checking for changes in CCTS_SP_NEW_MODEL_TBL
-- ****************************************************************************
begin
select count(rowid)
into cnt
from ccts_sp_new_model_tbl
where substr(serial_num,1,1) = '*';
if cnt > 0 then
flag :=1;
p_status_ind := 'Error';
p_error_desc := 'Changing new serial to space';
end if;
end;
exit when flag = 1;
begin
select count(rowid)
into cnt
from ccts_sp_new_model_tbl
where substr(eng_chg_num,1,1) = '*';
if cnt > 0 then
flag :=1;
p_status_ind := 'Error';
p_error_desc := 'Changing new engine to space';
end if;
end;
if substr(ccts_sp_tbl_rec.nomenclature,1,1) = '*' then
flag :=1;
p_status_ind := 'Error';
p_error_desc := 'Changing nomenclature to space';
end if;
exit when flag = 1;
if substr(ccts_sp_tbl_rec.part_retention_ind,1,1) = '*' then
flag :=1;
p_status_ind := 'Error';
p_error_desc := 'Changing retention to space';
end if;
if substr(ccts_sp_tbl_rec.design_variant,1,1) = '*' then
flag :=1;
p_status_ind := 'Error';
p_error_desc := 'Changing design variant to space';
end if;
if substr(ccts_sp_tbl_rec.prev_part_num,1,1) = '*' then
flag :=1;
p_status_ind := 'Error';
p_error_desc := 'Changing prev part num to space';
end if;
exit when flag = 1;
if substr(ccts_sp_tbl_rec.old_cat_seq_num,1,1) = '*' then
flag :=1;
p_status_ind := 'Error';
p_error_desc := 'Changing old cat sequence number to space';
end if;
if substr(ccts_sp_tbl_rec.new_cat_seq_num,1,1) = '*' then
flag :=1;
p_status_ind := 'Error';
p_error_desc := 'Changing new cat sequence number to space';
end if;
exit when flag = 1;
-- ****************************************************************************
-- Checking for changes in PART_IP_TBL
-- ****************************************************************************
begin
select count(rowid)
into cnt
from ip_part_tbl
where part_num = p_part_num;
if cnt = 0 then
flag :=1;
p_status_ind := 'Error';
p_error_desc := 'Part not found for change';
end if;
end;
exit when flag = 1;
Update ccts_sp_new_model_tbl
set serial_num = '00000000'
where eng_chg_num = p_eng_chg_num
and part_num = p_part_num
and cat_seq_num = p_old_cat_seq_num
and substr(serial_num,1,1) = '0';
for ccts_new_model_cur_rec in ccts_new_model_cur
loop
begin
select *
into cat_seq_num_tbl_rec
from cat_seq_num_tbl
where part_num = p_part_num
and new_cat_seq_num = p_new_cat_seq_num
and engine_model = ccts_new_model_cur_rec.engine_model
and configuration = ccts_new_model_cur_rec.configuration;
exception
when no_data_found then
dbms_output.put_line('No Data Found');
if cat_seq_num_tbl_rec.supersedure_ind = 'D' then
flag :=1;
p_status_ind := 'Error';
p_error_desc := 'Cat sequence number marked for delete';
end if;
end;
if flag = 1 then
exit;
end if;
v_engine_model := ccts_new_model_cur_rec.engine_model;
v_configuration := ccts_new_model_cur_rec.configuration;
begin
select *
into cat_seq_num_tbl_rec
from cat_seq_num_tbl
where part_num = p_part_num
and cat_seq_num = p_new_cat_seq_num
and engine_model = ccts_new_model_cur_rec.engine_model
and configuration = ccts_new_model_cur_rec.configuration;
exception
when no_data_found then
begin
SP_CCTS_BASE_MODEL_CHECK(v_engine_model
,v_configuration
,v_status_ind
,v_error_desc);
exception
when others then
dbms_output.put_line('Error encountered while executing procedure SP_CCTS_BASE_MODEL_CHECK');
end;
if v_status_ind = 'Error ' then
p_status_ind := 'Error';
exit;
end if;
begin
select *
into cat_seq_num_tbl_rec
from cat_seq_num_tbl
where part_num = p_part_num
and cat_seq_num = p_new_cat_seq_num
and engine_model = v_engine_model
and configuration = v_configuration;
begin
select *
into ccts_sp_tbl_rec
from ccts_sp_tbl
where batch_num = p_batch_num
and eng_chg_num = p_eng_chg_num
and part_num = p_part_num
and new_cat_seq_num = p_new_cat_seq_num
and old_cat_seq_num = p_old_cat_seq_num
and prev_part_num = p_prev_part_num;
-- ********************************************************************************************************
--Delete the row from ccts_sp_tbl where eng_chg_num ,part_num, prev_part_num
--new_cat_seq_num and old_cat_seq_num matches the parameters passed to this procedure
-- ********************************************************************************************************
delete ccts_sp_tbl
where batch_num = p_batch_num
and eng_chg_num = p_eng_chg_num
and part_num = p_part_num
and new_cat_seq_num = p_new_cat_seq_num
and old_cat_seq_num = p_old_cat_seq_num
and prev_part_num = p_prev_part_num;
-- ********************************************************************************************************
--Delete any related rows from ccts_sp_old_model_tbl
-- ********************************************************************************************************
delete ccts_sp_old_model_tbl
where eng_chg_num = p_eng_chg_num
and part_num = p_part_num
and cat_seq_num = p_new_cat_seq_num;
-- ********************************************************************************************************
--insert rows into ccts_sp_tbl with the Activity_code='A' and spaces to old_cat_seq_num,
--prev_part_num and part_retention_ind
-- ********************************************************************************************************
insert into ccts_sp_tbl(eng_chg_num
, activity_code
, part_num
,new_cat_seq_num
, old_cat_seq_num
,prev_part_num
,drpc
,mrpc
,prime_supplier
,vendor_part_num
,nomenclature
,service_bulletin
,spareability_code
,indentation
,interchange_code
,delete_ind
,part_retention_ind
,design_variant
,prev_design_variant
,batch_num
,alt_part_ind
,peculiar_part_code
,invalid_factors_code
,reject_sent_ind
,mult_prev_design_variant
,item_variant_remarks
,application_code
,review_reason_code
,units_per_assembly )
values (ccts_sp_tbl_rec.eng_chg_num
,'A'
,ccts_sp_tbl_rec.part_num
,ccts_sp_tbl_rec.new_cat_seq_num
,' ',' '
,ccts_sp_tbl_rec.drpc
,ccts_sp_tbl_rec.mrpc
,ccts_sp_tbl_rec.prime_supplier
,ccts_sp_tbl_rec.vendor_part_num
,ccts_sp_tbl_rec. nomenclature
,ccts_sp_tbl_rec.service_bulletin
,ccts_sp_tbl_rec.spareability_code
,ccts_sp_tbl_rec.indentation
,ccts_sp_tbl_rec.interchange_code
,ccts_sp_tbl_rec.delete_ind
,' '
,ccts_sp_tbl_rec.design_variant
,ccts_sp_tbl_rec.prev_design_variant
,ccts_sp_tbl_rec.batch_num
,ccts_sp_tbl_rec.alt_part_ind
,ccts_sp_tbl_rec.peculiar_part_code
,ccts_sp_tbl_rec.invalid_factors_code
,ccts_sp_tbl_rec.reject_sent_ind
,ccts_sp_tbl_rec.mult_prev_design_variant
,ccts_sp_tbl_rec.item_variant_remarks
,ccts_sp_tbl_rec.application_code
,ccts_sp_tbl_rec.review_reason_code
,ccts_sp_tbl_rec.units_per_assembly );
-- ********************************************************************************************************
--insert rows into ccts_sp_tbl with the Activity_code='D', part_num =prev_part_num
--delete_ind = 'I' and spaces to prev_part_num
-- ********************************************************************************************************
insert into ccts_sp_tbl(eng_chg_num
, activity_code
, part_num
,new_cat_seq_num
, old_cat_seq_num
,prev_part_num
,drpc
,mrpc
,prime_supplier
,vendor_part_num
,nomenclature
,service_bulletin
,spareability_code
,indentation
,interchange_code
,delete_ind
,part_retention_ind
,design_variant
,prev_design_variant
,batch_num
,alt_part_ind
,peculiar_part_code
,invalid_factors_code
,reject_sent_ind
,mult_prev_design_variant
,item_variant_remarks
,application_code
,review_reason_code
,units_per_assembly )
values (ccts_sp_tbl_rec.eng_chg_num
,'D'
,ccts_sp_tbl_rec.prev_part_num
,ccts_sp_tbl_rec.new_cat_seq_num
,ccts_sp_tbl_rec.old_cat_seq_num
,' '
,ccts_sp_tbl_rec.drpc
,ccts_sp_tbl_rec.mrpc
,ccts_sp_tbl_rec.prime_supplier
,ccts_sp_tbl_rec.vendor_part_num
,ccts_sp_tbl_rec. nomenclature
,ccts_sp_tbl_rec.service_bulletin
,ccts_sp_tbl_rec.spareability_code
,ccts_sp_tbl_rec.indentation
,ccts_sp_tbl_rec.interchange_code
,'I'
,ccts_sp_tbl_rec.part_retention_ind
,ccts_sp_tbl_rec.design_variant
,ccts_sp_tbl_rec.prev_design_variant
,ccts_sp_tbl_rec.batch_num
,ccts_sp_tbl_rec.alt_part_ind
,ccts_sp_tbl_rec.peculiar_part_code
,ccts_sp_tbl_rec.invalid_factors_code
,ccts_sp_tbl_rec.reject_sent_ind
,ccts_sp_tbl_rec.mult_prev_design_variant
,ccts_sp_tbl_rec.item_variant_remarks
,ccts_sp_tbl_rec.application_code
,ccts_sp_tbl_rec.review_reason_code
,ccts_sp_tbl_rec.units_per_assembly );
-- ********************************************************************************************************
--insert rows into ccts_sp_old_model_tbl with the Activity_code='A' and spaces to old_cat_seq_num,
--prev_part_num and part_retention_ind
-- ********************************************************************************************************
insert into ccts_sp_old_model_tbl (eng_chg_num
,cat_seq_num
,part_num
,engine_model
,configuration
,serial_num )
values( ccts_sp_tbl_rec.eng_chg_num
,ccts_sp_tbl_rec.new_cat_seq_num
,ccts_sp_tbl_rec.prev_part_num
,v_engine_model
,v_configuration
,' ' );
exception
when no_data_found then
dbms_output.put_line('Data not found');
end;
exception
when no_data_found then
p_status_ind := 'Error';
p_error_desc := 'Part number, Engine model and Cat sequence number not found';
end;
end;
end loop;
exit when flag = 1;
end loop;
end;

when i'm trying to exceute this procedure it is giving the
declare
*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error: character string buffer too small
ORA-06512: at "IAE_IP.SP_CCTS_CHANGE_CHECK", line 40
ORA-06512: at line 5

when i changed the
p_error_desc := trim('Old CSN found for change'); to
p_error_desc := trim('Old CSN fo');--und for change');
then it is working fine.

Can you please tell me whats going wrong here..its really urgent..

I'm getting ORA-06502: in following scenario when using 10g

Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod
PL/SQL Release 10.2.0.1.0 - Production
CORE 10.2.0.1.0 Production
TNS for 32-bit Windows: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production

db_block_size : 8192

NLS_LENGTH_SEMANTICS - BYTE


Script:

CREATE TABLE char_test
(
char_column CHAR(1) DEFAULT 'N' NOT NULL ENABLE
)

insert into char_test
select SECONDARY from all_objects
where rownum < 100

commit

declare
--ls_char_column char_test.char_column%type;
-- ls_char_column varchar2(4000);
ls_char_column varchar2(3999);
begin
select max(char_column)
into ls_char_column
from char_test
where rownum <= 10;
end;

ORA-06502: PL/SQL: numeric or value error: character string buffer too small
ORA-06512: at line 6

If I change it to varchar(4000) ,it gets completed successfully.

If I do not use max then also it works.

declare
ls_char_column char(1);
begin
select char_column
into ls_char_column
from char_test
where rownum = 1;
end;

looks like a bug in my opinion, here is a more comprehensive test case you may submit to support to work this out.

the for loop shows that plsql is making this a 4000 char - but regular sql (via the view) knows "char 1"

ops$tkyte@ORA10GR2> CREATE TABLE char_test
  2     (
  3      char_column CHAR(1) DEFAULT 'N' NOT NULL ENABLE
  4     )
  5  /

Table created.

ops$tkyte@ORA10GR2>   insert into char_test
  2    select SECONDARY from all_objects
  3    where rownum < 100
  4  /

99 rows created.

ops$tkyte@ORA10GR2>  commit;

Commit complete.

ops$tkyte@ORA10GR2>
ops$tkyte@ORA10GR2>
ops$tkyte@ORA10GR2> declare
  2     ls_char_column varchar2(3999);
  3  begin
  4    for x in (
  5    select max(char_column) max_char_column
  6      from char_test
  7     where rownum <= 10 )
  8    loop
  9          dbms_output.put_line( 'length = ' || length(x.max_char_column) );
 10    end loop;
 11  end;
 12  /
length = 4000

PL/SQL procedure successfully completed.

ops$tkyte@ORA10GR2>
ops$tkyte@ORA10GR2> declare
  2     ls_char_column varchar2(3999);
  3  begin
  4    select max(char_column)
  5      into ls_char_column
  6      from char_test
  7     where rownum <= 10;
  8  end;
  9  /
declare
*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error: character string buffer too small
ORA-06512: at line 4


ops$tkyte@ORA10GR2>
ops$tkyte@ORA10GR2> create or replace view testv
  2  as
  3  select max(char_column) max_char_column, char_column
  4    from char_test
  5   group by char_column;

View created.

ops$tkyte@ORA10GR2>
ops$tkyte@ORA10GR2> desc testv;
 Name                                     Null?    Type
 ---------------------------------------- -------- ----------------------------
 MAX_CHAR_COLUMN                                   CHAR(1)
 CHAR_COLUMN                              NOT NULL CHAR(1)

ops$tkyte@ORA10GR2>

 

Hi Tom,

I want to parse a string of >32k size. Therefore I'm using 
dbms_sql.varchar2s. But it gives numeric or value error as following. I thought every element of dbms_sql.varchar2s eg. l_stmt(1), l_stmt(2) can be of maximum 32k in size. The size  of p_query is only 463, but its still giving error.

SQL > create or replace procedure proc1 (p_query in varchar2, p_seqno_count out number)
  2  as
  3   l_stmt           dbms_sql.varchar2s;
  4   l_cursor         integer default dbms_sql.open_cursor;
  5   l_rows           number  default 0;
  6   f_col1   number;
  7   l_adsc_query  varchar2(32000);
  8   n    number;
  9  begin
 10   n:= 1;
 11   l_stmt(1)  := 'select count(*)  from (';
 12   n:= n+1;
 13   dbms_output.put_line('length(p_query)='||length(p_query));
 14   l_stmt(2) :=  p_query;
 15   n:= n+1;
 16   for i in 0 .. 10
 17   loop
 18  
 19    l_stmt(n) := ' intersect ';
 20    n:= n+1;
 21  
 22    select adsc_query into l_adsc_query
 23    from advsearch_cache
 24    where adsc_filter_number = i;
 25    l_stmt(n) := l_adsc_query ;
 26  
 27    n:= n+1;
 28  
 29   end loop;
 30   l_stmt(n) := ' ) ';
 31   dbms_sql.parse( c             => l_cursor,
 32     statement     => l_stmt,
 33     lb            => l_stmt.first,
 34     ub            => l_stmt.last,
 35     lfflg         => true,
 36     language_flag => dbms_sql.native );
 37   dbms_sql.define_column(l_cursor, 1, f_col1);
 38   l_rows := dbms_sql.execute(l_cursor);
 39   if dbms_sql.fetch_rows(l_cursor)=0 then
 40    null;
 41   end if;
 42   dbms_sql.column_value(l_cursor, 1, f_col1);
 43   dbms_sql.close_cursor( l_cursor );
 44  
 45   p_seqno_count := f_col1;
 46  end ;
 47  /

Procedure created.

SQL> declare
  2  p_query         VARCHAR2(32000) := 'select distinct vend_seqno from vendors, vendor_index, vendor_qualifications where 1 = 1 and (vend_date
  3  between to_date(''03-Apr-1996'') and to_date(''19-Apr-2006'')) and (vndx_vend_seqno=vend_seqno) and ((lower(VNDX_NAMESEG)
  4  between ''a'' and ''f'') or (lower(VEND_VENDOR_NAME2) between ''a'' and ''f'')) and (vnqu_vend_seqno=vend_seqno AND
  5  vnqu_vtyp_type=''QUALS OTHER'' AND vnqu_vtyv_value_code=''BIDDING'') and (lower(vnqu_qualification_number) between ''100000''
  6  and ''250000'')';
  7  
  8  p_seqno_count   NUMBER;
  9  begin
 10   proc1(p_query,p_seqno_count);
 11   dbms_output.put_line('p_seqno_count='||p_seqno_count);
 12  end;
 13  /
length(p_query)=463
declare
*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error: character string buffer too small
ORA-06512: at "BPST420.PROC1", line 14
ORA-06512: at line 10


Please advise. 

Tom,

We migrated an application from 8i to 9i and when I run several procedures in 9iAS it sometimes gives me this error.

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

There is no code in line 10 in th program. Sometimes the procedure runs fine and sometimes it does not.

Could this be coming from a value in the HTTP header or the DAD configuration or duplicate DADs. I noticed a large value sometimes gets asssigned for HTTP_COOKIE even though I am not using any cookies in the procedure.

2. When we call the page we all it at the default port for 9iAS which 7778. However the debug error page shows SERVER_PORT=7777. DBA says this is web cache port.
Is it normal for the error page to show port 7777 (web cache port)

Tom:

Here is the app server config. Note I am not using any cookies and it is setting an HTTP_COOKIE value. Could this be because of the stateful/stateless mode.


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

DAD name: ipmp
PROCEDURE : new_trans
URL : </code> http://ts1.att.org:7777/pls/ipmp/new_trans?i_userid=john <code>
PARAMETERS :
===========
i_userid:
john

ENVIRONMENT:
============
PLSQL_GATEWAY=WebDb
GATEWAY_IVERSION=2
SERVER_SOFTWARE=Oracle HTTP Server/1.3.22 (Unix) mod_plsql/9.0.2.0.0 DAV/1.0.2 (OraDAV enabled) mod_oc4j/3.0 mod_ossl/9.0.2.0.0 mod_fastcgi/2.2.10 mod_perl/1.26 mod_oprocmgr/1.0
GATEWAY_INTERFACE=CGI/1.1
SERVER_PORT=7777
SERVER_NAME=ts1.att.org
REQUEST_METHOD=GET
QUERY_STRING=i_userid=john
PATH_INFO=/new_trans
SCRIPT_NAME=/pls/ipmp
REMOTE_HOST=
REMOTE_ADDR=65.108.258.98
SERVER_PROTOCOL=HTTP/1.1
REQUEST_PROTOCOL=HTTP
REMOTE_USER=
HTTP_CONTENT_LENGTH=
HTTP_CONTENT_TYPE=
HTTP_USER_AGENT=Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.1; SV1; .NET CLR 1.0.3705; InfoPath.1; .NET CLR 2.0.50727)
HTTP_HOST=ts1.att.org:7778
HTTP_ACCEPT=*/*
HTTP_ACCEPT_ENCODING=gzip, deflate
HTTP_ACCEPT_LANGUAGE=en-us
HTTP_ACCEPT_CHARSET=
HTTP_COOKIE=ForeseeLoyalty_MID_MsF0ld0M1Z=3; s_sess=%20s_cc%3Dtrue%3B%20s_sq%3D%3B
HTTP_IF_MODIFIED_SINCE=
HTTP_REFERER=
HTTP_SOAPACTION=
HTTP_AUTHORIZATION=
WEB_AUTHENT_PREFIX=
DAD_NAME=ipmp
DOC_ACCESS_PATH=
DOCUMENT_TABLE=
PATH_ALIAS=
REQUEST_CHARSET=WE8MSWIN1252
REQUEST_IANA_CHARSET=WINDOWS-1252
SCRIPT_PREFIX=/pls
HTTP_IF_MATCH=
HTTP_CACHE_CONTROL=
SOAP_BODY=
HTTP_X_ORACLE_DEVICE_CLASS=
HTTP_X_ORACLE_DEVICE_ORIENTATION=
HTTP_X_ORACLE_DEVICE_MAXDOCSIZE=
HTTP_X_ORACLE_DEVICE=
HTTP_X_ORACLE_ORIG_ACCEPT=
HTTP_X_ORACLE_ORIG_USER_AGENT=
HTTP_X_ORACLE_USER_LOCALE=
HTTP_X_ORACLE_USER_NAME=
HTTP_X_ORACLE_USER_DISPLAYNAME=
HTTP_X_ORACLE_USER_USERKIND=
HTTP_X_ORACLE_USER_AUTHKIND=
HTTP_X_ORACLE_USER_DEVICEID=
HTTP_X_ORACLE_USER_LOCATION_ADDRESSLINE1=
HTTP_X_ORACLE_USER_LOCATION_ADDRESSLINE2=
HTTP_X_ORACLE_USER_LOCATION_ADDRESSLASTLINE=
HTTP_X_ORACLE_USER_LOCATION_BLOCK=
HTTP_X_ORACLE_USER_LOCATION_CITY=
HTTP_X_ORACLE_USER_LOCATION_COMPANYNAME=
HTTP_X_ORACLE_USER_LOCATION_COUNTY=
HTTP_X_ORACLE_USER_LOCATION_STATE=
HTTP_X_ORACLE_USER_LOCATION_POSTALCODE=
HTTP_X_ORACLE_USER_LOCATION_POSTALCODEEXT=
HTTP_X_ORACLE_USER_LOCATION_COUNTRY=
HTTP_X_ORACLE_USER_LOCATION_TYPE=
HTTP_X_ORACLE_USER_LOCATION_X=
HTTP_X_ORACLE_USER_LOCATION_Y=
HTTP_X_ORACLE_SERVICE_HOME_URL=
HTTP_X_ORACLE_SERVICE_PARENT_URL=
HTTP_X_ORACLE_HOME_URL=
HTTP_X_ORACLE_MODULE_CALLBACK_URL=
HTTP_X_ORACLE_MODULE_CALLBACK_LABEL=
HTTP_X_ORACLE_CACHE_USER=
HTTP_X_ORACLE_CACHE_SUBID=
HTTP_X_ORACLE_CACHE_AUTH=
HTTP_X_ORACLE_CACHE_DEVICE=
HTTP_X_ORACLE_CACHE_LANG=
HTTP_X_ORACLE_CACHE_ENCRYPT=

Here is the code:

PROCEDURE NEW_TRANS (
i_userid VARCHAR2 DEFAULT NULL )

AS

ve_cursor integer;
po_cursor integer;
sh_cursor integer;
po_cursor integer;
st_cursor integer;
de_cursor integer;
org_cursor integer;

v_contact contact.contactid%type;
v_orgn contact.orgn%type;
v_firstname contact.firstname%type;
v_lastname contact.lastname%type;
cnt integer;
i integer;


BEGIN

SELECT contactid INTO v_contactid FROM contact WHERE
user_id=i_userid ;

SELECT trim(orgn) into v_orgn FROM contact
WHERE contactid=v_contactid;

htp.p(' <HTML>');
htp.p(' <HEAD>');
htp.p('<SCRIPT LANGUAGE="JavaScript 1.2" TYPE="Text/JavaScript"> ');
htp.p('function doSubmit()');
htp.p('{');
htp.p('document.trans_form.submit();');
htp.p('}');


htp.p('<TR>');
htp.p('<TH align=right> PO Number:&nbsp</TH>');
htp.p('<TD colspan=3>');

htp.formText('i_po_number',14,14,null);
htp.p('</TD>');
htp.p('</TR>');

htp.p('<TR>');
htp.p('<TH align=right> Vendor:&nbsp</TH>');
htp.p('<TD colspan=3>');
ve_cursor:=owa_util.bind_variables('select null,''Pick One -->'',''x'',1 from dual
union all
select vencd,name,null,2 from vendor order by 4,2');
owa_util.listprint(ve_cursor,'i_vendor_code',null,null);
dbms_sql.close_cursor(ve_cursor);
htp.p('</TD>');
htp.p('</TR>');

htp.p(' </CENTER>');
htp.p(' </FORM>');
htp.p(' </BODY>');
htp.p('');
htp.p('</HTML>');


END;

 Tom:

The strange thing is that many procedures report the same error at Line 10. Them sometimes procedure work and sometimes they do not.

Can this error be coming from database or DAD configuration or it is only application issue? Can 9i be treating integers/numbers data types differently.

 1  select * from user_source where
  2* name='NEW_TRANS' and line=10


SQL> /

NAME                           TYPE               LINE
------------------------------ ------------ ----------
TEXT
--------------------------------------------------------------------------------
NEW_TRANS                    PROCEDURE            10
  st_cursor          integer;
 

Tom:

I do not understand. Anoynomous block would not have a procedure name?

This is the old machine/database.

8iSQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle8i Enterprise Edition Release 8.1.7.2.0 - 64bit Production
PL/SQL Release 8.1.7.2.0 - Production
CORE    8.1.7.0.0       Production
TNS for IBM/AIX RISC System/6000: Version 8.1.7.2.0 - Production
MSSRTL Version 3.4.1.0.0 - Production

This is the new machine/database

9iSQL> select * from v$version;
BANNER
----------------------------------------------------------------
Oracle9i Enterprise Edition Release 9.2.0.6.0 - 64bit Production
PL/SQL Release 9.2.0.6.0 - Production
CORE    9.2.0.6.0       Production
TNS for IBM/AIX RISC System/6000: Version 9.2.0.6.0 - Production
MSSRTL Version 9.2.0.6.0 - Production

Is this what you are asking for, 

right, do you SEE a procedure name in the error stack?? I do not.

sort of like this, you are trying to run a procedure "prt" for example.  the plsql gateway stuff creates an anonymous block to run it.  So, in order to run your "prt" routine, they create some PLSQL, set up the cgi-environment, do other stuff and finally run your routine and then get the output from it.  sort of like this "concept" piece of code:

ops$tkyte%ORA10GR2> declare
  2          x number(1);
  3          y number;
  4          z number;
  5  begin
  6
  7          y := 11;
  8          z := 11;
  9
 10          x := 11;
 11          prt( 'hello' );
 12  end;
 13  /
declare
*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error: number precision too large
ORA-06512: at line 10
 


so, I get an error stack that looks a lot like yours.


I'm not interested in the database version, but the middle tier.   I've a feeling you are blowing up their anonymous block. 

Tom:

Can the problem be related to the database connect string in the DAD.conf.
We are using the SID format.

1. We have a TNS service name on the new 9i machine called "ipmp" that is connecting to the old "ipmp" 8i database on the old machine. However the current 9i SID/database name is also "ipmp". Do you think this may be related to that error.

2. What is the difference between "SErvice Name Format" and "TNSFormat" and "NetServiceNameFOrmat"?
-------------------------------------------------------------------------------
<Location /pls/ipmp>
SetHandler pls_handler
Order deny,allow
Allow from all
AllowOverride None
PlsqlDatabaseUsername devadmin
PlsqlDatabasePassword !rr1zYWRtaW4=
PlsqlDatabaseConnectString itt1.att.org:1521:ipmp
PlsqlAuthenticationMode Basic
PlsqlErrorStyle DebugStyle
PlsqlSessionStateManagement StatelessWithResetPackageState
</Location>

----------------------- PlsqlDatabaseConnectString ---------------------------

Specifies the connect information to an Oracle database.

Syntax: PlsqlDatabaseConnectString [string]
[ServiceNameFormat/SIDFormat/TNSFormat/NetServiceNameFormat]

where [string] can be in one of the following based on the second argument

ServiceNameFormat : HOST:PORT:SERVICE_NAME format where
HOST is the hostname running the database,
PORT is the port number the TNS listener is listening,
SERVICE_NAME is the database service name
SIDFormat : HOST:PORT:SID format where
HOST is the hostname running the database,
PORT is the port number the TNS listener is listening,
SID is the database sid
TNSFormat : A valid TNS alias which resolves using Net8 utilities like
tnsping and SQL*Plus
NetServiceNameFormat : A valid net service name that resolves to a
connect descriptor. A connect descriptor is a specially
formatted description of the destination for a network
connection. A connect descriptor contains destination service
and network route information.

If the format argument is not specified, then mod_plsql assumes that 'string'
is either in the HOST:PORT:SID format, or resolvable by Net8. The
differentiation between the two is made by the presence of colon in the
specified string.

It is recommended that newer DAD's do not use the SIDFormat syntax. This
exists only for backward compatibility reasons. Please use the new two
argument format for newly created DADs.

Default: [none]

Example:
PlsqlDatabaseConnectString myhost.com:1521:myhost.iasdb.inst ServiceNameFormat
or
PlsqlDatabaseConnectString myhost.com:1521:iasdb SIDFormat
or
PlsqlDatabaseConnectString mytns_entry TNSFormat
or
PlsqlDatabaseConnectString cn=oracle,cn=iasdb NetServiceNameFormat
or
PlsqlDatabaseConnectString (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(Host=myhost.com)(Port= 1521))(CONNECT_DATA=(SID=iasdb))) TNSFormat
or
PlsqlDatabaseConnectString myhost_tns
or
PlsqlDatabaseConnectString myhost.com:1521:iasdb

Tom:

As I read the whole column I realized that one person reported exactly the same issue.

He did not even has 10 lines of code in his program.

January 13, 2005
Reviewer: Anu from San Antonio, TX USA

Did you knw if that was caused by an oracle 9i bug?

2. Would setting blank_trimming to true help?

Tom:

1. What do you mean on whether I see a name in the error stack? How do you determine this.

2. What you say makes sense and is probably what is happening.
<the plsql gateway stuff creates an anonymous block to run it. So, in order to run
your "prt" routine, they create some PLSQL, set up the cgi-environment, do other
stuff and finally run your routine and then get the output from it. sort of
like this "concept" piece of code>

How do find that piece of code and fix it?

3. Does not this tell the middle tier version?

SERVER_SOFTWARE=Oracle HTTP Server/1.3.22 (Unix) mod_plsql/9.0.2.0.0 DAV/1.0.2 (OraDAV enabled) mod_oc4j/3.0 mod_ossl/9.0.2.0.0 mod_fastcgi/2.2.10 mod_perl/1.26 mod_oprocmgr/1.0
GATEWAY_INTERFACE=CGI/1.1

4. What query you exactly run on V$sql to see what you need.

Wed, 30 Aug 2006 15:56:57 GMT

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

DAD name: ipmp
PROCEDURE : new_trans
URL : </code> http://xxx.xx.xxx:7777/pls/ipmp/new_trans?i_userid=john <code>
PARAMETERS :
===========
i_userid:
john

This is the V$user_source for line 10.
-----------------
NAME : NEW_TRANS
TYPE : PROCEDURE
LINE : 10
TEXT : st_cursor integer;

-----------------

1) i read your error stack.  it is from an anonymous block, the value error is not being raised in YOUR procedure, else it would appear in the call stack.

See, if it was in your procedure, we'd see YOU in the call stack, like this:

ops$tkyte%ORA10GR2> declare
  2          x number(1);
  3  begin
  4          x := 11;
  5          p;
  6  end;
  7  /
declare
*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error: number precision too large
ORA-06512: at line 4


ops$tkyte%ORA10GR2> declare
  2          x number(1);
  3  begin
  4          x := 1;
  5          p;
  6  end;
  7  /
declare
*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error: number precision too large
ORA-06512: at "OPS$TKYTE.P", line 5
ORA-06512: at line 5

2) look in v$sql for it.

Not going to promise you can fix it, but you can understand it.


3) not entirely, tell me what you have installed. (actually, I hope you have a tar open???)


4) look for some sql text that includes a call to your procedure in it, that would be the block they generate. 

Tom:

I copied the procedure that was giving me an error and copied to another instance that runs 9i fine on same machine. It ran fine. They both use the same web server but different DADs

I looked at the environment variables set for each and found that the one that works has
HTTP_REFERER=blank

while the other one causing the problem had
HTTTP_REFERER=</code> http://xxx.xxx.xx:7778/pls/ipmp/new_trans <code>

Do you think that is causing the problem? if yes how do you trun it off.

Tom:

V$sql has a lot of columns and so over 1000 records. I am not sure what query you want me to write. However, is this what you want to see:

SQL> select sql_text from v$sql where sql_text like '%new_trans%';

SQL_TEXT
--------------------------------------------------------------------------------
select sql_text from v$sql where sql_text like '%new_trans%'
declare  rc__ number; begin  owa.init_cgi_env(:n__,:nm__,:v__);  htp.HTBUF_LEN :
= 255;  null;  null;  null;  null;  new_trans(i_user_id=>:i_user_id);  if (wpg
_docload.is_file_download) then   rc__ := 1;   wpg_docload.get_download_file(:do
c_info);    null;   null;   null;   commit;  else   rc__ := 0;    null;   null;
  null;   commit;   owa.get_page(:data__,:ndata__);  end if;  :rc__ := rc__; end
;

declare  rc__ number; begin  owa.init_cgi_env(:n__,:nm__,:v__);  htp.HTBUF_LEN :
= 255;  null;  null;  null;  null;  parts_trans.new_trans(i_user_id=>:i_us
er_id);  if (wpg_docload.is_file_download) then   rc__ := 1;   wpg_docload.get_d
ownload_file(:doc_info);    null;   null;   null;   commit;  else   rc__ := 0;
  null;   null;   null;   commit;   owa.get_page(:data__,:ndata__);  end if;  :r
c__ := rc__; end;


3 rows selected.
 

Tom:

Does than mean I do not have access to this view.


SQL> desc v$sql_text_with_newlines;
ERROR:
ORA-04043: object v$sql_text_with_newlines does not exist

2. What query you run on this to give you what you need to look at? 

Tom:

IS this what you need?

  
SQL>  select * from V$SQLTEXT_WITH_NEWLINES where 
  2            address='070000000E3087F0' and hash_value=1326104259
  3  /

ADDRESS          HASH_VALUE COMMAND_TYPE      PIECE
---------------- ---------- ------------ ----------
SQL_TEXT
----------------------------------------------------------------
070000000E3087F0 1326104259           47          6
c__ := rc__;
end;

070000000E3087F0 1326104259           47          5
  null;
  commit;
  owa.get_page(:data__,:ndata__);
 end if;
 :r

070000000E3087F0 1326104259           47          4
;
  null;
  null;
  commit;
 else
  rc__ := 0;
   null;
  null;

ADDRESS          HASH_VALUE COMMAND_TYPE      PIECE
---------------- ---------- ------------ ----------
SQL_TEXT
----------------------------------------------------------------

070000000E3087F0 1326104259           47          3
  rc__ := 1;
  wpg_docload.get_download_file(:doc_info);
   null

070000000E3087F0 1326104259           47          2
i_user_id=>:i_user_id);
 if (wpg_docload.is_file_download) then

070000000E3087F0 1326104259           47          1
 htp.HTBUF_LEN := 255;
 null;
 null;
 null;
 null;
 new_trans(

070000000E3087F0 1326104259           47          0
declare

ADDRESS          HASH_VALUE COMMAND_TYPE      PIECE
---------------- ---------- ------------ ----------
SQL_TEXT
----------------------------------------------------------------
 rc__ number;
begin
 owa.init_cgi_env(:n__,:nm__,:v__);


7 rows selected. 

Tom:

If I read this correctly it looks like Line 10 is either null or the broken peice of the url call? 

 1  select sql_text from V$SQLTEXT_WITH_NEWLINES where
  2                address='070000000E3087F0' and hash_value=1326104259
  3* order by piece
SQL> /

SQL_TEXT
----------------------------------------------------------------
declare
 rc__ number;
begin
 owa.init_cgi_env(:n__,:nm__,:v__);

 htp.HTBUF_LEN := 255;
 null;
 null;
 null;
 null;
 new_trans(

i_user_id=>:i_user_id);
 if (wpg_docload.is_file_download) then

  rc__ := 1;
  wpg_docload.get_download_file(:doc_info);
   null

;
  null;
  null;

SQL_TEXT
----------------------------------------------------------------
  commit;
 else
  rc__ := 0;
   null;
  null;

  null;
  commit;
  owa.get_page(:data__,:ndata__);
 end if;
 :r

c__ := rc__;
end;


7 rows selected. 

ok, it is likely the CALL to new_trans.

Now, what data type does NEW_TRANS expect as input?  (and what type did you send it??)


perhaps you have it as a number and you "accidently" sent it a string?

ops$tkyte%ORA10GR2> create or replace procedure new_trans( p_id in number default null )
  2  as
  3  begin
  4  null;
  5  end;
  6  /

Procedure created.

ops$tkyte%ORA10GR2> var x varchar2(5);
ops$tkyte%ORA10GR2> exec new_trans(:x);

PL/SQL procedure successfully completed.

ops$tkyte%ORA10GR2> exec :x := '  '

PL/SQL procedure successfully completed.

ops$tkyte%ORA10GR2> exec new_trans(:x)
BEGIN new_trans(:x); END;

*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error: character to number conversion
error
ORA-06512: at line 1


I would recommend using ONLY varchar2 IN types for the top level web procedures (those invoked by mod_plsql) and doing explicit conversions that you can CATCH yourself in your code.

mod_plsql only binds strings, period. 

Tom:

The code is listed above. I will list the header again. I never use type number for input parameters.

PROCEDURE NEW_TRANS (
i_user_id VARCHAR2 DEFAULT NULL )

Can this be an internal bug in mod_plsql?

Tom:

yes i have a test environment (application/database). But I did not understand what you want done. Can you explain more on what queries need to be done.

Tom:

It seems it is a common problem wiht 9.2.0.6 databases and oacle has a patch for it. If you have access to metalink (i am sure you do) you will see it.

Symptoms
- A modplsql application fails with "Page not found"
- The database the DAD connects to is 9.2.0.6
- The database is single-byte and not multi-byte
- Error in the $ORACLE_HOME/Apache/Apache/logs/error_log is:
[Thu May 05 15:35:32 2005] [error] mod_plsql: /pls/iviewsdev/FWIVHORIZON.fwwp_task.get_sub_menu
HTTP-404 ORA-06502: PL/SQL: numeric or value error
- This application works fine where the RDBMS is 9.2.0.5


Cause
Bug 4015165 REGRN:SCALAR VARCHAR2 IN BINDS WITH DIFFERENT SIZE RANDOMLY FAILS WITH ORA-06502

This bug was introduced in 9.2.0.6, and even though the bug mentions this problem is caused with multibyte character sets, it also causes problems with single byte character set databases.
This problem can occur with Oracle Application Server or with the HTTP Server that ships with RDBMS.

Solution
To implement the solution, execute the following steps:

1. Download patch Patch 4015165 REGRN:SCALAR VARCHAR2 IN BINDS WITH DIFFERENT SIZE RANDOMLY FAILS WITH
ORA-06502 from Metalink, and apply as per the patch README
2. Test the application

do you agree?

I have created a simple example to duplicate the same error message. The same statement works fine with our 9i instance, but it fails in our new 19g instance (10.2.0.1.0).
Could this be a 10g related issue? Or certain parameters are not set up right in the session or database?

I would really appreciate if you would try it in your 10g instance to see if the same error occurs or not.

Thanks ahead.

----------------------------------------------------------
declare
v_flag varchar2(1);
BEGIN
SELECT max('A')
into v_flag
FROM DUAL;
exception
when no_data_found then
v_flag := 'N';

ORA-06502: PL/SQL: numeric or value error: character string buffer too small
ORA-06512: at line 4

ops$tkyte%ORA10GR2> declare
  2          x varchar2(1);
  3  begin
  4          select max('A') into x from dual;
  5  end;
  6  /

PL/SQL procedure successfully completed.


10.2.0.2

 

It is a bug in certain versions. Here is the information from MetaLink.

Symptoms
The following errors occur when you use MAX and MIN Functions with CHAR Variables.

ORA-06502 : PL/SQL: numeric or value error: character string buffer too small
ORA-06512 : at line %s

These errors will occur in 10.1.0.4.0 and 10.2.0.1.0 Database Versions only.

However the code will work well in other releases of Database Versions.

Cause
The errors occur because of : Bug 4458790

An example testcase is :

create table my_test(rating char(2));
insert into my_test values ('A1');
insert into my_test values ('A2');
commit;

declare
min_rating my_test.rating%type;
max_rating my_test.rating%type;
begin
select min(rating),max(rating)
into min_rating,max_rating
from my_test;
end;
/

ERROR at line 1:
ORA-06502 : PL/SQL: numeric or value error: character string buffer too small
ORA-06512 : at line 6

Solution
Bug 4458790 is fixed in 11.0 Version of Database.
@ Backport Patches are available for 10.1.0.4.0 on Linux x86, Solaris (SPARC 64-bit) and 10.2.0.1.0 on AIX5L Based
@ Systems

However there are 3 workarounds for this problem. The last 2 workarounds involve changing the Code whereas the first workaround requires the modification of an spfile parameter:

Workaround 1:
==========

Set the initialization parameter blank_trimming to true. Please refer Note 249664.1 for more help on how to change the initialization parameters in spfile or pfile.

Note 249664.1 for more help on how to change the initialization parameters in spfile or pfile.

Workaround 2:
==========

We need to use the to_char function before the MIN and MAX sql functions.

For example, we have to modify the above testcase as follows:

declare
min_rating my_test.rating%type;
max_rating my_test.rating%type;
begin
select to_char(min(rating)),to_char(max(rating))
into min_rating,max_rating
from my_test;
end;
/

Workaround 3:
==========

Declare the variables as varchar2(4000) instead of %type.

For example, we have to modify the above testcase as follows:

declare
min_rating varchar2(4000);
max_rating varchar2(4000);
begin
select min(rating),max(rating)
into min_rating,max_rating
from my_test;
end;
/

References
Bug 4458790 - Ora-6502 Happens In Select Min From Char
Note 249664.1 - Pfile vs SPfile

From "METALINK TERMS OF USE":

<quote>
Except for information in Web sites controlled by third parties that are accessible via hyperlinks from MetaLink, the information contained in the Materials is the confidential proprietary information of Oracle. You may not use, disclose, reproduce, transmit, or otherwise copy in any form or by any means the information contained in the Materials for any purpose, other than to support your authorized use of the Oracle Programs for which you hold a supported license from Oracle, without the prior written permission of Oracle.
</quote>

To all, this is Metalink note 314283.1.

Michel

Dear Friends
When I run this script I am getting this message
---
declare
in_file text_io.file_type;
string string long;
begin

BEGIN
in_file := text_io.fopen('C:\HEADER.dat','r');
loop
text_io.get_line(in_file, string);

:IM_TRANS_ISSUE_HEADER.REQ_DOC_CODE := substr(string,1,10);
:IM_TRANS_ISSUE_HEADER.D_S_CODE := LTRIM(RTRIM(substr(string,12,22)));
:IM_TRANS_ISSUE_HEADER.R_S_CODE := substr(string,24,34);
end loop;
exception when no_data_found then
text_io.fclose(in_file);
when others then
show_message(sqlerrm);
END;


---
ORA-06502 PL/SQL NUMERIC OR VALUE ERROR


And the block fields size as value in the block:


REQ_DOC_CODE VARCHAR2(10) DATA BASE FIELD
D_S_CODE VARCHAR2(10) NOT A DATA BASE FIELD R_S_CODE NOT A DATA BASE FIELD

And the actual text that I am trying to load as the following :

102001415 110121 102001

Waiting for your valuable answer

Best regards
jamil alshaibani

hi tom i have one procedure like this,

CREATE OR REPLACE PROCEDURE "SIEBEL_WODATA"
 IS
  --file_id UTL_FILE.FILE_TYPE;
CURSOR siebel_wodata_cur IS
SELECT *
FROM nidapps.siebeldatanid_Vw WHERE
customer_wo_no  NOT IN (SELECT customer_wo_no FROM nidapps.CUSTWORKORDERS)
AND c_circuit_nm IS NOT NULL AND kvh_prod_nm IS NOT NULL
AND kvh_prod_type NOT LIKE 'Data%' AND c_circuit_nm NOT LIKE 'V%'
AND c_circuit_nm NOT LIKE '-' AND kvh_prod_nm NOT LIKE 'IP%Address%' AND kvh_prod_nm NOT LIKE 'Rental%TA%Service%T1'
AND kvh_prod_nm NOT LIKE 'Managed%Router%Service' AND kvh_prod_nm NOT LIKE 'IP%Address%Allocation'
AND customer_wo_no  NOT IN
(SELECT customer_wo_no FROM nidapps.siebeldatanid_Vw
 WHERE wo_type  LIKE 'Disconnect'
 AND customer_Wo_no  NOT LIKE '%D%'
 AND customer_wo_no  NOT LIKE '%-D' );
--  select *
-- from nidapps.siebeldatanid_Vw where
-- customer_wo_no  not in (select customer_wo_no from nidapps.custworkorders)
-- and c_circuit_nm is not null and kvh_prod_nm is not null
--  and kvh_prod_type not like 'Data%' and c_circuit_nm not like 'V%'
-- and c_circuit_nm not like '-' and kvh_prod_nm not like 'IP%Address%'
--  and wo_type not like 'Disconnect' and customer_Wo_no not like '%D%' and customer_wo_no not like '%-D' ;
 TYPE icust_tabarr IS TABLE OF siebeldatanid_vw%ROWTYPE  INDEX BY BINARY_INTEGER;
 icustarr icust_tabarr;
 cust_ver NUMBER :=1;
 insert_count NUMBER := 1;
 i NUMBER := 1;
 BEGIN
 OPEN siebel_wodata_cur;
--file_id := utl_file.FOPEN( 'h:\nidbatchprog', 'ston-wod.log', 'w' );
 LOOP
  EXIT WHEN siebel_wodata_cur%NOTFOUND ;
  FETCH siebel_wodata_cur INTO icustarr(i);
  i := i + 1;
  END LOOP;
     CLOSE siebel_wodata_cur;
 FOR i IN 1..icustarr.last LOOP
 INSERT INTO CUSTWORKORDERS (custworkordeR_id,swo_id,
                            wo_entered_by,wo_entry_dt,kvh_prod_nm,
                            kvh_prod_type,expected_dly_dt,wo_status,
                            wo_type,custworkorder_ver,wo_delay_flg,
                            WO_OPS_STATUS,MISCELANEOUS_INFO,customer_wo_no,
                            actual_dly_dt,
                            BILLING_END_DT)
                        VALUES
                            (custworkorders_seq.NEXTVAL,icustarr(i).swo_id,
                            icustarr(i).wo_entered_by,icustarr(i).wo_entry_date,
                            icustarr(i).kvh_prod_nm||icustarr(i).x_kvh01_bandwidth||icustarr(i).x_kvh03_if_bandwidth,icustarr(i).kvh_prod_type,
                            icustarr(i).expected_dly_dt,icustarr(i).status,
                            icustarr(i).wo_type,1,0,'',icustarr(i).X_KVH03_IF_BANDWIDTH,icustarr(i).customer_wo_no
                            ,icustarr(i).X_KVH01_BILLING_START_DATE,
                            icustarr(i).X_KVH01_BILLING_TERMINATE_DATE);
 COMMIT;
 COMMIT;
--utl_file.PUT_LINE(file_id, 'Hello');
  --utl_file.PUT_LINE( file_id,icustarr(i).CUSTOMER_WO_NO);
 insert_count :=insert_count + 1;
 /*IF insert_count = 1000 THEN
 COMMIT;
 insert_count := 1;
 ELSE
 insert_count := insert_count + 1;
 END IF;*/
 END LOOP;
  --utl_file.PUT_LINE( file_id,'Workorder Data Migration Completed');
 EXCEPTION
  WHEN OTHERS THEN
    NULL;
UPDATE nidapps.CUSTWORKORDERS SET wo_status='WO-Review' WHERE wo_status LIKE 'Confirmation-Started';
COMMIT;
UPDATE nidapps.CUSTWORKORDERS SET wo_status='WO-Review' WHERE wo_status LIKE 'Open';
COMMIT;
UPDATE nidapps.CUSTWORKORDERS SET wo_status='WO-Review' WHERE wo_status LIKE 'Provisioning%';
COMMIT;
-- update nidapps.custworkorders set wo_status='WO-Review' where wo_status like 'Provisioning%';
-- commit;
UPDATE nidapps.CUSTWORKORDERS SET wo_status='WO-Review' WHERE wo_status LIKE 'Provisioning-Started';
COMMIT;
UPDATE nidapps.CUSTWORKORDERS SET wo_status='WO-Review' WHERE wo_status LIKE 'Review-Started';
COMMIT;
UPDATE nidapps.CUSTWORKORDERS SET wo_status='WO-Review' WHERE wo_status LIKE 'Submitted';
COMMIT;
UPDATE nidapps.CUSTWORKORDERS SET wo_status='WO-Review' WHERE wo_status LIKE 'Test-Started';
COMMIT;
UPDATE nidapps.CUSTWORKORDERS SET kvh_prod_type='1.5M' WHERE swo_id  IN ( SELECT swo_id FROM siebeldata_mv@siebelcust WHERE x_kvh01_bandwidth LIKE '1.5M%');
COMMIT;
UPDATE nidapps.CUSTWORKORDERS SET kvh_prod_type='1.5M' WHERE swo_id  IN ( SELECT swo_id FROM siebeldata_mv@siebelcust WHERE x_kvh01_bandwidth LIKE '64K%');
COMMIT;
UPDATE nidapps.CUSTWORKORDERS SET kvh_prod_type='1.5M' WHERE swo_id  IN ( SELECT swo_id FROM siebeldata_mv@siebelcust WHERE x_kvh01_bandwidth LIKE '128K%');
COMMIT;
UPDATE nidapps.CUSTWORKORDERS SET kvh_prod_type='1G' WHERE swo_id  IN ( SELECT swo_id FROM siebeldata_mv@siebelcust WHERE x_kvh01_bandwidth LIKE '1000BaseLX%');
COMMIT;
UPDATE nidapps.CUSTWORKORDERS SET kvh_prod_type='2G' WHERE swo_id  IN ( SELECT swo_id FROM siebeldata_mv@siebelcust WHERE x_kvh01_bandwidth LIKE '1000BaseSX%');
COMMIT;
UPDATE nidapps.CUSTWORKORDERS SET kvh_prod_type='100M' WHERE swo_id  IN ( SELECT swo_id FROM siebeldata_mv@siebelcust WHERE x_kvh01_bandwidth LIKE '100BaseFX%');
COMMIT;
UPDATE nidapps.CUSTWORKORDERS SET kvh_prod_type='100M' WHERE swo_id  IN ( SELECT swo_id FROM siebeldata_mv@siebelcust WHERE x_kvh01_bandwidth LIKE '100BaseTX%');
COMMIT;
UPDATE nidapps.CUSTWORKORDERS SET kvh_prod_type='100M' WHERE swo_id  IN ( SELECT swo_id FROM siebeldata_mv@siebelcust WHERE x_kvh01_bandwidth LIKE '100Mbps%');
COMMIT;
UPDATE nidapps.CUSTWORKORDERS SET kvh_prod_type='10M' WHERE swo_id  IN ( SELECT swo_id FROM siebeldata_mv@siebelcust WHERE x_kvh01_bandwidth LIKE '10Mbps%');
COMMIT;
UPDATE nidapps.CUSTWORKORDERS SET kvh_prod_type='10G' WHERE swo_id  IN ( SELECT swo_id FROM siebeldata_mv@siebelcust WHERE x_kvh01_bandwidth LIKE '1000BaseSX%');
COMMIT;
UPDATE nidapps.CUSTWORKORDERS SET kvh_prod_type='10M' WHERE swo_id  IN ( SELECT swo_id FROM siebeldata_mv@siebelcust WHERE x_kvh01_bandwidth LIKE '1000BaseSX%');
COMMIT;
UPDATE nidapps.CUSTWORKORDERS SET kvh_prod_type='1G' WHERE swo_id  IN ( SELECT swo_id FROM siebeldata_mv@siebelcust WHERE x_kvh01_bandwidth LIKE '1Gbps%');
COMMIT;
UPDATE nidapps.CUSTWORKORDERS SET kvh_prod_type='2.4G' WHERE swo_id  IN ( SELECT swo_id FROM siebeldata_mv@siebelcust WHERE x_kvh01_bandwidth LIKE '2.4Gbps%');
COMMIT;
UPDATE nidapps.CUSTWORKORDERS SET kvh_prod_type='20M' WHERE swo_id  IN ( SELECT swo_id FROM siebeldata_mv@siebelcust WHERE x_kvh01_bandwidth LIKE '20Mbps%');
COMMIT;
UPDATE nidapps.CUSTWORKORDERS SET kvh_prod_type='2M' WHERE swo_id  IN ( SELECT swo_id FROM siebeldata_mv@siebelcust WHERE x_kvh01_bandwidth LIKE '2Mbps%');
COMMIT;
UPDATE nidapps.CUSTWORKORDERS SET kvh_prod_type='30M' WHERE swo_id  IN ( SELECT swo_id FROM siebeldata_mv@siebelcust WHERE x_kvh01_bandwidth LIKE '30Mbps%');
COMMIT;
UPDATE nidapps.CUSTWORKORDERS SET kvh_prod_type='40M' WHERE swo_id  IN ( SELECT swo_id FROM siebeldata_mv@siebelcust WHERE x_kvh01_bandwidth LIKE '40Mbps%');
COMMIT;
UPDATE nidapps.CUSTWORKORDERS SET kvh_prod_type='4M' WHERE swo_id  IN ( SELECT swo_id FROM siebeldata_mv@siebelcust WHERE x_kvh01_bandwidth LIKE '4Mbps%');
COMMIT;
UPDATE nidapps.CUSTWORKORDERS SET kvh_prod_type='50M' WHERE swo_id  IN ( SELECT swo_id FROM siebeldata_mv@siebelcust WHERE x_kvh01_bandwidth LIKE '50Mbps%');
COMMIT;
UPDATE nidapps.CUSTWORKORDERS SET kvh_prod_type='5M' WHERE swo_id  IN ( SELECT swo_id FROM siebeldata_mv@siebelcust WHERE x_kvh01_bandwidth LIKE '5Mbps%');
COMMIT;
UPDATE nidapps.CUSTWORKORDERS SET kvh_prod_type='622M' WHERE swo_id  IN ( SELECT swo_id FROM siebeldata_mv@siebelcust WHERE x_kvh01_bandwidth LIKE '622Mbps%');
COMMIT;
UPDATE nidapps.CUSTWORKORDERS SET kvh_prod_type='7M' WHERE swo_id  IN ( SELECT swo_id FROM siebeldata_mv@siebelcust WHERE x_kvh01_bandwidth LIKE '7Mbps%');
COMMIT;
UPDATE nidapps.CUSTWORKORDERS SET kvh_prod_type='8M' WHERE swo_id  IN ( SELECT swo_id FROM siebeldata_mv@siebelcust WHERE x_kvh01_bandwidth LIKE '8Mbps%');
COMMIT;
UPDATE nidapps.CUSTWORKORDERS SET kvh_prod_type='45M' WHERE swo_id  IN ( SELECT swo_id FROM siebeldata_mv@siebelcust WHERE x_kvh01_bandwidth LIKE '%45M%');
COMMIT;
UPDATE nidapps.CUSTWORKORDERS SET kvh_prod_type='155M' WHERE swo_id  IN ( SELECT swo_id FROM siebeldata_mv@siebelcust WHERE x_kvh01_bandwidth LIKE '%155M%');
COMMIT;
UPDATE nidapps.CUSTWORKORDERS SET kvh_prod_type='10M' WHERE swo_id  IN ( SELECT swo_id FROM siebeldata_mv@siebelcust WHERE x_kvh01_bandwidth LIKE '%10BaseT%');
COMMIT;
UPDATE nidapps.CUSTWORKORDERS SET kvh_prod_type='10M' WHERE swo_id  IN ( SELECT swo_id FROM siebeldata_mv@siebelcust WHERE x_kvh01_bandwidth LIKE '%10M%');
COMMIT;
 UPDATE nidapps.CUSTWORKORDERS SET kvh_prod_type='2M'  WHERE kvh_prod_nm LIKE 'High Speed Digital 2Mbps';
 COMMIT;
 UPDATE nidapps.CUSTWORKORDERS SET kvh_prod_type='1.5M' WHERE kvh_prod_type LIKE 'Switched Service';
 COMMIT;
END;
/

when i execute the procedure it gives me the error
ORA-06512: at "NIDAPPS.SIEBEL_WODATA", line 86
ORA-06502: PL/SQL: numeric or value error

but when i execute the statement,
UPDATE nidapps.CUSTWORKORDERS SET kvh_prod_type='1.5M' WHERE swo_id IN ( SELECT swo_id FROM siebeldata_mv@siebelcust WHERE x_kvh01_bandwidth LIKE '1.5M%');

individually it runs fine

can you please tell me what is the reason behind this

thanks in advance

santosh

I second all of Tom's comments.

The only one i have is that I'd wager your problem is in

FOR i IN 1..icustarr.last LOOP

which should be

IF icustarr.COUNT > 0
THEN

FOR i IN 1..icustarr.last LOOP

END IF;

You should also look into things like BULK COLLECT and LIMIT.

OR...as i'm sure Tom would point out to you, do this properly in a single SQL statement, as it can / should be.

Hi Tom,
We have a function which contains a CLOB data type variable.

Env: Oracle 10g in Unix.

eg: create or replace package p1
.....
....
function f1 ( fld1 in clob) return number
as
......
....
end;
..
end;
The above function parses the fld1 and then stores into the table. The above one was working fine in production for an year and above.
Now, the same function is failing with ora-6502 error for certain messages.
Some of the messages contain junk characters. Some fail with the error and some gets parsed and gets stored in the table. Is it due to multi byte characters that come in the input message.

Parsing is done like below in the function.
eg:- a1:=ltrim(ltrim(substr(fld1,1,100)));
should this be changed to
a1:=ltrim(ltrim(substrb(fld1,1,100)));

please advise.

ops$tkyte%ORA10GR2> declare
  2          a varchar2(1);
  3          b varchar2(1 char);
  4  begin
  5          null;
  6  end;
  7  /

PL/SQL procedure successfully completed.

create or replace package pack1 is

   v1 varchar2(30) := lpad('a',33,'a');

   function f1 return varchar2;

end;
/
show errors


create or replace package body pack1 is

   function f1 return varchar2 is
   begin
      return (v1);
   end;

end;
/
show errors

select pack1.f1 from dual
/

select pack1.f1 from dual
/

My friend showed me this problem, I have reduced to test case above which is similar to other items in this thread. But my head hurts from being too dense I guess because I am not understanding why the second call succeeds.

I would have thought that Oracle would continue to raise the same error with each invocation rather than to instantiate a workable copy of the package specification inspite of this error. Here are results of running this code.

The second invocation works and says that the variable responsible for the error has a value of null. This could be bad if code went forward to execute would it not?

SQL> create or replace package pack1 is
  2  
  3     v1 varchar2(30) := lpad('a',33,'a');
  4  
  5     function f1 return varchar2;
  6  
  7  end;
  8  /

Package created.

SQL> show errors
No errors.
SQL> 
SQL> 
SQL> create or replace package body pack1 is
  2  
  3     function f1 return varchar2 is
  4     begin
  5        return (v1);
  6     end;
  7  
  8  end;
  9  /

Package body created.

SQL> show errors
No errors.
SQL> 
SQL> select pack1.f1 from dual
  2  /
select pack1.f1 from dual
       *
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error: character string buffer too small
ORA-06512: at "R2IDB.PACK1", line 3
ORA-06512: at line 1


SQL> 
SQL> select pack1.f1 from dual
  2  /

F1
----------------------------------------------------------

I ran same code on an 8i instance and 10g instance, same error on first invocation, and same result of null on second invocation (well at least it is consistent across releases). My frind Mike wants to know if this is a bug or undocumented feature? I told him to mind his manners because this is ASKTOMHOME.

SQL> select * from v$version;

BANNER
--------------------------------------------------------
Oracle8i Release 8.1.7.0.0 - Production
PL/SQL Release 8.1.7.0.0 - Production
CORE    8.1.7.0.0       Production
TNS for 32-bit Windows: Version 8.1.7.0.0 - Production
NLSRTL Version 3.4.1.0.0 - Production


Sto> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod
PL/SQL Release 10.2.0.1.0 - Production
CORE    10.2.0.1.0      Production
TNS for 32-bit Windows: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production

Sto>

Please try one more time to help me get past my mental block here and understand what is happening. I guess as I said before I am confused as to why the second invocation does not return the same error. Seems to me this should be a bug. What do you think?

Kevin

Select To_Char(DocNO), 'BR', A.ACC_ID,CHQ# ,'C',b.Amount
from fbank_rec a, fvoch_OS b
where a.acc_id = b.acc_code
and a.cr_amount = b.amount
and a.chq# = b.INST_NO
and acc_id = '1302310007'
and amount > 0
and DRCR = 'D'
and cr_amount > 0
and a.Status = 'O'
and (acc_code,AMOUNT ,INST_NO) Not in (Select VD_ACC_CODE,VD_DR_AMOUNT ,VD_CHQ# from fvoch_dtl
Where NVL(VD_DR_AMOUNT,0)>0
and VD_CHQ# is Not Null
and VD_ACC_Code = '1302310007'
and VD_VDate <='31-Mar-2008'
Group by VD_ACC_CODE,VD_DR_AMOUNT,VD_CHQ#
having Count(*)>1 )



Error is Following

ORA-00604: error occurred at recursive SQL level 1
ORA-06502: PL/SQL: numeric or value error: character string buffer too small
ORA-06512: at line 8
ORA-00600: internal error code, arguments: [srsget_1], [], [], [], [], [], [], []

Please do the needful.
I am running this querry on Oracle(8.1.7)

Where NVL(VD_DR_AMOUNT,0)>0

          and VD_VDate <='31-Mar-2008'

          and VD_VDate <=to_date('31-Mar-2008','dd-mon-yyyy')

Hi again,
I have read this ora-06502 error on Metalink and google.
Majority of the solution are relating to sort_area_retained_size and sort_area_size.

Solutions were:
Should be same of both the sort area parameters.
I have tried but problem still persist.
Later on I have increase both of the parameters 100M, 200M, 300M and finally 500M
But the error remains same.

I need your expertise.

Current Sort Area parameters values are as follows:

SVRMGR> show parameter sort_area
NAME TYPE VALUE
----------------------------------- ------- ----------------
sort_area_retained_size 65536
sort_area_size 104857600

Thank You very much for your prompt reply.
You asked:

1. why is your sort area size so large
2. your retained size so small
3. what is the thinking behind that?
4. did you do what was suggested?

Actually I didn't change it. When I have taken charge It was current values. Sort area size should be the recomanded values or 25M or 50M.

Should I change it with this and then run my query with your two changes:

sort_area_retained_size integer 65536
sort_area_size integer 65536

or

sort_area_retained_size integer 65536
sort_area_size integer 26214400


Regards,
Tariq

Or it should be same as Oracle9i default sort area size

sort_area_retained_size integer 0
sort_area_size integer 524288


Regards,
Tariq

I have test it with the following parameters:

sort_area_size = 524288
sort_area_retained_size = 0

and

sort_area_size = 65536
sort_area_retained_size = 65536

and

sort_area_retained_size integer 65536
sort_area_size integer 22428800


But the problem still Persists.

Eventually I need your Recommendation to resolve this problem.

Regards,
Tari

Hi Tom,

Thank you very much for the assitance.

I have tried to match the values of both sort parameters and later on tries to apply on session level (Alter session)

following were the parameters before and after.

Old Parameters:
===============
sort_area_retained_size integer 65536
sort_area_size integer 104857600

New Parameters:
===============
sort_area_retained_size integer 104857600
sort_area_size integer 104857600

And then restart my database and execute the query. I got same error.

2nd mehtod as you have written with alter session command:


Connected to:
Oracle8i Enterprise Edition Release 8.1.7.0.0 - Production
With the Partitioning option
JServer Release 8.1.7.0.0 - Production

SP2-0103: Nothing in SQL buffer to run.
DFMLGL @ PRODKHI.WORLD>show parameter sort

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
nls_sort string
sort_area_retained_size integer 65536
sort_area_size integer 104857600
sort_multiblock_read_count integer 2
DFMLGL @ PRODKHI.WORLD>ALTER SESSION SET SORT_AREA_RETAINED_SIZE=xxx;
ALTER SESSION SET SORT_AREA_RETAINED_SIZE=xxx
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-06502: PL/SQL: numeric or value error: character string buffer too small
ORA-06512: at line 8
ORA-02017: integer value required


DFMLGL @ PRODKHI.WORLD>ALTER SESSION SET SORT_AREA_RETAINED_SIZE='xxx';
ALTER SESSION SET SORT_AREA_RETAINED_SIZE='xxx'
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-06502: PL/SQL: numeric or value error: character string buffer too small
ORA-06512: at line 8
ORA-02017: integer value required

What is your suggestions now.

Keeping in mind I want to upgrade 8i Replication into 9i Streams and facing errors.
I cann't switch immediately on 9i or 10g because I have 5 site with Multimaster replication and I am still in testing phase.

Please resolve my problem as soon as possble.


Regards,
Tariq

Hi,

I was little bit confuse because of 'xxx' (litral) but i know this is an integer value. Anyways.

As you have written the same values of sort parameters. I have already told you that I have done this but the error remain same.

sort_area_retained_size integer 104857600
sort_area_size integer 104857600

Now I will try today to set this parameter between 64k to 1m as you suggested.

Any suggestion?

Regards,
Tariq

ops$tkyte@ORA817DEV>  alter session set sort_area_size=10485760;

Session altered.

ops$tkyte@ORA817DEV> alter session set sort_area_retained_size=10485760;

Session altered.

ops$tkyte@ORA817DEV> select .... (your select goes here of course, not ....)

Hi,

I have changed both the parameters as recomanded values (i.e 64k and 1M) but the querry return same error.

What should I do next?


Regards,
tariq

Hi again,
Thank you very much for the assistance.
As per your instruction I have done the following things.

1.DFMLGL @ PRODKHI.WORLD>alter session set sort_area_size=10485760;

Session altered.

DFMLGL @ PRODKHI.WORLD>alter session set sort_area_retained_size=10485760;

Session altered.


==>query

1 Select To_Char(DocNO), 'BR', A.ACC_ID,CHQ# ,'C',b.Amount
2 from fbank_rec a, fvoch_OS b
3 where a.acc_id = b.acc_code
4 and a.cr_amount = b.amount
5 and a.chq# = b.INST_NO
6 and acc_id = '1302310007'
7 and amount > 0
8 and DRCR = 'D'
9 and cr_amount > 0
10 and a.Status = 'O'
11 and (acc_code,AMOUNT ,INST_NO) Not in (Select VD_ACC_CODE,VD_DR_AMOUNT ,VD_CHQ# from fvoch_dtl
12 Where NVL(VD_DR_AMOUNT,0)>0
13 and VD_CHQ# is Not Null
14 and VD_ACC_Code = '1302310007'
15 and VD_VDate <='31-Mar-2008'
16 Group by VD_ACC_CODE,VD_DR_AMOUNT,VD_CHQ#
17* having Count(*)>1 )
DFMLGL @ PRODKHI.WORLD>
DFMLGL @ PRODKHI.WORLD>/
from fbank_rec a, fvoch_OS b
*
ERROR at line 2:
ORA-00604: error occurred at recursive SQL level 1
ORA-06502: PL/SQL: numeric or value error: character string buffer too small
ORA-06512: at line 8
ORA-00942: table or view does not exist

===Yesterday and today work after office hours====
I have perform all my steps through parameter file and restart database to check the output of querry.

What should I do next?

Regards,
Tariq

@Tariq >
I think you should post the output of a "desc fvoch_OS", and if it is a view, post the query behind that view (along with description of underlying objects).

Does a simple "select * from fvoch_OS" raise that same "recursive error" ?

Hi Tom,

Thank you very much for the continuous assistance.
Issue has been resolved.
Now I want to tell the whole senario and your steps:

Actually we have two database users.
(1. Actual users in which tables are exist and
2. only for synonym user)

At user's end we have given the synonym user.

As per your instruction i have describe my table

==Synonym user (dfmlgl)==

SELECT_USER @ PRODKHI.WORLD>desc dfmlgl.fvoch_os
ERROR:
ORA-00604: error occurred at recursive SQL level 1
ORA-06502: PL/SQL: numeric or value error: character string buffer too small
ORA-06512: at line 8
ORA-04043: object dfmlgl.fvoch_os does not exist

==Actual user (dfmlgl_user)==
SELECT_USER @ PRODKHI.WORLD>desc dfmlgl_user.fvoch_os
Name Type
--- -------------
VNO NUMBER
VTYPE VARCHAR2(3)
VDT DATE
ACC_CODE VARCHAR2(15)
DRCR CHAR(1)
AMOUNT NUMBER
INST_NO VARCHAR2(20)

==To check the synonym (fvoch_os) exist or not==
select object_name,object_type from dba_objects
where owner ='DFMLGL'
AND OBJECT_NAME ='FVOCH_OS';

no rows selected

==I have create synonym===
SYSTEM @ PRODKHI.WORLD>CREATE SYNONYM DFMLGL.FVOCH_OS FOR DFMLGL_USER.FVOCH_OS;

Synonym created.

==Connect with dfmlgl user (synonym user)==
DFMLGL @ PRODKHI.WORLD>DESC FVOCH_OS
Name ------
VNO VTYPE VDT ACC_CODE DRCR AMOUNT INST_NO

==Rows check in Fvoch_os==
DFMLGL @ PRODKHI.WORLD>SELECT COUNT(*) FROM FVOCH_OS;
COUNT(*)
----------
1056
1 row selected.

==set the session parameters (sort)==
DFMLGL @ PRODKHI.WORLD>alter session set sort_area_size=10485760;

Session altered.

DFMLGL @ PRODKHI.WORLD>alter session set sort_area_retained_size=10485760;

Session altered.

==Execure the querry===
DFMLGL @ PRODKHI.WORLD>

Select To_Char(DocNO), 'BR', A.ACC_ID,CHQ# ,'C',b.Amount
from fbank_rec a, fvoch_OS b
where a.acc_id = b.acc_code
and a.cr_amount = b.amount
and a.chq# = b.INST_NO
and acc_id = '1302310007'
and amount > 0
and DRCR = 'D'
and cr_amount > 0
and a.Status = 'O'
and (acc_code,AMOUNT ,INST_NO) Not in (Select VD_ACC_CODE,VD_DR_AMOUNT ,VD_CHQ# from
fvoch_dtl
Where NVL(VD_DR_AMOUNT,0)>0
and VD_CHQ# is Not Null
and VD_ACC_Code = '1302310007'
and VD_VDate <='31-Mar-2008'
Group by VD_ACC_CODE,VD_DR_AMOUNT,VD_CHQ#
having Count(*)>1 )
DFMLGL @ PRODKHI.WORLD>/

TO_CHAR(DOCNO) 'B ACC_ID CHQ# ' AMOUNT
---------------------------------------- -- --------------- -------------------- - ----------
3799 BR 1302310007 8170368 C 300000
3797 BR 1302310007 5107588 C 300000
3798 BR 1302310007 5107605 C 20000
3796 BR 1302310007 206219 C 20000
3791 BR 1302310007 53992379 C 1000000
3781 BR 1302310007 531732 C 35000

6 rows selected.

For your information! We have created few database level trigger for auditing purpose. We cannot stop or remove them because of management requirement (to cross check).


I need your final suggestion to set sort parameters perminently in init file.

Any other suggestions Please!

Regards,
Tariq

Hi Tom,

Thank you very much for your prompt reply.

..........
SELECT_USER @ PRODKHI.WORLD>desc dfmlgl.fvoch_os
ERROR:
ORA-00604: error occurred at recursive SQL level 1
ORA-06502: PL/SQL: numeric or value error: character string buffer too small
ORA-06512: at line 8

you, umm, really want to fix that don't you.
.............
How Can I fix it?

Is there any query to check which database trigger is doing wrong or doesn't work properly? I can just check the status.

I have set sort parameter in int file.
SELECT_USER @ PRODKHI.WORLD>show parameter sort

NAME TYPE VALUE
------------------------------------ ----------- -----------
nls_sort string
sort_area_retained_size integer 10485760
sort_area_size integer 10485760
sort_multiblock_read_count integer 2


Regards,
Tariq

1. Error

SELECT_USER @ PRODKHI.WORLD>select owner, trigger_name, trigger_type, triggering_event
from dba_triggers WHERE triggering_event like 'ERROR%';

OWNER TRIGGER_NAME TRIGGER_TYPE TRIGGERING_EVENT
--------- ---------------- -------------- ----------------
SYSTEM LOG_ERRORS AFTER EVENT ERROR

2. Trigger Body

SELECT_USER @ PRODKHI.WORLD>set long 1000
SELECT_USER @ PRODKHI.WORLD>select trigger_body from dba_triggers
where trigger_name like 'LOG_ERRORS%';

TRIGGER_BODY
--------------------------------------------------------------------------------
declare
var_user varchar2(30);
var_osuser varchar2(30);
var_machine varchar2(64);
var_process varchar2(8);
var_program varchar2(48);
begin
select
username,
osuser,
machine,
process,
program
into
var_user,
var_osuser,
var_machine,
var_process,
var_program
from
v$session
where
audsid=userenv('sessionid');
insert into watch.servererror_log
values(
dbms_standard.server_error(1),
sysdate,
var_user,
var_osuser,
var_machine,
var_process,
var_program);
end;


1 row selected.

3. Table Structure

SELECT_USER @ PRODKHI.WORLD>set line 100
SELECT_USER @ PRODKHI.WORLD>desc watch.servererror_log
Name Null? Type
------------------ -------- ------------------------------------
ERROR VARCHAR2(30)
TIMESTAMP DATE
USERNAME VARCHAR2(30)
OSUSER VARCHAR2(30)
MACHINE VARCHAR2(64)
PROCESS VARCHAR2(8)
PROGRAM VARCHAR2(48)

4. Default tablespace & Free Space

TABLESPACE_NAME TOTAL_SIZE USED_SPACE FREE_SPACE %used_sapce %free_space
------------------------------ ---------- ---------- ---------- ----------- -----------
TS_WATCH_HO 1200 960.570313 239.429688 80 20

4.1 File System

TABLESPACE_NAME FILE_NAME SIZE_MB AUT
---------------- ----------------------------------- ------- ---
TS_WATCH_HO /u02/oradata/prodkhi/watch_ho.dbf 700 NO
TS_WATCH_HO /u02/oradata/prodkhi/watch_ho2.dbf 500 YES


5. Trigger select is working fine.

select
username,
osuser,
machine,
process,
program
from
v$session
where
audsid=userenv('sessionid')

USERNAME OSUSER MACHINE PROCESS PROGRAM
-------- ------ ------- ------- -------
SELECT_USER tlakho.it DMG\TLAKHO_IT 2440:2448 sqlplusw.exe


Note: Select_user I have created for just select any object in the database.
It has the following privillages.
Privillages are: connect,select any table,select_catalog_role


SELECT_USER @ PRODKHI.WORLD>select * from session_roles;

ROLE
------------------------------
SELECT_CATALOG_ROLE
HS_ADMIN_ROLE


6. I have just compile this trigger and then show error

SYSTEM @ PRODKHI.WORLD>ALTER TRIGGER SYSTEM.LOG_ERRORS COMPILE;

Trigger altered.

SYSTEM @ PRODKHI.WORLD>SHOW ERROR
No errors.

7. Again run the fixed querry. (it is still appear)
SYSTEM @ PRODKHI.WORLD>select owner, trigger_name, trigger_type, triggering_event from dba_triggers
where triggering_event like 'ERROR%';

OWNER TRIGGER_NAME TRIGGER_TYPE TRIGGERING_EVENT
--------- ---------------- -------------- ----------------
SYSTEM LOG_ERRORS AFTER EVENT ERROR


8. Trigger Status

SELECT_USER @ PRODKHI.WORLD>SELECT TRIGGER_NAME,STATUS FROM DBA_TRIGGERS
WHERE TRIGGER_NAME LIKE 'LOG_ERRORS%';

TRIGGER_NAME STATUS
------------------------------ --------
LOG_ERRORS ENABLED


9. No of rows in watch.servererror_log

SELECT_USER @ PRODKHI.WORLD>select count(*) from watch.servererror_log;

COUNT(*)
----------
101294

10. I have check last 1000 in my alert log
There is no error like ORA-06502:
But the errors are appear for replication

Errors in file /u01/app/oracle/admin/prodkhi/bdump/prodkhi_snp8_21029.trc:
ORA-12012: error on auto execute of job 126
ORA-23386: replication parallel push cannot create slave processes
ORA-06512: at "SYS.DBMS_DEFER_SYS", line 1520
ORA-06512: at "SYS.DBMS_DEFER_SYS", line 1583
ORA-06512: at line 1

Errors in file /u01/app/oracle/admin/prodkhi/udump/prodkhi_ora_24143.trc:
ORA-00600: internal error code, arguments: [srsget_1], [], [], [], [], [], [], []

Errors in file /u01/app/oracle/admin/prodkhi/udump/prodkhi_ora_24059.trc:
ORA-00600: internal error code, arguments: [12333], [0], [2], [31], [], [], [], []

==>What should I do to fix it?

ops$tkyte%ORA9IR2> create table servererror_log
  2  as
  3  select dbms_standard.server_error(1) error, sysdate timestamp, username, osuser, machine, process, program from v$session where 1=0;

Table created.

ops$tkyte%ORA9IR2>
ops$tkyte%ORA9IR2> desc servererror_log;
 Name                                     Null?    Type
 ---------------------------------------- -------- ----------------------------
 ERROR                                             NUMBER
 TIMESTAMP                                         DATE
 USERNAME                                          VARCHAR2(30)
 OSUSER                                            VARCHAR2(30)
 MACHINE                                           VARCHAR2(64)
 PROCESS                                           VARCHAR2(12)
 PROGRAM                                           VARCHAR2(48)

ops$tkyte%ORA9IR2>
ops$tkyte%ORA9IR2> create or replace trigger server_error_trigger
  2  after servererror on database
  3  begin
  4          insert into servererror_log
  5          ( error, timestamp, username, osuser, machine, process, program )
  6          select dbms_standard.server_error(1) error, sysdate dt, username, osuser, machine, process, program
  7            from v$session
  8           where sid = ( select sid from v$mystat where rownum=1 );
  9  end;
 10  /

Trigger created.

USERNAME      OSUSER    MACHINE         PROCESS   PROGRAM
--------      ------    -------         -------   -------
SELECT_USER   tlakho.it DMG\TLAKHO_IT   2440:2448 sqlplusw.exe

==>Complete trigger body===

CREATE OR REPLACE TRIGGER log_errors
after servererror on database
declare
var_user varchar2(30);
var_osuser varchar2(30);
var_machine varchar2(64);
var_process varchar2(8);
var_program varchar2(48);
begin
select
username,
osuser,
machine,
process,
program
into
var_user,
var_osuser,
var_machine,
var_process,
var_program
from
v$session
where
audsid=userenv('sessionid');
insert into watch.servererror_log
values(
dbms_standard.server_error(1),
sysdate,
var_user,
var_osuser,
var_machine,
var_process,
var_program);
end;

Regards,
Tariq

Thank you Tom for your assistance.
Actually you can't believe that I am learning more from your code & queries.

Anyways, Issue has been resolved.
And I have drop and recreate trigger with your code.
It is working fine.

I will further update you if needed.

Thanks again.




Regards,
Tariq

in one of the forms(6) i am using i get the ora-6502 error
on screen ,without any further information. is there a way to detect which procedure and what line is causing the error ?

Hi Tom,

Please evaluate the following PL/SQL block and enlighten us.



SQL*Plus: Release 10.2.0.1.0 - Production on Wed Jul 6 22:30:26 2011

Copyright (c) 1982, 2005, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> DECLARE
  2    p_msg VARCHAR2(4000);
  3  BEGIN
  4    -- simulating OUT parameter of (VARCHAR2) data type
  5    p_msg := 100 || ',' || 
  6             --( -- if these brackets are added the error ORA-06502 vanishes
  7             100 + 100
  8             --) 
  9             || ',' || 300;
 10  
 11    dbms_output.put_line('p_msg=' || p_msg);
 12  END;
 13  /
DECLARE
*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error: character to number conversion error
ORA-06512: at line 5

* We were expecting 100,200,300 as the output but we received an ORA-06502! Is this an intended behaviour, please advise.

The operator precedence for your reference
http://download.oracle.com/docs/cd/B28359_01/server.111/b28286/operators001.htm#sthref866

Thanks Tom, The operator precedence documentation makes sense now!. We thank you for your time spent on this review.

This answer helped me solve a odd issue:

DECLARE
   cur_month       pls_integer := 10;
   l_date_string   VARCHAR ( 8 );
BEGIN
   l_date_string := '2013' || '0' || cur_month - 1 || '01' ;
   DBMS_OUTPUT.put_line ( l_date_string );
END;
/

Generates an error. This is due to operator precedence. "||" and " - " have the same level of precedence. Therefore the string is assembled: "201300901" which was not what was intended: "20130901". Solution is obvious: put parens around the (Cur_month - 1).

This might help someone else...