CODEWITHSUNDEEP
oracle-databasecsvprocedure
Thejus32
Thejus32

Reputation: 371

CSV File generation in Oracle

I am new to oracle and here I am trying generate a CSV file from the values from my table and mail it. I am able to generate the csv file but I am unable to add a heading to my file and my date values are being shown as ########. I want the CSV file to be generated as the expected one .

The expected CSV :

enter image description here

The generated CSV :

enter image description here

Here is my code :

l_attach_text_h :=
'RECORD_ID ,INPUTTED DATE ,INPUTTED BY ,BROKER .....

FOR employee_rec in c1

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       || '","' ||

........

l_clob := l_clob||chr(10)||l_attach_text;
END LOOP;

l_clob := l_attach_text_h ||chr(13)|| l_clob;

DBMS_OUTPUT.put_line(' Dtls processing  completed...');


v_Mail_Conn := utl_smtp.Open_Connection(v_Mail_Host, 25);

utl_smtp.Helo(v_Mail_Conn, v_Mail_Host);

utl_smtp.Mail(v_Mail_Conn, v_From);

utl_smtp.Rcpt(v_Mail_Conn, v_Recipient);

utl_smtp.Data(v_Mail_Conn,
'Date: ' || to_char(sysdate, 'Dy, DD Mon YYYY hh24:mi:ss') || crlf ||
'From: ' || v_From || crlf ||
'Subject: '|| v_Subject || crlf ||
'To: ' || v_Recipient || crlf ||

'MIME-Version: 1.0'|| crlf || -- Use MIME mail standard
'Content-Type: multipart/mixed;'|| crlf ||
' boundary="-----SECBOUND"'|| crlf ||
crlf ||

'-------SECBOUND'|| crlf ||
'Content-Type: text/plain;'|| crlf ||
'Content-Transfer_Encoding: 7bit'|| crlf ||
crlf ||
'Please find the following in the attachments :'|| crlf || -- Message body
'Entry details & Entry details'|| crlf ||
crlf ||

'-------SECBOUND'|| crlf ||
'Content-Type: text/plain;'|| crlf ||
' name="Files.csv"'|| crlf ||
'Content-Transfer_Encoding: 8bit'|| crlf ||
'Content-Disposition: attachment;'|| crlf ||
' filename="Files.csv"'|| crlf ||
crlf ||
 l_clob  || crlf || -- Content of attachment
crlf ||


'-------SECBOUND--' -- End MIME mail
);

utl_smtp.Quit(v_mail_conn);

DBMS_OUTPUT.put_line('mail send  completed...');

can anyone help me with this?

Upvotes: 0

Views: 71

Answers (1)

Littlefoot
Littlefoot

Reputation: 143083

As of the heading: create a new comma-separated line which contains all headings, e.g.

SQL> select '"Record ID"' ||','|| '"Inputted Date"' ||','|| '"Inputted By"' as heading from dual;

HEADING
-----------------------------------------
"Record ID","Inputted Date","Inputted By"

SQL>

and include it as the 1st line in the output file.

As of the ###### issue: are you sure it is a problem? What happens when you double-click in between columns B and C in Excel? That operation should adjust column B width so that it matches data width (so I'd expect actual data to be seen afterwards).

Upvotes: 1

Related Questions