Pl sql numeric or value error character string buffer too small перевод

Об этой ошибке: ORA-06502: числовая или значимая ошибка

 FOR this_loop IN (SELECT field_A, field_B FROM TABLE_NAME WHERE num = i_num) LOOP IF this_loop.field_B BETWEEN 1 AND 3 THEN v_A := v_A || ' ' || this_loop.field_A; ELSIF this_loop.field_B BETWEEN 4 AND 8 THEN v_field_A := v_field_A || ' ' || this_loop.field_A; -- Error is at this line ELSIF this_loop.field_B BETWEEN 9 AND 15 THEN v_B := v_B || ' ' || this_loop.field_A; END IF; END LOOP; 

Переменная обозначается как

v_field_A VARCHAR2 (100); 

Что я знаю —

1. Переменная v_field_A не может содержать значение более 100 символов.
2. Результат, который я получаю от SELECT Запрос не может содержать более 10 символов.

Мой вопрос — как вообще можно решить эту проблему с пространственным буфером, когда символы находятся в пределах лимита varchar2? Я столкнулся с этой проблемой несколько лет назад, но в прошлый раз причиной был выход select запрос. В нем было более 100 символов, и, следовательно, проблема с размером, но на этот раз не более 10 символов. Я в замешательстве. Любая помощь приветствуется

• сколько строк у вас в таблице?
• Есть более миллиона строк, но это происходит только с несколькими строками.
• Чтобы быть более конкретным, сколько строк извлекает цикл? 10 символов * 11 итераций цикла == вы столкнетесь с ошибкой, так как вы продолжаете конкатенировать значение переменной на каждой итерации
• почему нельзя увеличить размер v_field_A? это решит проблему
• @ Сатья Это зависит от обстоятельств. Обычно это не более 3-4 рядов.

Переменная v_field_A не может содержать значение более 100 символов.

Почему нет? Это очень возможно, так как вы сцепление переменная для каждой строки в КУРСОР ДЛЯ ПЕТЛИ.

Например,

SQL> DECLARE 2 v_name VARCHAR2(50); 3 BEGIN 4 FOR i IN 5 (SELECT ename FROM emp 6 ) 7 LOOP 8 v_name := v_name || i.ename; 9 END LOOP; 10 END; 11 / DECLARE * ERROR at line 1: ORA-06502: PL/SQL: numeric or value error: character string buffer too small ORA-06512: at line 8 

Использовать DBMS_OUTPUT чтобы увидеть текущий размер переменной и добавляемое новое значение.

Давайте отладим

SQL> DECLARE 2 v_name VARCHAR2(50); 3 BEGIN 4 FOR i IN 5 (SELECT ename FROM emp 6 ) 7 LOOP 8 dbms_output.put_line('Length of new value = '||LENGTH(i.ename)); 9 v_name := v_name || i.ename; 10 dbms_output.put_line('Length of variable = '||LENGTH(v_name)); 11 END LOOP; 12 END; 13 / Length of new value = 5 Length of variable = 5 Length of new value = 5 Length of variable = 10 Length of new value = 4 Length of variable = 14 Length of new value = 5 Length of variable = 19 Length of new value = 6 Length of variable = 25 Length of new value = 5 Length of variable = 30 Length of new value = 5 Length of variable = 35 Length of new value = 5 Length of variable = 40 Length of new value = 4 Length of variable = 44 Length of new value = 6 Length of variable = 50 Length of new value = 5 

ошибка

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

Это довольно ясно, мы хотели объединить строку длиной 5 к переменной, объявленной как максимальный размер 50, в настоящее время хранит значение размера 50. следовательно, он выдает ошибку ORA-06502: PL/SQL: numeric or value error: character string buffer too small.

• Поправьте меня если я ошибаюсь. если переменная может содержать более 50 или 100 символов. Проблема размера не должна существовать вовсе, верно?
• @PirateX Вы забываете тот факт, что вы объединяете значения в переменную в цикле, поэтому в какой-то момент при итерации по косякам он превышает заявленный размер. Я добавил в свой ответ пример.
• Хорошо, я думаю, что у меня есть идея. Это конкатенация, которая в какой-то момент превышает предел 100, вызывая ошибку. Правильно ?
• @PirateX Совершенно верно. Это то, что вы видите в DBMS_OUTPUT в примере, который я вам показал.
• @PirateX Пожалуйста, отметьте это как ответ, помогло бы и другим! Я вижу, что ни один из ваших вопросов никогда не был отмечен как отвеченный. Это хороший жест, и сообщество предлагает принять ответ, который решит вашу проблему. Удачи!

11 строк * 10 символов> 100 символов => ошибка — вы объединяете много строк по 10 символов вместе в одну из трех переменных. Один из них будет расти каждый раз в цикле.

Что мне не хватает?

Также остерегайтесь символов вместо байтов. В varchar2 каждый CHARACTER, вероятно, займет 2 байта.

Проверьте, сколько раз это условие выполняется и какие значения объединяются. Поскольку это объединяется несколько раз, существует вероятность того, что размер v_field_A превышает 50 символов.

ELSIF this_loop.field_B BETWEEN 4 AND 8 THEN v_field_A := v_field_A || ' ' || this_loop.field_A; 

Чтобы решить эту проблему, вы можете увеличить размер этой переменной. Вы можете объявить varchar размером до 32 767 байт

Вы можете выполнить конкатенацию строк в SQL-запросе:

SELECT field_A, LISTAGG(CASE WHEN field_B BETWEEN 1 AND 3 THEN field_A END, ' ') WITHIN GROUP (ORDER BY field_A) as val1, LISTAGG(CASE WHEN field_B BETWEEN 4 AND 8 THEN field_A END, ' ') WITHIN GROUP (ORDER BY field_A) as val2, LISTAGG(CASE WHEN field_B BETWEEN 9 AND 15 THEN field_A END, ' ') WITHIN GROUP (ORDER BY field_A) as val3 FROM TABLE_NAME WHERE num = i_num; 

Это сделано для упрощения вашего кода. Вы по-прежнему ограничены длиной строки Oracle. Вы можете обойти ограничение Oracle в PL / SQL с помощью CLOB, но в этом нет необходимости, если конечная строка состоит всего из нескольких сотен символов.

• 2 Даже LISTAGG имеет Ограничение SQL из 4000.

Ответ на ваш вопрос заключается в том, сколько раз цикл выполняется и сколько раз он входит в условие IF.

ПРИМЕР :

Состояние : МЕЖДУ 4 А ТАКЖЕ 8

this_loop.field_A: = ‘тест’;

Количество выполнений цикла = 100

Из-за КОНКАТИНАЦИЯ размер определенно вырастет более чем на 100 символов.

То же самое будет и с другими условиями LOOP.

Решение : Попробуйте использовать CLOB вместо того VARCHAR чтобы устранить эту проблему.

Ошибки Oracle очень наглядны. Если он вызывает ошибку, это в значительной степени объясняет сценарий: P.

← →
SergP ©
 
(2006-11-13 13:04)
[0]

← →
Sergey13 ©
 
(2006-11-13 13:10)
[1]

← →
Desdechado ©
 
(2006-11-13 13:14)
[2]

s VARCHAR2(10):=»123456789012345″;

← →
SergP ©
 
(2006-11-13 13:27)
[3]

← →
Sergey13 ©
 
(2006-11-13 13:30)
[4]

← →
Desdechado ©
 
(2006-11-13 13:32)
[5]

смысл такого
EXCEPTION
    WHEN OTHERS
    THEN
       RAISE;
 END;
обработчика?
без него тебе бы и строку показало

← →
ANB ©
 
(2006-11-13 13:34)
[6]

> SergP ©   (13.11.06 13:27) [3]

Хех. Известный косяк. Сохраняй на всех перехватах исключений ее трассировку. так сможешь найти место ошибки. И сделай все варчаровые переменные подлиннее. Оракл сильно от этого не напряжется. Кстати, возможный вариант, из-за чего может появится ошибка :
имеем S varchar2(10). S := «Вася123456»; На 10-ке (XE) вполне вероятна ошибка невлезания, т.к. русские буквы занимают по 2 байта. Лечится заменой varchar2(10) на varchar2(10 char)

← →
SergP ©
 
(2006-11-13 13:49)
[7]

Спасибо… Убрал этот обработчик и нашел где…

← →
Desdechado ©
 
(2006-11-13 13:52)
[8]

> русские буквы занимают по 2 байта
Это если NVARCHAR2 пытаться туда запихнуть. Или нет?

← →
SergP ©
 
(2006-11-13 14:01)
[9]

← →
ANB ©
 
(2006-11-13 14:15)
[10]

> Это если NVARCHAR2 пытаться туда запихнуть. Или нет?

Не. Просто константу. Причем на 9-ке с этим проблем не было.

← →
SergP ©
 
(2006-11-13 15:24)
[11]

← →
Desdechado ©
 
(2006-11-13 16:09)
[12]

← →
k2 ©
 
(2006-11-13 16:12)
[13]

Desdechado ©   (13.11.06 16:09) [12]
для nls_length_semantics умолчание изменилось вроде:
в 9-ке-char, в 10-ке-byte

← →
ANB ©
 
(2006-11-13 16:13)
[14]

> Desdechado ©   (13.11.06 16:09) [12]

ЭЭЭЭ. Просто пнули XE она сама все поставила. Не задавая лишних вопросов. Хотя, в принципе, указание размерности никому не мешает, зато код будет работать при любой настройке базы.

← →
Игорь Шевченко ©
 
(2006-11-13 16:26)
[15]

← →
Desdechado ©
 
(2006-11-13 16:32)
[16]

← →
k2 ©
 
(2006-11-13 16:35)
[17]

Desdechado ©   (13.11.06 16:32) [16]
знач наврала сорри, у меня это запросто

← →
ANB ©
 
(2006-11-13 16:43)
[18]

> Desdechado ©   (13.11.06 16:32) [16]

Не, это из-за кодировки. Почему то в XE по дефолту полуторнобайтовая кодировка врублена (не помню как она называется, однако dump хорошо это показывает).

← →
Игорь Шевченко ©
 
(2006-11-13 17:21)
[19]

← →
Игорь Шевченко ©
 
(2006-11-13 22:55)
[20]

← →
ANB ©
 
(2006-11-14 10:37)
[21]

← →
Игорь Шевченко ©
 
(2006-11-14 11:09)
[22]

← →
ANB ©
 
(2006-11-14 11:18)
[23]

> Игорь Шевченко ©   (14.11.06 11:09) [22]

Уй млин. А мы в базе не стали указывать. Только в хранимках. Хотя я просто все символьные поля, кроме особых случаев, сделал по 4000 байт и забил. Так что по идее влезет все.

← →
ANB ©
 
(2006-11-14 11:19)
[24]

> а гады говорят, что надо устанавливать nls_length_semantics
> для базы

А где ты это накопал ?

← →
Игорь Шевченко ©
 
(2006-11-14 11:45)
[25]

← →
ANB ©
 
(2006-11-14 11:57)
[26]

← →
Игорь Шевченко ©
 
(2006-11-14 12:16)
[27]

← →
ANB ©
 
(2006-11-14 12:20)
[28]

> Рекомендую: http://www.mini-mag.ru/?cid=2170&tov=163372

Тьфу, млин. Это ссылка на магазин.
Звиняйте — у меня свой авторитет есть Тем более он рулил проектом и я у него и спросил — делать так или не надо. Индексы составные поругались поначалу, он размер блока побольше сделал — и всех делов

← →
k2 ©
 
(2006-11-14 12:24)
[29]

и мы даже знаем кто
только Селко наверное тож пару раз проектами рулил

← →
Игорь Шевченко ©
 
(2006-11-14 12:59)
[30]

← →
ANB ©
 
(2006-11-14 13:04)
[31]

> Особенно в SQL*Plus удобно запросы с такими полями делать.
> ..

Я тоадом пользуюсь. он под ширину данных ширину колонки подгоняет

← →
Игорь Шевченко ©
 
(2006-11-14 13:12)
[32]

← →
ANB ©
 
(2006-11-14 15:48)
[33]

> Вот и остается SQL*Plus

Есть еще pl/sql девелопер, склнафигатор (жуткая гадость).
А разве жаба платная ?

← →
Игорь Шевченко ©
 
(2006-11-14 16:18)
[34]

← →
k2 ©
 
(2006-11-14 16:40)
[35]

ANB ©   (14.11.06 13:04) [31]
Андрей, а твоя жаба оракловые пакеты понимает на десятке? и какая тогда версия?

← →
Val ©
 
(2006-11-14 17:02)
[36]

>[15] Игорь Шевченко ©   (13.11.06 16:26)
>..Домены бы в Оракл — цены б ему не было.
да вроде %TYPE неплохо справляется…

← →
ANB ©
 
(2006-11-14 17:08)
[37]

← →
k2 ©
 
(2006-11-14 17:20)
[38]

у меня 8.0.0.47 категорически отказывается просматривать в 10-ке процедуры , функции и спецификацию пакета, хотя боди и просматривает и редактировать и компилить пожалуйста, такая загогулина :-/

← →
ANB ©
 
(2006-11-14 17:30)
[39]

> k2 ©   (14.11.06 17:20) [38]

Упс. И у меня такой же. Хотя я на живой 10-ке его не гонял — только на XE. Свежее версии нету ? Вполне возможно, что в этой не выгребли все баги. Таки 7-ка вообще не умела работать с ораклом 10, даже XE.

← →
k2 ©
 
(2006-11-14 17:36)
[40]

у меня 10.2.0.2
кстати если прихъодилось жабу под mssql-ем гонять пристойно себя ведет?

← →
Игорь Шевченко ©
 
(2006-11-14 17:42)
[41]

← →
k2 ©
 
(2006-11-14 17:57)
[42]

Игорь Шевченко ©   (14.11.06 17:42) [41]
Oracle Developer немножко знает про домены, после генерации на базе это выглядит как табличка cg_ref_codes (сейчас нет дизайнера, не знаю настраивается) вида:
CREATE TABLE <Schema>.CG_REF_CODES
(
 RV_DOMAIN        VARCHAR2(100)                NOT NULL,
 RV_LOW_VALUE     VARCHAR2(240)                NOT NULL,
 RV_HIGH_VALUE    VARCHAR2(240),
 RV_ABBREVIATION  VARCHAR2(240),
 RV_MEANING       VARCHAR2(240),
 RV_TYPE          VARCHAR2(10)
)
TABLESPACE USERS
LOGGING
NOCACHE
NOPARALLEL;
CREATE INDEX <Schema>.X_CG_REF_CODES_1 ON <Schema>.CG_REF_CODES
(RV_DOMAIN, RV_LOW_VALUE)
LOGGING
TABLESPACE <USERS>
NOPARALLEL;
DROP SYNONYM SGDEVNU1.CG_REF_CODES;
CREATE SYNONYM SGDEVNU1.CG_REF_CODES FOR <Schema>.CG_REF_CODES;
ALTER TABLE <Schema>.CG_REF_CODES ADD (
 CONSTRAINT RV_PK PRIMARY KEY (RV_DOMAIN, RV_LOW_VALUE));
если это те домены и ничего не попутала

← →
Val ©
 
(2006-11-14 17:57)
[43]

>[41] Игорь Шевченко ©   (14.11.06 17:42)
зачем?

← →
Игорь Шевченко ©
 
(2006-11-14 18:03)
[44]

← →
Игорь Шевченко ©
 
(2006-11-14 18:04)
[45]

← →
Val ©
 
(2006-11-14 18:06)
[46]

>[42] k2 ©   (14.11.06 17:57)
Здесь под доменом понимается определенный пользователем тип, который может быть и типом поля таблицы. Такого в Оракле пока я не встретил.

← →
Val ©
 
(2006-11-14 18:22)
[47]

>[44] Игорь Шевченко ©   (14.11.06 18:03)
Ну не знаю… Дело привычки. В ИБ привык, в Оракле затем отвык — оказалось не так уж и нужно мне это в таблицах.

← →
ANB ©
 
(2006-11-14 18:48)
[48]

> Игорь Шевченко ©   (14.11.06 18:04) [45]

Не. Как то же оракл разрешает создавать поля с объектными типами. Надо просто покопаться.
Хотя впрямую — да, обломс.

← →
Val ©
 
(2006-11-14 19:02)
[49]

>ANB ©   (14.11.06 18:48)
поля с объектыми типами? может, таблицы объектного типа?

← →
Desdechado ©
 
(2006-11-14 19:09)
[50]

Val ©   (14.11.06 19:02) [49]
и поля объектных типов тоже

← →
Val ©
 
(2006-11-14 19:17)
[51]

>[50] Desdechado ©   (14.11.06 19:09)
речь о вложенных таблицах?

← →
Desdechado ©
 
(2006-11-14 20:41)
[52]

Val ©   (14.11.06 19:17) [51]
нет, например:
CREATE TABLE TUNE(
 REGION_ID        NUMBER(10) NOT NULL,
 SDO_MIN_RECT     MDSYS.SDO_GEOMETRY,
 SDO_DIMINFO        MDSYS.SDO_DIM_ARRAY
);

← →
Игорь Шевченко ©
 
(2006-11-15 10:27)
[53]

← →
Val ©
 
(2006-11-15 14:24)
[54]

$id = 0;
$stmt->bindParam('o_ID', $id);

2015-12-18T13:02:06+00:00 ERR (3): An exception occurred while executing 'BEGIN PR_LOAD_XML_FILE (
i_XML => :i_XML,
i_FILENAME => :i_FILENAME,
o_ID => :o_ID,
on_ErrorID => :on_ErrorID
);
END;' with params ["<?xml version="1.0" encoding="UTF-8" ?><test><TestDetails><TestVersionId>3244</TestVersionId><Marks>20</Marks></TestDetails></test>", "test.csv"]:

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

PROCEDURE PR_LOAD_XML_FILE (
i_XML IN CLOB,
i_FILENAME IN XML_DOC.FILENAME%TYPE,
o_ID OUT NUMBER,
on_ErrorID OUT INTEGER
);

PROCEDURE PR_LOAD_XML_FILE (
i_XML IN CLOB,
i_FILENAME IN XML_DOC.FILENAME%TYPE,
o_ID OUT NUMBER,
on_ErrorID OUT INTEGER
) IS
doc_id XML_DOC.id%TYPE;
xml        XMLTYPE;
BEGIN
...
END PR_LOAD_XML_FILE;

Решение

CREATE OR REPLACE PACKAGE PKG_PR_LOAD_XML_FILE
AS
PROCEDURE PR_LOAD_XML_FILE (
i_XML IN CLOB,
i_FILENAME IN varchar2,
o_ID OUT NUMBER,
on_ErrorID OUT INTEGER );
END PKG_PR_LOAD_XML_FILE;
/
CREATE OR REPLACE PACKAGE BODY PKG_PR_LOAD_XML_FILE
AS
PROCEDURE PR_LOAD_XML_FILE (
i_XML IN CLOB,
i_FILENAME IN varchar2,
o_ID OUT NUMBER,
on_ErrorID OUT INTEGER )
IS

BEGIN
dbms_output.put_line( 'i_XML: '||i_XML );
dbms_output.put_line( 'i_FILENAME: '||i_FILENAME );
o_ID := 999999;
on_ErrorID := 123456789;
END PR_LOAD_XML_FILE;
END PKG_PR_LOAD_XML_FILE;
/

SQLPlus> var v_o_ID varchar2(4)
SQLPlus> var v_on_ErrorID number

SQLPlus> exec PKG_PR_LOAD_XML_FILE.PR_LOAD_XML_FILE( '<heres my XML>', 'heresfilename.csv', :v_o_ID, :v_on_ErrorID);
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error: character string buffer too small
ORA-06512: at line 1

SQLPlus> var v_o_ID number

SQLPlus> exec PKG_PR_LOAD_XML_FILE.PR_LOAD_XML_FILE( '<heres my XML>', 'heresfilename.csv', :v_o_ID, :v_on_ErrorID);
i_XML: <heres my XML>
i_FILENAME: heresfilename.csv

PL/SQL procedure successfully completed.
SQLPlus> print v_o_ID
V_O_ID
----------
999999

SQLPlus> print v_on_ErrorID
V_ON_ERRORID
------------
123456789

Другие решения

Других решений пока нет …

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

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.

Thank you for the help.

Best Answer

And now look at code I posted and compare your

CD_DESC VARCHAR2(200);

v_cd_desc varchar2(700); — now it fits

Answers

You didn’t show how procedure is called. Most likely OUT actual parameter size is too small:

SQL> declare

2 v_cd_desc varchar2(100); — too small

3 begin

4 p_state_codes(‘STATES’,v_cd_desc);

5 end;

6 /

declare

ERROR at line 1:

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

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

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

ORA-06512: at line 4

SQL> declare

2 v_cd_desc varchar2(700); — now it fits

3 begin

4 p_state_codes(‘STATES’,v_cd_desc);

5 end;

6 /

PL/SQL procedure successfully completed.

SQL>

Getting the error when when was running the stored procedure in SQL Developer and passing STATES as the input parameter.

Connecting to the database test_11.2.

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

Disconnecting from the database test_11.2.

However, when I run the same procedure from PL/SQL Developer, it works.

— Call the procedure

end;cd_type String STATES

cd_desc String AL;ALABAMA+AK;ALASKA+AZ;ARIZONA+AR;ARKANSAS+CA;CALIFORNIA+CO;COLORADO+CT;CONNECTICUT+DE;DELAWARE+DC;DISTRICT OF COLUMBIA+FL;FLORIDA+GA;GEORGIA+HI;HAWAII+ID;IDAHO+IL;ILLINOIS+IN;INDIANA+IA;IOWA+KS;KANSAS+KY;KENTUCKY+LA;LOUISIANA+ME;MAINE+MD;MARYLAND+MA;MASSACHUSETTS+MI;MICHIGAN+MN;MINNESOTA+MS;MISSISSIPPI+MO;MISSOURI+MT;MONTANA+NE;NEBRASKA+NV;NEVADA+NH;NEW HAMPSHIRE+NJ;NEW JERSEY+NM;NEW MEXICO+NY;NEW YORK+NC;NORTH CAROLINA+ND;NORTH DAKOTA+OH;OHIO+OK;OKLAHOMA+OR;OREGON+PA;PENNSYLVANIA+RI;RHODE ISLAND+SC;SOUTH CAROLINA+SD;SOUTH DAKOTA+TN;TENNESSEE+TX;TEXAS+UT;UTAH+VT;VERMONT+VA;VIRGINIA+WA;WASHINGTON+WV;WEST VIRGINIA+WI;WISCONSIN+WY;WYOMING+

Источник

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

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.

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.

Output

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.

Output

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.

Output

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.

Источник

How to solve numeric or value error: character string buffer too small?

I’m having this really simple PL/SQL anonymous block that returns the current time in seconds. I get an error when I run it.

So, here’s my (rather short) code: If I remove the «:P24_TIME := x_seconds_systime;» part, then it works like a charm. But I can’t just remove this, because I need to put the resulting value in this item (I use this code on APEX).

So, if I run it as it is now (and you can test it yourself), then it gives the following error: I assume that the to_char has something to do with this. Probably because the number it has to hold exceeds the limit of the max value a char can have. So, how do I work around this?

Any help is really appreciated, I’m stuck on this since yesterday morning and I still haven’t found it. Thanks.

Answers

Firstly, why are you using a clob? A number or perhaps varchar2 would do.

Secondly, what’s your definiton of p24_time. I have no problem
Edited by: Paul Horth on 25-Apr-2012 01:56

It’s not about the CLOB here, it’s about the to_char that’s failing.
I need the CLOB because It’ll be a larger number when I start calculating with it. Even a NUMBER won’t do.

So please, just a fix on how to get the to_char thing working. Just try the code yourself.

And just how are you going to do calculations on a CLOB which is a string?
A NUMBER datatype can hold some pretty big numbers. Just what exactly are you trying to do?

I wasn’t at that stage yet. I just wanted to get rid of the error.
The item p24_TIME is just a item with a static value in APEX. It’s not defined as anything, nor is it even a varchar2. Just a page item that holds some value, regardless of what data type that value was.

I used a CLOB because from what I found on the internet, that’s supposed to hold large amounts of data. Since I will be having data that has 34000 as a value or something, I figured that a varchar2 couldn’t hold it because that’s too large. That’s why I tried it with the CLOB.

Don’t shoot at me, kay? I just learned how to use a cursor last week, before that all I could do was fetch single row data and display that on a page in APEX.

Thanks for telling. I really thought that the value it should hold shouldn’t exceed the maximum, sorry about that.
If I replace CLOB with NUMBER and to_clob with to_number, I still get the same error.

Code is now like this:

First of all, if you want the number of seconds since midnight, Oracle date arithmetic makes this pretty easy.

SYSDATE contains the current date and time, including seconds.

TRUNC(SYSDATE) contains the current date at midnight.

SYSDATE — TRUNC(SYSDATE) gives a number that is the difference in days between the two dates, including a fraction for the time difference.

To get the number of seconds since midnight, multiply SYSDATE — TRUNC(SYSDATE) by the number of seconds in a day.

To assign a number to a VARCHAR2, always prefer an explicit conversion using TO_CHAR. ‘TM’ means «text minimum», which will produce the shortest possible string; otherwise you might get a space in front, which is a placeholder for the minus sign.Now, you are getting your error because :P24_ITEM is defined as a VARCHAR2 that is not long enough to hold the data. You can see the error if you define a PL/SQL variable in place of :P24_ITEMHow you redefine :P24_ITEM to be longer is an APEX problem.

Edited by: Stew Ashton on Apr 25, 2012 12:22 PM

Источник

Smart way of Technology

Worked in Database technology for fixed the issues faced in daily activities in Oracle, MS SQL Server, MySQL, MariaDB etc.

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

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

This error occurred while varchar character is overflow from its specified length. In this blog, we show you the example by generating error and fixed it by increasing the size of varchar character.

Error:
Generate error while fetching data which is concatenating in the VAR variable having 1000 bytes limit defined in DECLARE part.

set serveroutput on
DECLARE
—var varchar2(32767);
—var varchar2(20000);
var varchar2(1000); — Size is 1000 bytes
var1 number;
CURSOR cn IS SELECT object_name FROM dba_objects where rownum NULL);
FOR v_objectname IN cn
LOOP
var1 := 1+var1;
var := var || v_objectname.object_name;
dbms_output.put_line(var1);
—Dbms_output.put_line(var);
END LOOP;
END;
/

OUTPUT
1
2
.
.
119
120
DECLARE
*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error: character string buffer too small
ORA-06512: at line 14
ORA-06512: at line 14

Solution
Increase the limit of VAR variable to 2000, it will fixed the issue.
Maximum limit of VARCHAR2 is 32767.

set serveroutput on
DECLARE
var varchar2(2000); — Size is 2000 bytes
var1 number;
CURSOR cn IS SELECT object_name FROM dba_objects where rownum NULL);
FOR v_objectname IN cn
LOOP
var1 := 1+var1;
var := var || v_objectname.object_name;
dbms_output.put_line(var1);
—Dbms_output.put_line(var);
END LOOP;
END;
/

PL/SQL procedure successfully completed.

Like this:

About SandeepSingh DBA

Hi, I am working in IT industry with having more than 10 year of experience, worked as an Oracle DBA with a Company and handling different databases like Oracle, SQL Server , DB2 etc Worked as a Development and Database Administrator.

Leave a Reply Cancel reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Источник

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

I have a PL/SQL function body returning SQL query. The select statement that I’m trying to return is huge (lots of hidden ID columns etc. ), much larger than the initial variable I had defined, v_query VARCHAR2 (32767). So I defined my variable as a CLOB instead but I still can’t return the value, I get the same error, it’s almost like there’s a limit on the size of the return variable!

What’s the work around, how can I return this huge select statement from the source region and use it to build my layout.

Answers

Probably you can create a view and then select statement will be :
select * from HUGE_VIEW

if your function returns a varchar2, there is still the same limit of 32767. So your function should also return a clob. But a SQL with this size is of course possible but is not very handy (also not for the optimizer), I think you also have to look to optimize the SQL code.

Herald ten Dam
http://htendam.wordpress.com

My function DOES I return a clob, but it doesn’t work. I get the same error (ORA-06502: PL/SQL: numeric or value error: character string buffer too small) that I get if I return a varchar2(32767).

I’m trying to return a large sql statement that displays multiple lines of data with about 20 columns per row. I need to use PL/SQL function body returning query because of the dynamic nature of the data.

Instead of returning a string representation of the query, why not return a collection and query it via th «TABLE» keyword in your query:

1. Amend your function to return a collection (I’d also recommend making it pipelined for performance) e.g.

(You will first need to define a collection type, either by basing it on an existing table ala the %rowtype keyword, or by creating a collection of a custom record type — see documentation for more details)
2. In the appropriate report region, your query should now be ‘static’ and along the lines of:
The above assumes a static ‘select’ clause i.e. if your function currently returns a variable number of fields, it will cause you problems.

p.s. I’m back now, although the OTN forum appear to have ‘lost’ my old account as a result of me attempting to change the email address when the error happened last week, so I’ve lost ALL my helpful points. Thanks OTN! 🙂

I will look into this, but yes, my function returns a variable number of fields, in other words nothing about the select statement I need to build is static. Think of it this way, the number of columns in the region and the heading for those columns are dependant on the current customer. The customer setups are kept in other tables which I query to build the layout in the region for this particular customer, so I really can’t hard code any type of select statement. I’ve thought of building a view on the fly that always has the same name but returns very different things based on the current customer, but what if two users tried to use the form at the same time? The view would get re-created by the second user and blow up what the first user is working on.

Edited by: blue4speed on Oct 1, 2010 12:02 PM

The limitation that you are running into isn’t a limitation of ApEx, it’s a limitation of dynamic SQL (which is what the PL/SQL function returning query is passed to). The ApEx engine behind the scenes is going to try to execute the query you dynamically build either via an EXECUTE IMMEDIATE statement or via the DBMS_SQL package. I don’t know which it uses, but both have a 32K-character limitation on the statement to be executed.

A 32K-character SQL statement, assuming a conservative 40-characters per line, is a 800+ line query statement. Even if it could be made to work, I’d be amazed if it performed well. Just a suggestion, but I’d look to re-working the approach.

Hope this helps,
John

If you find this information useful, please remember to mark the post «helpful» or «correct».

Thanks jrager, that’s helpful. I’ve had a lot of people tell me my query is too long, but think about how much has to be in there that’s not data related. I’m trying to show quote lines from the Oracle quote module in a APEX form, some quote have 200-300 lines. Here’s just 2 lines generated by my sql statement, this shows about 20 columns, this is 3503 characters so think what happens when I get a 50 line quote.

SELECT apex_item.text (22,’1′,5,100,’readonly=»readonly»‘,’f22_’||LPAD (ROWNUM, 4, ‘0’)) «Line Number», apex_item.text (22,’X4270-S1-AA’,20,100,’readonly=»readonly»‘,’f22_’||LPAD (ROWNUM, 4, ‘0’)) «Item Number», apex_item.text (22,’SUN FIRE X4270 X64 SERVER: 2.5-INCH HDD BASE CHASSIS PACKAGE INCLUDING MOTHERBOARD, NO DVD, 1 X PSU, REDUNDANT FANS AND SERVICE PROCESSOR FOR FACTORY INTEGRATION. ROHS-6.’,50,100,’readonly=»readonly»‘,’f22_’||LPAD (ROWNUM, 4, ‘0’)) «Description», apex_item.text (22,’8′,5,100,’readonly=»readonly»‘,’f22_’||LPAD (ROWNUM, 4, ‘0’)) «Quantity», apex_item.text (22,’EA’,7,100,’readonly=»readonly»‘,’f22_’||LPAD (ROWNUM, 4, ‘0’)) «UOM», apex_item.hidden(22,3749) , apex_item.text (22,’test’,12,100,’readonly=»readonly»‘,’f22_’||LPAD (ROWNUM, 4, ‘0’)) «Project Number», apex_item.hidden(22,3750) , apex_item.text (22,’test’,12,100,’readonly=»readonly»‘,’f22_’||LPAD (ROWNUM, 4, ‘0’)) «Project Name», apex_item.hidden(22,3751) , apex_item.text (22,’test’,12,100,’readonly=»readonly»‘,’f22_’||LPAD (ROWNUM, 4, ‘0’)) «Data Center», apex_item.hidden(22,3752) , apex_item.text (22,’test’,12,100,’readonly=»readonly»‘,’f22_’||LPAD (ROWNUM, 4, ‘0’)) «Line of Business», apex_item.hidden(22,3753) , apex_item.text (22,’test’,12,100,’readonly=»readonly»‘,’f22_’||LPAD (ROWNUM, 4, ‘0’)) «Cost Center», apex_item.hidden(22,3754) , apex_item.text (22,’test’,12,100,’readonly=»readonly»‘,’f22_’||LPAD (ROWNUM, 4, ‘0’)) «Project Manager», apex_item.hidden(22,3755) , apex_item.text (22,’test’,12,100,’readonly=»readonly»‘,’f22_’||LPAD (ROWNUM, 4, ‘0’)) «Buyer», apex_item.hidden(22,3756) , apex_item.text (22,’test’,12,100,’readonly=»readonly»‘,’f22_’||LPAD (ROWNUM, 4, ‘0’)) «Order Classification» FROM aso_quote_lines
WHERE quote_header_id = 18430 and line_number = 1
UNION
SELECT
apex_item.text (22,’2′,5,100,’readonly=»readonly»‘,’f22_’||LPAD (ROWNUM, 4, ‘0’)) «Line Number», apex_item.text (22,’X9238-1-A’,20,100,’readonly=»readonly»‘,’f22_’||LPAD (ROWNUM, 4, ‘0’)) «Item Number», apex_item.text (22,’POWER JUMPER CABLE, 2.5 METER (QTY 1), FOR SUN RACK 900/1000. THIS PRODUCT IS HAZARD CLASS Y, ROHS COMPLIANT.’,50,100,’readonly=»readonly»‘,’f22_’||LPAD (ROWNUM, 4, ‘0’)) «Description», apex_item.text (22,’16’,5,100,’readonly=»readonly»‘,’f22_’||LPAD (ROWNUM, 4, ‘0’)) «Quantity», apex_item.text (22,’EA’,7,100,’readonly=»readonly»‘,’f22_’||LPAD (ROWNUM, 4, ‘0’)) «UOM», apex_item.hidden(22,3757) , apex_item.text (22,’test’,12,100,’readonly=»readonly»‘,’f22_’||LPAD (ROWNUM, 4, ‘0’)) «Project Number», apex_item.hidden(22,3758) , apex_item.text (22,’test’,12,100,’readonly=»readonly»‘,’f22_’||LPAD (ROWNUM, 4, ‘0’)) «Project Name», apex_item.hidden(22,3759) , apex_item.text (22,’test’,12,100,’readonly=»readonly»‘,’f22_’||LPAD (ROWNUM, 4, ‘0’)) «Data Center», apex_item.hidden(22,3760) , apex_item.text (22,’test’,12,100,’readonly=»readonly»‘,’f22_’||LPAD (ROWNUM, 4, ‘0’)) «Line of Business», apex_item.hidden(22,3761) , apex_item.text (22,’test’,12,100,’readonly=»readonly»‘,’f22_’||LPAD (ROWNUM, 4, ‘0’)) «Cost Center», apex_item.hidden(22,3762) , apex_item.text (22,’test’,12,100,’readonly=»readonly»‘,’f22_’||LPAD (ROWNUM, 4, ‘0’)) «Project Manager», apex_item.hidden(22,3763) , apex_item.text (22,’test’,12,100,’readonly=»readonly»‘,’f22_’||LPAD (ROWNUM, 4, ‘0’)) «Buyer», apex_item.hidden(22,3764) , apex_item.text (22,’test’,12,100,’readonly=»readonly»‘,’f22_’||LPAD (ROWNUM, 4, ‘0’)) «Order Classification» FROM aso_quote_lines
WHERE quote_header_id = 18430 and line_number = 2

You may ask why I do it this way, the final 8 columns and the data is determined dymanically by data held in other tables. The data maybe different for each quote line, here’s the code.

DECLARE
v_query clob;
v_counter NUMBER := 22;

— define the main cursor loop to return the lines for the quote
FOR quote_lines IN
(SELECT line_number
,mtl.segment1
,mtl.description
,line.quantity
,line.uom_code
,line.quote_line_id
FROM aso_quote_lines_all line
,mtl_system_items_b mtl
WHERE
mtl.organization_id = line.organization_id
AND line.inventory_item_id = mtl.inventory_item_id
AND line.quote_header_id = :P3_QUOTE_HEADER_ID)
—AND ROWNUM = 1)
—AND line.quote_header_id = 13643)

— Get the values for each line returned by the main loop
FOR c in
(SELECT »»||quote_lines.line_number||»» line_number
,»»||quote_lines.segment1||»» item_number
,»»||quote_lines.description||»» item_description
,»»||quote_lines.quantity||»» quantity
,»»||quote_lines.uom_code||»» uom
FROM aso_quote_lines line
WHERE quote_header_id = :P3_QUOTE_HEADER_ID
AND line_number = quote_lines.line_number
ORDER BY quote_lines.line_number)

— Define the items for the read only line values
v_query :=
v_query
|| ‘apex_item.text (‘
|| v_counter
|| ‘,’
|| c.line_number
|| ‘,’
|| ‘5,’
|| ‘100,’
|| »’readonly=»readonly»»,’
|| »’f’
|| LPAD (v_counter, 2, 0)
|| ‘_»’
|| ‘||LPAD (ROWNUM, 4, »0»)’
|| ‘) ‘
|| ‘»‘||’Line Number’||’»‘
|| ‘, ‘;

—v_counter := v_counter + 1;

v_query :=
v_query
|| ‘apex_item.text (‘
|| v_counter
|| ‘,’
|| c.item_description
|| ‘,’
|| ’50,’
|| ‘100,’
|| »’readonly=»readonly»»,’
|| »’f’
|| LPAD (v_counter, 2, 0)
|| ‘_»’
|| ‘||LPAD (ROWNUM, 4, »0»)’
|| ‘) ‘
|| ‘»‘||’Description’||’»‘
|| ‘, ‘;

— Get the column names, values and ids
FOR d in
(SELECT
—»»||replace(label_name,»»)||»» column_name
label_name column_name
—,»»||replace(value,»»)||»» value —field values
— Account for ticks in data i.e. ‘Guns N’ Roses’
,(»»||replace(value,»»,»»»)||»») value
,value_id
FROM
forx_label_transactions trans
,forx_hz_cust_level_labels lab
,forx_hz_cust_levels cust_lev
,forx_levels lev
,forx_hz_mvp_customers cust
WHERE lab.cust_level_id = cust_lev.cust_level_id
AND cust_lev.level_id = lev.level_id
AND cust_lev.mvp_id = cust.mvp_id
AND cust.cust_account_id = :P3_CUST_ACCOUNT_ID
AND lev.level_name = ‘Quote Line’
AND trans.source_transaction_id(+) = quote_lines.quote_line_id
AND trans.label_id(+) = lab.label_id
and sysdate between lab.start_date_active
and nvl(lab.end_date_active,sysdate +1)
ORDER BY lab.position asc)

— Define the items to hold the custom field values

—v_counter := v_counter + 1;

v_query := RTRIM (v_query, ‘, ‘);

v_query := v_query || ‘ FROM aso_quote_lines ‘ || ‘ WHERE quote_header_id = ‘||:P3_QUOTE_HEADER_ID ||’ and line_number = ‘||quote_lines.line_number
||’ UNION SELECT ‘;

Edited by: blue4speed on Oct 1, 2010 12:52 PM

Edited by: blue4speed on Oct 1, 2010 12:53 PM

Edited by: blue4speed on Oct 1, 2010 12:56 PM

Edited by: blue4speed on Oct 1, 2010 1:05 PM

Edited by: blue4speed on Oct 1, 2010 1:05 PM

Edited by: blue4speed on Oct 1, 2010 1:06 PM

Edited by: blue4speed on Oct 1, 2010 1:09 PM

Источник