我遇到了 Oracle 管道功能的問題，我很想知道發生了什么。My Oracle Database 是在 Red Hat 7.2 上運行的 19c 版，并配置AL32UTF8為 CharacterSet。
讓我解釋一下這個場景。
為了使用并行行程生成檔案，我有以下兩種型別和一種管道功能的設定，因此我可以難以置信地加快大檔案的生成。
兩種型別
--
-- DUMP_PARALLEL_OBJECT (Type)
--
CREATE OR REPLACE TYPE CPL_DATA_OUT.dump_parallel_object AS OBJECT
(file_name VARCHAR2 (128), no_records NUMBER, seq_id NUMBER);
/
--
-- DUMP_PARALLEL_OBJECT_NTT (Type)
--
CREATE OR REPLACE TYPE CPL_DATA_OUT.dump_parallel_object_ntt AS TABLE OF cpl_data_out.dump_parallel_object;
/
流水線函式
這是管道功能，用于獲取我可以加入然后cat在 Linux 中使用的塊中的輸出檔案。
CREATE OR REPLACE function CPL_DATA_OUT.fn_generate_parallel_file
(
p_source IN SYS_REFCURSOR,
p_filename IN VARCHAR2,
p_directory IN VARCHAR2,
p_extension IN VARCHAR2 DEFAULT 'csv',
p_limit IN NUMBER DEFAULT 10000
) return dump_parallel_object_ntt
pipelined
parallel_enable (partition p_source by any)
as
type row_ntt is table of varchar2(32767);
v_rows row_ntt;
v_file UTL_FILE.FILE_TYPE;
v_buffer VARCHAR2(32767);
v_sid NUMBER;
v_name VARCHAR2(128);
v_lines PLS_INTEGER := 0;
c_eol CONSTANT VARCHAR2(1) := CHR(10);
c_eollen CONSTANT PLS_INTEGER := LENGTH(c_eol);
c_maxline CONSTANT PLS_INTEGER := 32767;
begin
SELECT generate_random_number.nextval INTO v_sid FROM dual;
v_name := p_filename || '_' || TO_CHAR(v_sid) || '.' || p_extension;
v_file := UTL_FILE.FOPEN(p_directory, v_name, 'w', 32767);
LOOP
FETCH p_source BULK COLLECT INTO v_rows LIMIT p_limit;
FOR i IN 1 .. v_rows.COUNT LOOP
IF LENGTH(v_buffer) c_eollen LENGTH(v_rows(i)) <= c_maxline THEN
v_buffer := v_buffer || c_eol || v_rows(i);
ELSE
IF v_buffer IS NOT NULL THEN
UTL_FILE.PUT_LINE(v_file, v_buffer);
END IF;
v_buffer := v_rows(i);
END IF;
END LOOP;
v_lines := v_lines v_rows.COUNT;
EXIT WHEN p_source%NOTFOUND;
END LOOP;
CLOSE p_source;
UTL_FILE.PUT_LINE(v_file, v_buffer);
UTL_FILE.FCLOSE(v_file);
PIPE ROW (dump_parallel_object(v_name, v_lines, v_sid));
RETURN;
END fn_generate_parallel_file;
/
我在函式內部使用序列為這些檔案分配唯一編號。讓我們測驗一下場景
有問題的表
SQL> desc SRD_OUT.FCT_EMPROLE_TRANSFORM
Name Null? Type
----------------------------------------- -------- ----------------------------
DAT_MONTH DATE
PERSNR VARCHAR2(6 CHAR)
ARBEITS_STATUS VARCHAR2(50 CHAR)
NAME VARCHAR2(50 CHAR)
VORNAME VARCHAR2(50 CHAR)
FTE NUMBER
WOCHENSTUNDEN NUMBER
FUNKTION VARCHAR2(50 CHAR)
OE VARCHAR2(70 CHAR)
DIREKTION VARCHAR2(50 CHAR)
BEREICH VARCHAR2(50 CHAR)
N_NUMMER VARCHAR2(50 CHAR)
FTE_VALUE NUMBER
CENTERKEY VARCHAR2(200 CHAR)
ROLLE VARCHAR2(200 CHAR)
BEMESSUNGSFAKTOR VARCHAR2(50 CHAR)
COD_PROCESS VARCHAR2(30 CHAR)
DAT_EFFECTIVE DATE
SQL> select count(*) from SRD_OUT.FCT_EMPROLE_TRANSFORM ;
COUNT(*)
----------
20436
如果我針對該dba_objects表或其他類似的表/視圖運行該函式，則一切正常。
SQL> COL FILE_NAME FOR A50
SQL> set lines 220
SQL> r
1 SELECT *
2 FROM TABLE(
3 cpl_data_out.fn_generate_parallel_file(
4 CURSOR(
5 SELECT /* PARALLEL(s,10) */
6 "OWNER" ||'~'||
7 "OBJECT_NAME" ||'~'||
8 "SUBOBJECT_NAME" ||'~'||
9 "OBJECT_ID" ||'~'||
10 "DATA_OBJECT_ID" ||'~'||
11 "OBJECT_TYPE" ||'~'||
12 "CREATED" ||'~'||
13 "LAST_DDL_TIME" as csv
14 FROM DBA_OBJECTS s)
15 , 'test_file'
16 , 'DIR_SRD_OUT'
17 , 'csv')
18 ) nt
19*
FILE_NAME NO_RECORDS SEQ_ID
-------------------------------------------------- ---------- ----------
test_file_459.csv 25496 459
test_file_449.csv 25496 449
test_file_453.csv 25496 453
test_file_461.csv 25496 461
test_file_455.csv 25499 455
test_file_451.csv 25496 451
test_file_447.csv 25496 447
test_file_443.csv 25496 443
test_file_457.csv 25496 457
test_file_445.csv 25497 445
10 rows selected.
As you may see, the pipelined function works as expected, it creates 10 csv files that I can join later on using cat. However, if I try to run it against the table shown above, this happens ( for the purpose of the example, I am just using some columns of the table )
Working
SQL> SELECT *
2 FROM TABLE(
3 cpl_data_out.fn_generate_parallel_file(
4 CURSOR(
5 SELECT /* PARALLEL(s,10) */
6 "DAT_MONTH" ||'~'||
7 "PERSNR" ||'~'||
8 "COD_PROCESS" ||'~'||
9 "DAT_EFFECTIVE"
10 as csv
11 FROM SRD_OUT.FCT_EMPROLE_TRANSFORM s)
12 , 'test_file'
13 , 'DIR_SRD_OUT'
14 , 'csv')
15* ) nt
SQL> /
FILE_NAME NO_RECORDS SEQ_ID
-------------------------------------------------- ---------- ----------
test_file_569.csv 456 569
test_file_571.csv 489 571
test_file_575.csv 314 575
test_file_573.csv 483 573
test_file_577.csv 496 577
test_file_581.csv 487 581
test_file_579.csv 430 579
test_file_567.csv 3500 567
test_file_565.csv 3606 565
test_file_563.csv 10175 563
10 rows selected.
Not Working
SQL> SELECT *
2 FROM TABLE(
3 cpl_data_out.fn_generate_parallel_file(
4 CURSOR(
5 SELECT /* PARALLEL(s,10) */
6 "DAT_MONTH" ||'~'||
7 "PERSNR" ||'~'||
8 "COD_PROCESS" ||'~'||
9 "DAT_EFFECTIVE" ||'~'||
10 "ROLLE"
11 as csv
12 FROM SRD_OUT.FCT_EMPROLE_TRANSFORM s)
13 , 'test_file'
14 , 'DIR_SRD_OUT'
15 , 'csv')
16* ) nt
SQL> /
ERROR:
ORA-12801: error signaled in parallel query server P005
ORA-06502: PL/SQL: numeric or value error: character string buffer too small
ORA-06512: at "CPL_DATA_OUT.FN_GENERATE_PARALLEL_FILE", line 34
ORA-06512: at line 1
The only difference between the two queries is the column "ROLLE" which contains ASCII extended characters ( as letters in German such as "?ü??" ). It happens with every column that contains such characters.
Actually the error refers to this line: v_buffer := v_buffer || c_eol || v_rows(i); , but I have no clue what is wrong there when such characters are involved.
SQL> set pages 200
SQL> r
1* select distinct rolle from SRD_OUT.FCT_EMPROLE_TRANSFORM
ROLLE
------------------------
Filialleiter (gro?e Filiale)
Vertriebsdirektor Verm?gensberatung
I don't really understand what relationship exists between those Extended ASCII characters and the function. What should I change in my function to make it work with such characters ?
Thank you all for your help.
uj5u.com熱心網友回復：
當你這樣做時：
IF LENGTH(v_buffer) c_eollen LENGTH(v_rows(i)) <= c_maxline
您正在計算緩沖區和集合變數中的字符數。當您只有單位元組字符時可以，但對于任何多位元組字符，您可能會遇到字符總數小于 32767，但位元組數超過該值的情況。支票通過；但是你這樣做：
v_buffer := v_buffer || c_eol || v_rows(i)
超出緩沖區的大小，并引發錯誤。如果您的緩沖區被宣告為小于最大值并使用字符語意，您可能仍然可以逃脫；但是使用最大尺寸（和任何語意）它將失敗。
如果您計算位元組而不是字符，則不會超過位元組限制：
IF LENGTHB(v_buffer) c_eollen LENGTHB(v_rows(i)) <= c_maxline
轉載請註明出處，本文鏈接：https://www.uj5u.com/gongcheng/411051.html
標籤：
上一篇：Python中的SQLite3查詢以檢索兩個日期之間的行數
下一篇：從列中選擇下一個最大值