Ох уж этот copypaste-driven девелопмент...
1. Изложите свою мегафункцию, раз уж всё одно вылезли в PL/SQL, на PL/SQL.
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
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 длиннее, то проскочите.
Да и текст сопровождать будет попроще.
Контатенаций в copypaste-style мегазапросе :)
не работает
функция
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.
47.
48.
49.
50.
51.
52.
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;
вызов:
1.
2.
3.
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.
