ORA-06502: 寫入 csv 時出現“字元串緩衝區太小”

我有一些要導出的表。我正在使用這個包（我稱之為“CSV”）：https ://www.williamrobertson.net/documents/refcursor-to-csv.shtml

在添加 CASE 語句之前，我能夠寫入 CSV 文件。我真的希望有辦法解決它，因為我的 CASE 語句非常有用。

錯誤是：

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

ORA-06512: at "SYS.DBMS_SQL", line 2084

ORA-06512: at "C##USER1.CSV", line 38

ORA-06512: at "C##USER1.CSV", line 162

ORA-06512: at line 1

ORA-06512: at "C##USER1.CSV", line 278

ORA-06512: at "C##USER1.CSV", line 278

ORA-06512: at "C##USER1.EXPORT_02_PRODUCED_PURIFIED", line 5

ORA-06512: at "C##USER1.EXPORT_ALL_LYS_FOR_TABLEAU", line 5

這是錯誤指向的地方：

ORA-06512: at "C##USER1.EXPORT_02_PRODUCED_PURIFIED", line 5 在這兒：

CREATE OR REPLACE VIEW vw_export_02_produced_purified AS SELECT 
   pk_purified_enz_id,
   fk_construct_id as "fk_for_construct",
   fk_expr_system as "expression_system",
   CASE pr.valid 
       WHEN 1 THEN 'Yes'
       WHEN 0 THEN 'No'
   END as "Is this growth ok?",
   g_batch,
   p_batch,
   pur.concentration as "Concentration (uM)",
   pur.abts5_pur_checkpoint as "purification checkpoint",
   pur.yield as "Yield (mg/L)",
       CASE 
           WHEN pk_purified_enz_id IS NULL THEN ' ' 
           WHEN pk_purified_enz_id  IS NOT NULL THEN 
           buffer_mol || ' mM, ' || 
           fk_final_buffer_system || ' ' || 
           'pH ' || final_ph || ', ' ||
           final_nacl || ' mM NaCl' ||
           CASE 
               WHEN final_add IS NULL THEN ' ' 
               WHEN final_add IS NOT NULL THEN ', ' || final_add
           END
       END AS "Purification Buffer",
   buffer_mol,
   fk_final_buffer_system as "buffer system",
   final_ph,
   final_nacl,
   final_add as "other buffer additives" 
   
FROM produced pr FULL OUTER JOIN purified_enz pur
on pr.pk_produced_id = pur.fk_produced_id;


CREATE OR REPLACE PROCEDURE EXPORT_02_PRODUCED_PURIFIED AS 
   l_dataset sys_refcursor;
begin
   open l_dataset for select * from vw_export_02_produced_purified;
   csv.write_file
   ( p_dataset => l_dataset
   , p_heading => 'Y'
   , p_directory => 'EXTRACT_DIR'
   , p_filename => '02_produced_purified.txt' );
END EXPORT_02_PRODUCED_PURIFIED;
/

這個ORA-06512: at "C##USER1.EXPORT_ALL_LYS_FOR_TABLEAU", line 5來自主程序，它結合了所有較小的程序。

CREATE OR REPLACE PROCEDURE EXPORT_ALL_LYS_FOR_TABLEAU AS
BEGIN
EXPORT_00_CPP;
EXPORT_01_CONSTRUCT;
EXPORT_02_PRODUCED_PURIFIED;
EXPORT_03_CHAR;
EXPORT_04_CELL_DATA;
END EXPORT_ALL_LYS_FOR_TABLEAU;
/

被引用的表的 DDL：

CREATE TABLE PRODUCED (
 pk_produced_id NUMBER GENERATED ALWAYS AS IDENTITY,
 fk_construct_id NUMBER NOT NULL,
 g_batch VARCHAR2 (50) NOT NULL, 
 full_g_batch VARCHAR2 (200) GENERATED ALWAYS AS (g_batch || '-' || fk_construct_id) VIRTUAL,
 valid NUMBER (1) DEFAULT 1,
 fk_expr_system VARCHAR2 (50),

 CONSTRAINT pk_produced_id PRIMARY KEY (pk_produced_id),
 CONSTRAINT fk_p_construct_id FOREIGN KEY (fk_construct_id) REFERENCES construct (pk_construct_id),
 CONSTRAINT fk_expr_system FOREIGN KEY (fk_expr_system) REFERENCES EXPR_SYSTEM(pk_expr_system),
 CONSTRAINT uq_produced UNIQUE (fk_construct_id, g_batch) 
);

CREATE TABLE PURIFIED_ENZ (
 pk_purified_enz_id NUMBER GENERATED ALWAYS AS IDENTITY,
 fk_produced_id NUMBER, 
 p_batch VARCHAR2 (50) NOT NULL,
 pur_profile BLOB,
 fk_final_buffer_system VARCHAR2 (50),
 buffer_mol NUMBER (6, 2),
 final_nacl NUMBER (4),
 final_ph NUMBER (4, 2),
 final_add VARCHAR2 (50),
 yield NUMBER (6, 2),
 concentration NUMBER (6, 2),
 abts5_pur_checkpoint NUMBER (5, 2),
 
 CONSTRAINT pk_purified_enz_id PRIMARY KEY (pk_purified_enz_id),
 CONSTRAINT fk_produced_id FOREIGN KEY (fk_produced_id) REFERENCES produced (pk_produced_id),
 CONSTRAINT fk_final_buffer_system FOREIGN KEY (fk_final_buffer_system) REFERENCES buffer_system (pk_final_buffer_system),
 CONSTRAINT uq_purified_enz UNIQUE (fk_produced_id, p_batch)
 );
 

根據您的堆棧跟踪，根本原因在 DBMS_SQL 中，由 CSV 包的第 38 行呼叫。第 38 行如下：

dbms_sql.describe_columns(p_cursor_id, l_col_count, p_cursor_columns);

這根本與您的數據無關，而是視圖本身的程式碼。似乎正在發生的事情是您的視圖中的列定義或別名太大而無法由過程呼叫處理。

這很可能與 varchar 或 pl/sql 構造的 4000 或 32K 字元的總體限制無關，而是與 describe_columns 過程中的特定可變大小約束有關。它也可能與您在容器數據庫而不是可插入數據庫中執行此程式碼有關。

使用您的表並查看 DDL 我無法在 PDB 中複製錯誤，因此我建議您先將您的工作負載移到那裡，看看是否能解決您的問題。如果您仍然有問題，我會向 Oracle Support 開一張票，因為這也可能表明存在錯誤。

引用自：https://dba.stackexchange.com/questions/274535