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) );