Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Oracle Новый топик    Ответить
 результат строковой конкатинации слишком велик  [new]
kmskmskms
Member

Откуда:
Сообщений: 18
при попытке выполнить функцию вот такая вот ошибка.

Сама функция:
create or replace FUNCTION FN_1GKB_GET_TEXT_ANSWERS (var_form_id IN VARCHAR2,var_from IN VARCHAR2,var_to IN VARCHAR2) RETURN clob AS
res clob;
BEGIN
SELECT LISTAGG(text,'') WITHIN GROUP (ORDER BY sortcode ASC) INTO res
FROM (SELECT
CASE
WHEN typ = 1 THEN
text ||
CASE
WHEN SUBSTR(text,-1) != '.' AND SUBSTR(text,-1) != '!' AND SUBSTR(text,-1) != '?' THEN '.'
END ||
CASE
WHEN (lag(typ,1) over (ORDER BY sortcode DESC) = 0) THEN chr(10)
WHEN (lag(typ,1) over (ORDER BY sortcode DESC) = 1) THEN ' '
END
WHEN typ = 0 THEN
CASE WHEN text = ' ' THEN '' ELSE '~b' || text || '.b& ' END
END text,sortcode,typ FROM
(SELECT text,sortcode,
CASE WHEN typ = 0 THEN
CASE WHEN (lag(typ,1) over (ORDER BY sortcode DESC) = 1) THEN 0 ELSE 1 END
ELSE 0
END del,typ
FROM (SELECT CASE WHEN fi.text = ' ' THEN '' ELSE fi.text || ' ' END || TRIM(fv.text) AS text, fi.sortcode, fi.typ
FROM
solution_reg.form_result_desc fd,
solution_form.form_item fi,
solution_reg.form_result_value_desc fv
WHERE fd.id = var_form_id
AND fv.form_result_id = fd.id
AND fi.id = fv.form_item_id
AND fv.text <> ' '
AND fi.typ=1
AND fi.sortcode BETWEEN var_from AND var_to
UNION ALL
SELECT fi.text, fi.sortcode, fi.typ
FROM
solution_reg.form_result_desc fd,
solution_form.form_item fi
WHERE fd.id = var_form_id
AND fi.form_id = fd.form_id
AND fi.typ=0
AND fi.sortcode BETWEEN var_from AND var_to
ORDER BY sortcode ASC))
WHERE del != 1);
RETURN res;
END FN_1GKB_GET_TEXT_ANSWERS;

вызов функции:
SELECT
FN_1GKB_GET_TEXT_ANSWERS('117094023',0,9999) FORM
FROM DUAL
17 ноя 21, 13:00    [22396926]     Ответить | Цитировать Сообщить модератору
 Re: результат строковой конкатинации слишком велик  [new]
PuM256
Member

Откуда:
Сообщений: 168
ОК, понятно.
17 ноя 21, 13:06    [22396932]     Ответить | Цитировать Сообщить модератору
 Re: результат строковой конкатинации слишком велик  [new]
kmskmskms
Member

Откуда:
Сообщений: 18
в чём тут проблема?
17 ноя 21, 13:09    [22396935]     Ответить | Цитировать Сообщить модератору
 Re: результат строковой конкатинации слишком велик  [new]
PuM256
Member

Откуда:
Сообщений: 168
результат строковой конкатинации слишком велик
17 ноя 21, 13:21    [22396949]     Ответить | Цитировать Сообщить модератору
 Re: результат строковой конкатинации слишком велик  [new]
Агрох
Member

Откуда:
Сообщений: 136
Скорее всего это потому, что когда конкатенацией в запросе собираешь строку из строк типа VARCHAR то результатом и будет строка типа VARCHAR, со всеми положенными ограничениями.
17 ноя 21, 13:28    [22396958]     Ответить | Цитировать Сообщить модератору
 Re: результат строковой конкатинации слишком велик  [new]
kmskmskms
Member

Откуда:
Сообщений: 18
и как можно решить эту проблему?
17 ноя 21, 13:30    [22396961]     Ответить | Цитировать Сообщить модератору
 Re: результат строковой конкатинации слишком велик  [new]
AmKad
Member

Откуда:
Сообщений: 5344
kmskmskms
и как можно решить эту проблему?
Обрезать строку до допустимого размера varchar2 или вернуть clob/xml. Последние версии, емнип, позволяют сделать первое элегантно средствами той же listagg.

Сообщение было отредактировано: 17 ноя 21, 13:47
17 ноя 21, 13:40    [22396967]     Ответить | Цитировать Сообщить модератору
 Re: результат строковой конкатинации слишком велик  [new]
PuM256
Member

Откуда:
Сообщений: 168
Ну или max_string_size = extended, но это на любителя.
17 ноя 21, 13:47    [22396972]     Ответить | Цитировать Сообщить модератору
 Re: результат строковой конкатинации слишком велик  [new]
Dimitry Sibiryakov
Member

Откуда:
Сообщений: 54798

Собирать прямо в CLOB функциями соответствующего пакета.

Posted via ActualForum NNTP Server 1.5

17 ноя 21, 13:47    [22396973]     Ответить | Цитировать Сообщить модератору
 Re: результат строковой конкатинации слишком велик  [new]
AmKad
Member

Откуда:
Сообщений: 5344
PuM256
Ну или max_string_size = extended, но это на любителя.
Это всего лишь отсрочит момент срабатывания взрывателя.
17 ноя 21, 13:49    [22396975]     Ответить | Цитировать Сообщить модератору
 Re: результат строковой конкатинации слишком велик  [new]
AmKad
Member

Откуда:
Сообщений: 5344
Dimitry Sibiryakov

Собирать прямо в CLOB функциями соответствующего пакета.
Можно и без пакета собрать средствами xmlagg и последующей конвертацией в clob.
17 ноя 21, 13:51    [22396979]     Ответить | Цитировать Сообщить модератору
 Re: результат строковой конкатинации слишком велик  [new]
Dimitry Sibiryakov
Member

Откуда:
Сообщений: 54798

AmKad
Можно и без пакета собрать средствами xmlagg и последующей конвертацией в clob.

Именно это он уже и делает.

Posted via ActualForum NNTP Server 1.5

17 ноя 21, 14:03    [22396987]     Ответить | Цитировать Сообщить модератору
 Re: результат строковой конкатинации слишком велик  [new]
AmKad
Member

Откуда:
Сообщений: 5344
Dimitry Sibiryakov

AmKad
Можно и без пакета собрать средствами xmlagg и последующей конвертацией в clob.

Именно это он уже и делает.
Если мы говорим про код в первом посте, то нет.
17 ноя 21, 14:15    [22396994]     Ответить | Цитировать Сообщить модератору
 Re: результат строковой конкатинации слишком велик  [new]
Dimitry Sibiryakov
Member

Откуда:
Сообщений: 54798

AmKad
Если мы говорим про код в первом посте, то нет.

Форматирование там, конечно, отсутствует как класс, но я вижу всего один
мегазапрос, обёрнутый как раз в list_agg в clob. Чего я не замечаю?

Posted via ActualForum NNTP Server 1.5

17 ноя 21, 14:32    [22397001]     Ответить | Цитировать Сообщить модератору
 Re: результат строковой конкатинации слишком велик  [new]
kmskmskms
Member

Откуда:
Сообщений: 18
ты не замечаешь того что это LISTAGG а не XMLAGG.
а вообще не подскажете как будет выглядеть аналог для
SELECT LISTAGG(text,'') WITHIN GROUP (ORDER BY sortcode ASC) с использованием XMLAGG?
17 ноя 21, 14:37    [22397005]     Ответить | Цитировать Сообщить модератору
 Re: результат строковой конкатинации слишком велик  [new]
andrey_anonymous
Member

Откуда: Москва
Сообщений: 19924
Ох уж этот copypaste-driven девелопмент...
1. Изложите свою мегафункцию, раз уж всё одно вылезли в PL/SQL, на PL/SQL.

create or replace FUNCTION FN_1GKB_GET_TEXT_ANSWERS(var_form_id IN VARCHAR2,
                                                    var_from    IN VARCHAR2,
                                                    var_to      IN VARCHAR2)
  RETURN clob AS
  res clob;
BEGIN
  dbms_lob.createtemporary(res,false);
  for i in ( SELECT ...
       ) loop
         dbms_lob.writeappend(res, length(i.text), i.text);
       end loop;
  RETURN res;
END FN_1GKB_GET_TEXT_ANSWERS;


2. Раз уж вылезли в PL/SQL - то стоит избавиться от unon all и сделать необходимую логику в цикле по одному проходу через таблички.

3. Подрываетесь вы, к гадалке не ходи, на конкатенациях - если их выполнить не в запросе, а в pl/sql, где varchar2 длиннее, то проскочите.
Да и текст сопровождать будет попроще.

Dimitry Sibiryakov
Чего я не замечаю?

Контатенаций в copypaste-style мегазапросе :)

Сообщение было отредактировано: 17 ноя 21, 15:01
17 ноя 21, 14:58    [22397016]     Ответить | Цитировать Сообщить модератору
 Re: результат строковой конкатинации слишком велик  [new]
kmskmskms
Member

Откуда:
Сообщений: 18
andrey_anonymous
Ох уж этот copypaste-driven девелопмент...
1. Изложите свою мегафункцию, раз уж всё одно вылезли в PL/SQL, на PL/SQL.

create or replace FUNCTION FN_1GKB_GET_TEXT_ANSWERS(var_form_id IN VARCHAR2,
                                                    var_from    IN VARCHAR2,
                                                    var_to      IN VARCHAR2)
  RETURN clob AS
  res clob;
BEGIN
  dbms_lob.createtemporary(res,false);
  for i in ( SELECT ...
       ) loop
         dbms_lob.writeappend(res, length(i.text), i.text);
       end loop;
  RETURN res;
END FN_1GKB_GET_TEXT_ANSWERS;


2. Раз уж вылезли в PL/SQL - то стоит избавиться от unon all и сделать необходимую логику в цикле по одному проходу через таблички.

3. Подрываетесь вы, к гадалке не ходи, на конкатенациях - если их выполнить не в запросе, а в pl/sql, где varchar2 длиннее, то проскочите.
Да и текст сопровождать будет попроще.

Dimitry Sibiryakov
Чего я не замечаю?

Контатенаций в copypaste-style мегазапросе :)


не работает

функция
create or replace FUNCTION FN_1GKB_TEST (var_form_id IN VARCHAR2,var_from IN VARCHAR2,var_to IN VARCHAR2) RETURN clob AS
res	clob;
BEGIN

dbms_lob.createtemporary(res,false);

FOR i IN (SELECT 
CASE 
WHEN typ = 1 THEN 
    text || 
    CASE 
        WHEN SUBSTR(text,-1) != '.' AND SUBSTR(text,-1) != '!' AND SUBSTR(text,-1) != '?' THEN '.'
    END ||
    CASE 
        WHEN (lag(typ,1) over (ORDER BY sortcode DESC) = 0) THEN chr(10)
        WHEN (lag(typ,1) over (ORDER BY sortcode DESC) = 1) THEN ' ' 
    END
WHEN typ = 0 THEN 
    CASE WHEN text = ' ' THEN '' ELSE '~b' || text || '.b& ' END
END text,sortcode,typ FROM
(SELECT text,sortcode,
CASE WHEN typ = 0 THEN
    CASE WHEN (lag(typ,1) over (ORDER BY sortcode DESC) = 1) THEN 0 ELSE 1 END
ELSE 0
END del,typ
FROM (SELECT CASE WHEN fi.text = ' ' THEN '' ELSE  fi.text || ' ' END ||  TRIM(fv.text) AS  text, fi.sortcode, fi.typ
FROM 
solution_reg.form_result_desc fd,
solution_form.form_item fi,
solution_reg.form_result_value_desc fv
WHERE fd.id = var_form_id
	AND fv.form_result_id = fd.id
	AND fi.id = fv.form_item_id
	AND fv.text <> ' '
	AND fi.typ=1
	AND fi.sortcode BETWEEN var_from AND var_to
UNION ALL
SELECT fi.text, fi.sortcode, fi.typ
FROM 
solution_reg.form_result_desc fd,
solution_form.form_item fi
WHERE fd.id = var_form_id
	AND fi.form_id = fd.form_id
	AND fi.typ=0
	AND fi.sortcode BETWEEN var_from AND var_to
ORDER BY sortcode ASC))
) LOOP
    dbms_lob.writeappend(res, length(i.text), i.text);
END LOOP;

RETURN res;
END;


вызов:
SELECT
FN_1GKB_TEST('117094023',0,9999) FORM
FROM DUAL


ошибка:
ORA-06502: PL/SQL: ошибка числа или значения
ORA-06512: на "SYS.DBMS_LOB", line 1163
ORA-06512: на "SOLUTION_MED.FN_1GKB_TEST", line 49
ORA-06512: на "SOLUTION_MED.FN_1GKB_TEST", line 49
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.
17 ноя 21, 15:45    [22397047]     Ответить | Цитировать Сообщить модератору
 Re: результат строковой конкатинации слишком велик  [new]
AmKad
Member

Откуда:
Сообщений: 5344
kmskmskms
SELECT LISTAGG(text,'') WITHIN GROUP (ORDER BY sortcode ASC) с использованием XMLAGG?
select rtrim(xmlcast(xmlagg(xmlelement("x", object_name || ',')) as clob), ',') x
from user_objects
where rownum <= 1000;
17 ноя 21, 16:14    [22397074]     Ответить | Цитировать Сообщить модератору
 Re: результат строковой конкатинации слишком велик  [new]
kmskmskms
Member

Откуда:
Сообщений: 18
andrey_anonymous, короче он так ошибкой реагирует если попадается text с пустой строкой
17 ноя 21, 17:42    [22397130]     Ответить | Цитировать Сообщить модератору
 Re: результат строковой конкатинации слишком велик  [new]
Stax
Member

Откуда: Ukraine,Lviv
Сообщений: 3279
kmskmskms,

SQL> ed
Wrote file afiedt.buf

  1  declare
  2   res clob;
  3   v_text varchar2(100);
  4  BEGIN
  5   dbms_lob.createtemporary(res,false);
  6   dbms_lob.writeappend(res, length(v_text), v_text);
  7   dbms_lob.freetemporary(res);
  8* end;
SQL> /
declare
*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error
ORA-06512: at "SYS.DBMS_LOB", line 1146
ORA-06512: at line 6


SQL> ed
Wrote file afiedt.buf

  1  declare
  2   res clob;
  3   v_text varchar2(100);
  4  BEGIN
  5   dbms_lob.createtemporary(res,false);
  6   if v_text is not null then
  7     dbms_lob.writeappend(res, length(v_text), v_text);
  8   end if;
  9   dbms_lob.freetemporary(res);
 10* end;
SQL> /

PL/SQL procedure successfully completed.

SQL>


.....
stax
17 ноя 21, 18:15    [22397150]     Ответить | Цитировать Сообщить модератору
Все форумы / Oracle Ответить