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

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?
    – pmdba
    Commented Aug 29, 2020 at 10:44

1 Answer 1

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.

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.
    – pmdba
    Commented Aug 29, 2020 at 15:10

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.