CODEWITHSUNDEEP
oracle-databasecsvplsql
Thejus32
Thejus32

Reputation: 371

Generating CSV file from Oracle DB

I am trying to generate CSV file from my oracle DB and send it as an attachment via mail . I have an issue with the CSV file generated. I could see that certain values from the table is not read properly. Like the value in description column is written in CSV file in two different cells instead of a single cell .

enter image description here

you can see that a value is written in two cells instead of a single cell as rest of the values. Here 'Testing pls ignore mails' should be treated as a single value and should be in the same cell .

I noticed one more thing that the value is in the table as follows:

enter image description here

could this be because my code is unable to read the value in multiple lines ?

Here is my code for creating the CSV file and the field FC_ED_DESC is causing the problems and I am including " l_clob " in the attachment section of the code that I have written for mailing .

   DECLARE
        l_clob CLOB;
        l_attach_text VARCHAR2 (32767);
        l_attach_text_h VARCHAR2 (32767);
        FC_SV_STATUS_DESC VARCHAR2(200) := 'open';
    
        -- select query from table to get the values needed
        CURSOR c1 IS 
           select FC_ED_RECORD_ID,to_char(FC_ED_UPLOADTIME,'DD.MM.YY')FC_ED_UPLOADTIME,FC_ED_USER_ID ,FC_ED_BROKER,FC_ED_ACT_NUM,FC_ED_POLICY_NUM,FC_ED_AMOUNT,FC_ED_TRANS_TYPE,FC_ED_CURRENCY,to_char(FC_ED_DUE_DATE,'DD.MM.YY')FC_ED_DUE_DATE,FC_ED_SENDER_NAME,FC_ED_DESC,to_char(FC_ED_CREDIT_DATE,'DD.MM.YY')FC_ED_CREDIT_DATE from  MYTABLE where FC_ED_EXPCASH_STATUS = 1 ;   
        
    BEGIN   
        -- csv file columns are these
        l_attach_text_h :=
        'FC_ED_RECORD_ID ,FC_ED_UPLOADTIME ,FC_ED_USER_ID ,FC_ED_BROKER ,FC_ED_ACT_NUM ,FC_ED_POLICY_NUM ,FC_ED_AMOUNT ,FC_SV_STATUS_DESC ,FC_ED_TRANS_TYPE ,FC_ED_CURRENCY ,FC_ED_DUE_DATE ,FC_ED_SENDER_NAME ,FC_ED_DESC ,FC_ED_CREDIT_DATE';   
        
        FOR employee_rec in c1    
        LOOP
            DBMS_OUTPUT.put_line('Before loop COUNT Boss ...'||c1%ROWCOUNT);
            -- each value is read using loop   
            l_attach_text :=                                  
            employee_rec.FC_ED_RECORD_ID ||','||
            employee_rec.FC_ED_UPLOADTIME ||','||
            employee_rec.FC_ED_USER_ID ||','||
            employee_rec.FC_ED_BROKER ||','||
            employee_rec.FC_ED_ACT_NUM ||','||
            employee_rec.FC_ED_POLICY_NUM ||','||
            employee_rec.FC_ED_AMOUNT ||','||
            FC_SV_STATUS_DESC ||','||
            employee_rec.FC_ED_TRANS_TYPE ||','||
            employee_rec.FC_ED_CURRENCY ||','||
            employee_rec.FC_ED_DUE_DATE ||','||
            employee_rec.FC_ED_SENDER_NAME ||','||
            employee_rec.FC_ED_DESC ||','||
            employee_rec.FC_ED_CREDIT_DATE ||chr(13);
   
            l_clob := l_clob || l_attach_text;    
        END LOOP;

        -- adding values
        l_clob := l_attach_text_h ||chr(13)|| l_clob;

Can anyone please help me with this ?

Upvotes: 0

Views: 2581

Answers (1)

EJ Egyed
EJ Egyed

Reputation: 6064

The problem is that you are not surrounding your fields with double quotes. Anything that contains a carriage return will be read as a new record in your CSV file.

Since you are using SQL Developer, the easiest way to generate a CSV file is to use the /*csv*/ hint in your query. This will properly quote all the fields.

Put this code into SQL Developer and execute it using F5 to run it in Script mode and you should get a properly quoted CSV output in DBMS_OUTPUT.

SELECT /*csv*/
       FC_ED_RECORD_ID,
       TO_CHAR (FC_ED_UPLOADTIME, 'DD.MM.YY')      FC_ED_UPLOADTIME,
       FC_ED_USER_ID,
       FC_ED_BROKER,
       FC_ED_ACT_NUM,
       FC_ED_POLICY_NUM,
       FC_ED_AMOUNT,
       FC_ED_TRANS_TYPE,
       FC_ED_CURRENCY,
       TO_CHAR (FC_ED_DUE_DATE, 'DD.MM.YY')        FC_ED_DUE_DATE,
       FC_ED_SENDER_NAME,
       FC_ED_DESC,
       TO_CHAR (FC_ED_CREDIT_DATE, 'DD.MM.YY')     FC_ED_CREDIT_DATE
  FROM MYTABLE
 WHERE FC_ED_EXPCASH_STATUS = 1;

Another option is to just run the query you want in SQL Developer, then highlight the results you want to export (or press Control + A), then right click and select Export and choose your Format to be CSV

Click Export

Update

To fix your existing code, put double quotes around each of the fields you are combining into your l_attach_text variable. See example below.

    l_attach_text :=       '"' ||                           
            employee_rec.FC_ED_RECORD_ID ||'","'||
            employee_rec.FC_ED_UPLOADTIME ||'","'||
            employee_rec.FC_ED_USER_ID ||'","'||
            employee_rec.FC_ED_BROKER ||'","'||
--          ...(the rest of your fields here)
            employee_rec.FC_ED_DESC ||'","'||
            employee_rec.FC_ED_CREDIT_DATE || '"' || chr(13);

Upvotes: 3

Related Questions