Ora 06502 pl sql буфер символьных строк слишком маленький ошибка числа или значения

I am getting a 'ORA-06502: PL/SQL: numeric or value error: character string buffer too small error', trying to execute a stored procedure that has an output parameter with all 50 US states and the abb...

I am getting a ‘ORA-06502: PL/SQL: numeric or value error: character string buffer too small error’, trying to execute a stored procedure that has an output parameter with all 50 US states and the abbreviations.

Any suggestions on what I am doing wrong.  Oracle database version is 11.2.0.1.0.  I am also searching to see if I can find anything online, but thought of asking here in case someone had this problem in the past that was addressed.

CREATE TABLE STATE_CDS (STATE VARCHAR2(2) NOT NULL, STATE_NAME VARCHAR2(25 BYTE));

insert into state_cds values(‘AL’, ‘ALABAMA’);

insert into state_cds values(‘AK’, ‘ALASKA’);

insert into state_cds values(‘AZ’, ‘ARIZONA’);

insert into state_cds values(‘AR’, ‘ARKANSAS’);

insert into state_cds values(‘CA’, ‘CALIFORNIA’);

insert into state_cds values(‘CO’, ‘COLORADO’);

insert into state_cds values(‘CT’, ‘CONNECTICUT’);

insert into state_cds values(‘DE’, ‘DELAWARE’);

insert into state_cds values(‘DC’, ‘DISTRICT OF COLUMBIA’);

insert into state_cds values(‘FL’, ‘FLORIDA’);

insert into state_cds values(‘GA’, ‘GEORGIA’);

insert into state_cds values(‘HI’, ‘HAWAII’);

insert into state_cds values(‘ID’, ‘IDAHO’);

insert into state_cds values(‘IL’, ‘ILLINOIS’);

insert into state_cds values(‘IN’, ‘INDIANA’);

insert into state_cds values(‘IA’, ‘IOWA’);

insert into state_cds values(‘KS’, ‘KANSAS’);

insert into state_cds values(‘KY’, ‘KENTUCKY’);

insert into state_cds values(‘LA’, ‘LOUISIANA’);

insert into state_cds values(‘ME’, ‘MAINE’);

insert into state_cds values(‘MD’, ‘MARYLAND’);

insert into state_cds values(‘MA’, ‘MASSACHUSETTS’);

insert into state_cds values(‘MI’, ‘MICHIGAN’);

insert into state_cds values(‘MN’, ‘MINNESOTA’);

insert into state_cds values(‘MS’, ‘MISSISSIPPI’);

insert into state_cds values(‘MO’, ‘MISSOURI’);

insert into state_cds values(‘MT’, ‘MONTANA’);

insert into state_cds values(‘NE’, ‘NEBRASKA’);

insert into state_cds values(‘NV’, ‘NEVADA’);

insert into state_cds values(‘NH’, ‘NEW HAMPSHIRE’);

insert into state_cds values(‘NJ’, ‘NEW JERSEY’);

insert into state_cds values(‘NM’, ‘NEW MEXICO’);

insert into state_cds values(‘NY’, ‘NEW YORK’);

insert into state_cds values(‘NC’, ‘NORTH CAROLINA’);

insert into state_cds values(‘ND’, ‘NORTH DAKOTA’);

insert into state_cds values(‘OH’, ‘OHIO’);

insert into state_cds values(‘OK’, ‘OKLAHOMA’);

insert into state_cds values(‘OR’, ‘OREGON’);

insert into state_cds values(‘OU’, ‘OUT OF COUNTRY’);

insert into state_cds values(‘PA’, ‘PENNSYLVANIA’);

insert into state_cds values(‘RI’, ‘RHODE ISLAND’);

insert into state_cds values(‘SC’, ‘SOUTH CAROLINA’);

insert into state_cds values(‘SD’, ‘SOUTH DAKOTA’);

insert into state_cds values(‘TN’, ‘TENNESSEE’);

insert into state_cds values(‘TX’, ‘TEXAS’);

insert into state_cds values(‘UT’, ‘UTAH’);

insert into state_cds values(‘VT’, ‘VERMONT’);

insert into state_cds values(‘VA’, ‘VIRGINIA’);

insert into state_cds values(‘WA’, ‘WASHINGTON’);

insert into state_cds values(‘WV’, ‘WEST VIRGINIA’);

insert into state_cds values(‘WI’, ‘WISCONSIN’);

insert into state_cds values(‘WY’, ‘WYOMING’);

create or replace procedure P_state_CODES

  (cd_type          in varchar2,

  cd_desc           out varchar2) is

cursor states is

  select state, state_name

    from state_cds

    order by state_name desc;

begin

  if cd_type = ‘STATES’ then

    for recs in states loop

      cd_desc := recs.state||’;’||recs.state_name||’+’||cd_desc;

    end loop;

  end if;

end;

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

ORA-06512: at «KBNIS.P_STATE_CODES», line 11

ORA-06512: at «KBNIS.P_STATE_CODES», line 11

ORA-06512: at line 7

Thank you for the help.

Sandeep.

Are you getting an ORA-06502 error message when working with Oracle SQL? Learn how to resolve it and what causes it in this article.

ORA-06502 Cause

The cause of the “ORA-06502 PL/SQL numeric or value error” can be one of many things:

  1. A value is being assigned to a numeric variable, but the value is larger than what the variable can handle.
  2. A non-numeric value is being assigned to a numeric variable.
  3. A value of NULL is being assigned to a variable which has a NOT NULL constraint.

Let’s take a look at the solutions for each of these causes.

The solution for this error will depend on the cause.

Let’s see an example of each of the three causes mentioned above.

Solution 1: Value Larger than Variable (Number Precision Too Large)

In this example, we have some code that is setting a numeric variable to a value which is larger than what can be stored.

Let’s create this procedure which declares and then sets a variable:

CREATE OR REPLACE PROCEDURE TestLargeNumber
AS
  testNumber NUMBER(3);
BEGIN
  testNumber := 4321;
END;

If we compile it, it compiles with no errors.

Procedure TESTLARGENUMBER compiled

Now, let’s run the procedure.

EXEC TestLargeNumber;

We get an error:

Error starting at line : 8 in command -
EXEC TestLargeNumber
Error report -
ORA-06502: PL/SQL: numeric or value error: number precision too large
ORA-06512: at "SYSTEM.TESTLARGENUMBER", line 5
ORA-06512: at line 1
06502. 00000 -  "PL/SQL: numeric or value error%s"
*Cause:    An arithmetic, numeric, string, conversion, or constraint error
           occurred. For example, this error occurs if an attempt is made to
           assign the value NULL to a variable declared NOT NULL, or if an
           attempt is made to assign an integer larger than 99 to a variable
           declared NUMBER(2).
*Action:   Change the data, how it is manipulated, or how it is declared so
           that values do not violate constraints.

The error we’ve gotten is “ORA-06502: PL/SQL: numeric or value error: number precision too large”. It also includes an ORA-06512, but that error just mentions the next line the code is run from, as explained in this article on ORA-06512.

This is because our variable testNumber can only hold 3 digits, because it was declared as a NUMBER(3). But, the value we’re setting it to a few lines later is 4 digit long (4321).

So, the value is too large for the variable.

To resolve it, increase the size of your variable, or manipulate your value to fit the size of the variable (if possible).

In our example , we can change the size of the variable.

CREATE OR REPLACE PROCEDURE TestLargeNumber
AS
  testNumber NUMBER(4);
BEGIN
  testNumber := 4321;
END;
Procedure TESTLARGENUMBER compiled

Now, let’s run the procedure.

EXEC TestLargeNumber;
PL/SQL procedure successfully completed.

The procedure runs successfully. We don’t get any output (because we didn’t code any in), but there are no errors.

Read more on the Oracle data types here.

Solution 2: Non-Numeric Value

Another way to find and resolve this error is by ensuring you’re not setting a numeric variable to a non-numeric value.

For example, take a look at this function.

CREATE OR REPLACE PROCEDURE TestNonNumeric
AS
  testNumber NUMBER(4);
BEGIN
  testNumber := 'Yes';
END;
Procedure TESTNONNUMERIC compiled

The procedure compiles successfully. Now, let’s fun the function.

EXEC TestNonNumeric;
Error starting at line : 8 in command -
EXEC TestNonNumeric
Error report -
ORA-06502: PL/SQL: numeric or value error: character to number conversion error
ORA-06512: at "SYSTEM.TESTNONNUMERIC", line 5
ORA-06512: at line 1
06502. 00000 -  "PL/SQL: numeric or value error%s"
*Cause:    An arithmetic, numeric, string, conversion, or constraint error
           occurred. For example, this error occurs if an attempt is made to
           assign the value NULL to a variable declared NOT NULL, or if an
           attempt is made to assign an integer larger than 99 to a variable
           declared NUMBER(2).
*Action:   Change the data, how it is manipulated, or how it is declared so
           that values do not violate constraints.

The error we get is “ORA-06502: PL/SQL: numeric or value error: character to number conversion error”.

This happens because our variable testNumber is set to a NUMBER, but a few lines later, we’re setting it to a string value which cannot be converted to a number

To resolve this error:

  1. Ensure the value coming in is a number and not a string.
  2. Convert your string to a number using TO_NUMBER (the conversion might happen implicitly but this may help).
  3. Convert your string to the ASCII code that represents the string using the ASCII function.
  4. Change the data type of your variable (but check that your code is getting the right value first).

The solution you use will depend on your requirements.

Solution 3: NOT NULL Variable

This error can appear if you try to set a NULL value to a NOT NULL variable.

Let’s take a look at this code here:

CREATE OR REPLACE PROCEDURE TestNonNull
AS
  testNumber NUMBER(4) NOT NULL := 10;
  nullValue NUMBER(4) := NULL;
BEGIN
  testNumber := nullValue;
END;

Procedure TESTNONNULL compiled

Now, the reason we’re using a variable to store NULL and not just setting testNumber to NULL is because we get a different error in that case. Besides, it’s probably more likely that your NULL value will come from another system or a database table, rather than a hard-coded NULL value.

Let’s run this function now.

Error starting at line : 9 in command -
EXEC TestNonNull
Error report -
ORA-06502: PL/SQL: numeric or value error
ORA-06512: at "SYSTEM.TESTNONNULL", line 6
ORA-06512: at line 1
06502. 00000 -  "PL/SQL: numeric or value error%s"
*Cause:    An arithmetic, numeric, string, conversion, or constraint error
           occurred. For example, this error occurs if an attempt is made to
           assign the value NULL to a variable declared NOT NULL, or if an
           attempt is made to assign an integer larger than 99 to a variable
           declared NUMBER(2).
*Action:   Change the data, how it is manipulated, or how it is declared so
           that values do not violate constraints.

We get the ORA-06502 error.

This error message doesn’t give us much more information. But, we can look at the code on line 6, as indicated by the message. We can see we have a variable that has a NOT NULL constraint, and the variable is NULL.

To be sure, we can output some text in our demo when it is null.

CREATE OR REPLACE PROCEDURE TestNonNull
AS
  testNumber NUMBER(4) NOT NULL := 10;
  nullValue NUMBER(4) := NULL;
BEGIN
  IF (nullValue IS NULL) THEN
    dbms_output.put_line('Value is null!');
  ELSE
    testNumber := nullValue;
  END IF;
END;

Now let’s call the procedure.

EXEC TestNonNull;
Value is null!

The output shows the text message, indicating the value is null.

ORA-06502 character string buffer too small

This version of the error can occur if you set a character variable to a value larger than what it can hold.

When you declare character variables (CHAR, VARCHAR2, for example), you need to specify the maximum size of the value. If a value is assigned to this variable which is larger than that size, then this error will occur.

For example:

DECLARE
  charValue VARCHAR2(5);
BEGIN
  charValue := 'ABCDEF';
END;

If I compile this code, I get an error:

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

This happens because the variable is 5 characters long, and I’m setting it to a value which is 6 characters long.

You could also get this error when using CHAR data types.

DECLARE
  charValue CHAR(5);
BEGIN
  charValue := 'A';
  charValue := charValue || 'B';
END;
ORA-06502: PL/SQL: numeric or value error: character string buffer too small
ORA-06512: at line 5

This error happens because the CHAR data type uses the maximum number of characters. It has stored the value of A and added 4 space characters, up until its maximum value of 5.

When you try to concatenate a value of B to it, the resulting value is ‘A    B’, which is 6 characters.

To resolve this, use a VARCHAR2 variable instead of a CHAR, and ensure the maximum size is enough for you.

ORA-06502: pl/sql: numeric or value error: null index table key value

Sometimes you might get this error message with the ORA-06502 error:

ORA-06502: pl/sql: numeric or value error: null index table key value

This means that either:

  • Your index variable is not getting initialized, or
  • Your index variable is getting set to NULL somewhere in the code.

Check your code to see that neither of these two situations are happening.

ORA-06502: pl/sql: numeric or value error: bulk bind: truncated bind

You might also get this specific error message:

ORA-06502: pl/sql: numeric or value error: bulk bind: truncated bind

This is caused by an attempt to SELECT, UPDATE, or INSERT data into a table using a PL/SQL type where a column does not have the same scale as the column in the table.

For example, you may have declared a variable in PL/SQL to be VARCHAR2(100), but your table is only a VARCHAR2(50) field. You may get this error then.

You may also get this error because some data types in PL/SQL have different lengths in SQL.

To resolve this, declare your variables as the same type as the SQL table:

type t_yourcol is table of yourtable.yourcol%TYPE;

So, that’s how you resolve the ORA-06502 error.

Lastly, if you enjoy the information and career advice I’ve been providing, sign up to my newsletter below to stay up-to-date on my articles. You’ll also receive a fantastic bonus. Thanks!

ORA-06502: PL/SQL: numeric or value error: character string buffer too small error occurs when the length of the character string exceeds the length of the declared character type variable,. The value cannot be assigned to the variable if the size of the value passed in the database exceeds the size of the variable declared. The error ORA-06502: PL/SQL: numeric or value error: character string buffer too small would be thrown by the oracle. The error occurs because the output value saved in that variable is longer than it was declared.

The length of the string should not exceed the size of the data type declared in the variable. The string can be stored in the variable in this case. If the length of the character string exceeds the declared variable size, the character string cannot be saved. If the character is attempted to be assigned to the attribute, an exception would be thrown.

Exception

The error will be described as follows. The line number identifies the location of the error. The variable data size is larger than the value size. The following error has been thrown.

declare
    empid varchar2(3);
begin
    empid := 'A101';
end;
Error report -
ORA-06502: PL/SQL: numeric or value error: character string buffer too small
ORA-06512: at line 4
06502. 00000 -  "PL/SQL: numeric or value error%s"

Two ORA errors can be seen in the error stack trace. The first error code is shown alongside the error message. The second error code indicates which line the error happened on. The error indicates that the declared string variable’s size is insufficient in comparison to the value assigned to it.

Problem

The character string cannot be allocated if the length of the string exceeds the size of the declared data type variable. The error can be repeated in this scenario. The database is attempting to assign the variable a string. The error would be thrown since the string is longer than the variable’s length.

In the example below, the value has four characters. The variable is declared to be three characters long. The length of the string value exceeds the length of the declared variable. The error ORA-06502:PL/SQL: numeric or value error: character string buffer too small would be thrown if the value is assigned to a variable that is smaller in size.

declare
    empid varchar2(3);
begin
    empid := 'A101';
end;

Output

declare
    empid varchar2(3);
begin
    empid := 'A101';
end;
Error report -
ORA-06502: PL/SQL: numeric or value error: character string buffer too small
ORA-06512: at line 4
06502. 00000 -  "PL/SQL: numeric or value error%s"

Cause

An arithmetic, numeric, string, conversion, or constraint error occurred. For example, this error occurs if an attempt is made to assign the value NULL to a variable declared NOT NULL, or if an attempt is made to assign an integer larger than 99 to a variable declared NUMBER(2).

Action

Change the data, how it is manipulated, or how it is declared so that values do not violate constraints.

Solution 1

The size of the value passed in Oracle PS./SQL exceeds the declared character data type size. To accommodate the value, the variable data type should be modified. The character data type’s size should be increased. If the size of the character data type is reached to maximum size of the data type, the different data type should be used to accommodate the larger value.

declare
    empid varchar2(4);
begin
    empid := 'A101';
end;

Output

PL/SQL procedure successfully completed.

Solution 2

It’s essential to double-check the PL/SQL value. It’s possible that the value was passed to the variable inappropriately or that there was an error in the method. The value will be stored in the variable if it is corrected.

declare
    empid varchar2(4);
begin
    empid := '101';
end;

Output

PL/SQL procedure successfully completed.

Solution 3

In most instances, the value assigned would be within the declared data type’s range. The length of the value sometimes reaches the declared data type size. We can’t adjust the data type size in this situation. The exception should be handled and taken action in the PL/SQL code.

declare
    empid varchar2(3);
begin
    empid := 'A101';
exception
    WHEN OTHERS THEN
        empid :=0;
end;

Output

PL/SQL procedure successfully completed.

Думаю, что вам уже кажется, что с процедурами и их параметрами мы разобрались, а вот и нет! Есть еще кое-что. Думаю, кто-то из вас заметил, что при написании формального параметра процедуры, например, определяя ее как VARCHAR2 или NUMBER я никогда не делал вот так:

CREATE OR REPLACE PROCEDURE some_proc(NUM IN OUT NUMBER(3,2), DT OUT VARCHAR2(100))

И вот почему. Накладывать ограничения на формальные параметры функций в PL/SQL — ЗАПРЕЩЕНО! Например, вот такой пример, приведет к ошибке компиляции:

CREATE OR REPLACE PROCEDURE TESTINOUT(NUM IN OUT NUMBER(3,2), DT OUT VARCHAR2(100))
IS

BEGIN

SELECT COMPANY INTO DT FROM customers
WHERE customers.CUST_NUM = NUM;

SELECT CUST_REP INTO NUM FROM customers
WHERE customers.CUST_NUM = NUM;

END TESTINOUT;
/

В результате получите:

SQL> CREATE OR REPLACE PROCEDURE TESTINOUT(NUM IN OUT NUMBER(3,2), DT OUT VARCHAR2(100))
  2  IS
  3  
  4  BEGIN
  5  
  6   SELECT COMPANY INTO DT FROM customers
  7   WHERE customers.CUST_NUM = NUM;
  8  
  9   SELECT CUST_REP INTO NUM FROM customers
 10   WHERE customers.CUST_NUM = NUM;
 11  
 12  END TESTINOUT;
 13  /

Предупреждение: Процедура создана с ошибками компиляции.

Что и требовалось доказать. Можете убрать неверные объявления и еще раз перекомпилировать процедуру для того, чтобы она осталась исправной. А, вот вам еще один подводный камешек. Запишем вот такую процедуру:

CREATE OR REPLACE PROCEDURE PTEST(I_PAR IN OUT NUMBER, II_PAR IN OUT VARCHAR2)
IS

BEGIN

	I_PAR := 15.6;
	II_PAR := 'POIUYTREWQLKJHGFDSA';

END PTEST;
/

Компилируем:

SQL> CREATE OR REPLACE PROCEDURE PTEST(I_PAR IN OUT NUMBER, II_PAR IN OUT VARCHAR2)
  2  IS
  3  
  4  BEGIN
  5  
  6   I_PAR := 15.6;
  7   II_PAR := 'POIUYTREWQLKJHGFDSA';
  8  
  9  END PTEST;
 10  /

Процедура создана.

Вот теперь I_PAR и II_PAR получили неявное ограничение посредством объявлений:

I_PAR := 15.6;
II_PAR := 'POIUYTREWQLKJHGFDSA';

т.е. получилось, что то вроде:

CREATE OR REPLACE PROCEDURE PTEST(I_PAR IN OUT NUMBER(3.4), II_PAR IN OUT VARCHAR2(19))

Теперь, если произвести вот такой вызов:

DECLARE 

V_STR VARCHAR2(10);
V_NUM NUMBER(3,4); 

BEGIN

	PTEST(V_NUM, V_STR);

END;
/

Получаем, что-то довольно странное:

SQL> DECLARE
  2  
  3  V_STR VARCHAR2(10);
  4  V_NUM NUMBER(3,4);
  5  
  6  BEGIN
  7  
  8   PTEST(V_NUM, V_STR);
  9  
 10  END;
 11  /
DECLARE
*
ошибка в строке 1:
ORA-06502: PL/SQL: : буфер символьных строк слишком маленький ошибка числа или значения 
ORA-06512: на  "MILLER.PTEST", line 7 
ORA-06512: на  line 8 

SQL>

Не сразу ясно, что происходит, так? А все очень просто, V_STR VARCHAR2(10) переопределила ограничение переменной II_PAR при ее явном вызове и запись строки длинной 19 символов в переменную всего в 10 символов привело к ошибке! Очень важно это понимать, иначе в дальнейшем вы запутаетесь совсем! Здесь ошибку вызвала сама вызывающая программа, а не код процедуры, как может показаться! Так вот во избежание ошибок, подобных ORA-06502 при создании процедур документируйте все ограничения налагаемые на фактические параметры — вносите в хранимые процедуры комментарии, а так же кроме описания каждого параметра записывайте функции выполняемые самой процедурой! Вот тогда я думаю, у вас все получится!

Так же единственным способом наложения ограничения на формальный параметр функции является использование оператора %TYPE. Мы с вами о нем говорили. В свете этого можно переписать нашу функцию пример — скажем, вот так:

CREATE OR REPLACE PROCEDURE PTEST(
                      I_PAR IN OUT CUSTOMERS.CUST_NUM%TYPE, 
                      II_PAR IN OUT CUSTOMERS.COMPANY%TYPE)
IS

BEGIN

	I_PAR := 15.6;
	II_PAR := 'POIUYTREWQLKJHGFDSA';

END PTEST;
/

Такой способ удобен тем, что при изменении полей таблицы автоматом меняются параметры процедур, что облегчает сопровождение кода хранимых процедур, не нужно менять все параметры связанные с данным полем! Получаем:

SQL> CREATE OR REPLACE PROCEDURE PTEST(
  2  	I_PAR IN OUT CUSTOMERS.CUST_NUM%TYPE,
  3  	II_PAR IN OUT CUSTOMERS.COMPANY%TYPE)
  4  IS
  5  
  6  BEGIN
  7  
  8  	I_PAR := 15.6;
  9  	II_PAR := 'POIUYTREWQLKJHGFDSA';
 10  
 11  END PTEST;
 12  /

Процедура создана.

Ошибок нет! Значит, все прошло успешно! Как работать с параметрами это дело вкуса, а на него, как говорится, товарищей совсем не бывает! Вот пока можете все это переварить, а я пойду попью чаю! :)

Понравилась статья? Поделить с друзьями:

Читайте также:

  • Ora 06502 pl sql numeric or value error null index table key value
  • Ora 06502 pl sql numeric or value error invalid lob locator specified
  • Oracle dbms utility format error backtrace
  • Ora 06502 pl sql numeric or value error character string buffer too small
  • Oracle dbms error text

  • 0 0 голоса
    Рейтинг статьи
    Подписаться
    Уведомить о
    guest

    0 комментариев
    Старые
    Новые Популярные
    Межтекстовые Отзывы
    Посмотреть все комментарии