0

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 is here:

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

This one 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) );
Improve this question
9
  • Is there a varchar2 value that's more than 4000 characters long? Commented Aug 28, 2020 at 13:24
  • No. I've been able to write all of these files before i added the CASE keywords. I'm not sure if that's what's messing it up because I did a lot of editing since it initially worked. The column that gets concatinated into "purification buffer" has 2 varchar2 datatypes that are both 50 characters. The other 3 columns that are concatinated with it are numbers, the biggest one is (6,2). That's the longest value in a column in the entire EXPORT_ALL procedure. Commented Aug 28, 2020 at 21:14
  • The error happens in your function in the CSV package. You have a full stack of all the calls made and the statements you go through: what does the failing line do ? It looks like that line writes something into a local variable that is too small. You can check the length of what you try to write and print out some debugging info Commented Aug 29, 2020 at 6:16
  • 2
    There are all sorts of reasons for a VARCHAR2 variable to be found too small besides hitting the maximum limit. William mentioned the 4000 bytes limit. You can extend that to 32767. Maybe you did that. BUT: that setting is ignored in the CDB - it is only used in the PDBs. Which brings me to one remark I wanted to make: you use a CDB account for your work (C##USER1). That is a big no-no. The CDB is just a container for the PDBs. A CDB is not intended for any application data or work. That belongs to a PDB. Commented Aug 29, 2020 at 6:22
  • Exactly what version of Oracle are you using? And is the workload taking place in the CDB or a PDB? Commented Aug 29, 2020 at 10:44

1 Answer 1

Reset to default
3

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.

Improve this answer
2
  • Yes, that was it! When I made the column alias smaller, it worked! Thank you! May I ask how you figured out that the problem was the size of the column alias from looking at line 38? Commented Aug 29, 2020 at 14:06
  • 1
    The root cause according to your stack trace is in 'ORA-06512: at "SYS.DBMS_SQL", line 2084', which is called by 'ORA-06512: at "C##USER1.CSV", line 38'. I looked at what line 38 was trying to do, which was to generate info on the columns in your cursor. It had nothing to do with the actual data you were retrieving. A quick search for "dbms_sql.describe_columns" in combination with "character string buffer too small" revealed the likely cause. Commented Aug 29, 2020 at 15:10

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.