All we need is an easy explanation of the problem, so here it is.
I have some tables that i want to export. I’m using this package (I called it "CSV"): https://www.williamrobertson.net/documents/refcursor-to-csv.shtml
I was able to write to CSV files before i added CASE statements. I really hope there’s a way to fix it because my CASE statements are super useful.
The error is:
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
Here’s where the errors point to:
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 is from the master procedure that combines all the smaller procedures.
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 for tables that are referenced:
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) );
How to solve :
The root cause – according to your stack trace – is in DBMS_SQL, which is called by line line 38 of your CSV package. Line 38 is the following:
dbms_sql.describe_columns(p_cursor_id, l_col_count, p_cursor_columns);
This isn’t about your data at all, but rather the code of the view itself. What appears to be happening is that a column definition or alias in your view is too large to be processed by the procedure call.
This most likely has nothing to do with the overall limit of 4000 or 32K characters on varchar or pl/sql constructs, but rather a specific variable size constraint within the describe_columns procedure. It may also be related to your execution of this code within a container database instead of a pluggable database.
Using your table and view DDL I was unable to duplicate the error in a PDB, so i would advise moving your workload there first to see if that resolves your issue. If you continue to have problems, I would open a ticket with Oracle Support as this could also potentially indicate a bug.
Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂