plsql - Need help to get a file as a whole in a single mail which is of the size 4 mb in oracle PL/SQL -


the below code in pl/sql procedure checks byte size , if above 30000 bytes iterates data , sends mail attachments(txt file through multiple mails) every 30000 bytes(30 kb).
need get file whole in single mail of size 4 mb in oracle pl/sql.

the data type of lv_message_tab varchar2(32767) . can user clob datatype instead of *varchar2(32767),*if yes please suggest how.

thanks million in advance !!!

for lv_idx in 1..pv_message.count       loop          lv_message := lv_message||pv_message(lv_idx)|| lv_cr;           if length(lv_message) >= 30000                     lv_message_tab.extend;           lv_message_tab(lv_message_tab.last) := lv_message;           lv_message := null;           end if;        end loop; --this send attachment 30000 characters , above       if lv_message_tab.count > 0                    lv_idx in 1..lv_message_tab.count            loop                utl_smtp.helo(lv_conn, lv_smtp_host);               utl_smtp.mail(lv_conn, gc_sender);               utl_smtp.rcpt(lv_conn, lv_to_email_ids);               utl_smtp.rcpt(lv_conn, lv_cc_email_ids);                 utl_smtp.data(lv_conn,               'date: '   || to_char(sysdate, gc_dd_month_yyyy_format) || lv_cr ||               'from: '   || gc_sender || lv_cr ||               'subject: '|| lv_subject || lv_cr ||               'to: '     || lv_to_email_ids || lv_cr ||               'cc: '     || lv_cc_email_ids || lv_cr ||                'mime-version: 1.0'|| lv_cr ||    -- use mime mail standard               'content-type: multipart/mixed;'|| lv_cr ||               ' boundary="-----secbound"'|| lv_cr ||                 lv_cr ||                '-------secbound'|| lv_cr ||               'content-type: text/plain;'|| lv_cr ||               ' name="excel.csv"'|| lv_cr ||               'content-transfer_encoding: 8bit'|| lv_cr ||               'content-disposition: attachment;'|| lv_cr ||               ' filename="'||pv_file_name||'"'|| lv_cr ||               lv_cr ||lv_message_tab(lv_idx)               || lv_cr ||   -- content of attachment               lv_cr ||                 '-------secbound--' );               end loop;        end if;        --this send attachment rest of data...for last few iterations       if lv_message not null                    utl_smtp.helo(lv_conn, lv_smtp_host);           utl_smtp.mail(lv_conn, gc_sender);           utl_smtp.rcpt(lv_conn, lv_to_email_ids);           utl_smtp.rcpt(lv_conn, lv_cc_email_ids);             utl_smtp.data(lv_conn,           'date: '   || to_char(sysdate, gc_dd_month_yyyy_format) || lv_cr ||           'from: '   || gc_sender || lv_cr ||           'subject: '|| lv_subject || lv_cr ||           'to: '     || lv_to_email_ids || lv_cr ||           'cc: '     || lv_cc_email_ids || lv_cr ||            'mime-version: 1.0'|| lv_cr ||    -- use mime mail standard           'content-type: multipart/mixed;'|| lv_cr ||           ' boundary="-----secbound"'|| lv_cr ||             lv_cr ||            '-------secbound'|| lv_cr ||           'content-type: text/plain;'|| lv_cr ||           ' name="excel.csv"'|| lv_cr ||           'content-transfer_encoding: 8bit'|| lv_cr ||           'content-disposition: attachment;'|| lv_cr ||           ' filename="'||pv_file_name||'"'|| lv_cr ||           lv_cr ||lv_message           || lv_cr ||   -- content of attachment           lv_cr ||             '-------secbound--' );           end if;        utl_smtp.quit(lv_conn);       pv_rtn := true;  else      pv_rtn := false;  end if;

i calling procedure giving input collection table like

procedure

sp1_send_letter_attach_wal(pv_job_id in job.job_id%type,                            pv_status in job_transaction_details.status_id%type,                            pv_subject in varchar2,                            pv_message in stringtable,                            pv_file_name in varchar2,                            pv_date_time in date default sysdate,                            pv_rtn out boolean);

i want store data of variable pv_message variable lv_message clob datatype.

lv_message clob;

how store lv_message := pv_message (i.e collection datatype clob type). tried typecast using 'to_clob()' function lv_message = to_clob(pv_message) didn't work.

try use in loop utl_smtp.write_data procedure instead of creating whole mail.

i can't test it, should this:

          utl_smtp.helo(lv_conn, lv_smtp_host);           utl_smtp.mail(lv_conn, gc_sender);           utl_smtp.rcpt(lv_conn, lv_to_email_ids);           utl_smtp.rcpt(lv_conn, lv_cc_email_ids);             utl_smtp.data(lv_conn,           'date: '   || to_char(sysdate, gc_dd_month_yyyy_format) || lv_cr ||           'from: '   || gc_sender || lv_cr ||           'subject: '|| lv_subject || lv_cr ||           'to: '     || lv_to_email_ids || lv_cr ||           'cc: '     || lv_cc_email_ids || lv_cr ||            'mime-version: 1.0'|| lv_cr ||    -- use mime mail standard           'content-type: multipart/mixed;'|| lv_cr ||           ' boundary="-----secbound"'|| lv_cr ||             lv_cr ||            '-------secbound'|| lv_cr ||           'content-type: text/plain;'|| lv_cr ||           ' name="excel.csv"'|| lv_cr ||           'content-transfer_encoding: 8bit'|| lv_cr ||           'content-disposition: attachment;'|| lv_cr ||           ' filename="'||pv_file_name||'"'|| lv_cr ||           lv_cr;   -- importent part (other parts taken question , moved out of loop)       lv_idx in 1..lv_message_tab.count        loop           utl_smtp.write_data(lv_conn, lv_message_tab(lv_idx));        end loop;       utl_smtp.write_data(lv_conn,lv_cr ||   -- content of attachment           lv_cr ||'-------secbound--' );

there used package called demo_mail on otn can't find it...
if find it, it's usefull.


Comments

Post a Comment

Popular posts from this blog

Why does Ruby on Rails generate add a blank line to the end of a file? -

running rails generate controller foo home results in: class foocontroller < application controller def home end end (there's nothing on line in actual file, blank line) is there purpose blank line? it's common use newline indicate end of line, last line. many editors (like vi, use) add in newline silently. consensus text files (especially in unix world) should end in newline, , there have been problems historically if wasn't present. why should text files end newline? the tool used count lines in file "wc" counts newlines in file, without trailing newline show 3 instead of 4. it improves readability of templates used in generator. consider: https://github.com/rails/rails/blob/master/railties/lib/rails/generators/rails/controller/templates/controller.rb to remove trailing newline, template have last line of: end<% end -%> instead of: end <% end -%> that seems less readable me. the discussion below refers ...
Read more

keyboard - Smiles and long press feature in Android -

i've created android keyboard. how add features inserting smilies, , using long keypresses display symbols? to create long-press behavior allows select several options pop-up menu, follow guide (specifically pop-up menu section): http://developer.android.com/guide/topics/ui/menus.html smileys (also known emoticons) combination of keys, understood programs , parsed same way. have do, when clicks smiley face on keyboard, simulate pressing of key combination matching emoticon. example, when clicks smiley, should simulate key presses ':' , ')' :). here's list of emoticons: http://en.wikipedia.org/wiki/list_of_emoticons
Read more

node.js - Bad Request - node js ajax post -

i'm using express 3.0 , node v0.11.0. have button submits form , i'm using ajax post data json object node server. client code is: $('#contact').submit(function(e) { console.log('submit called'); var formdata = json.stringify($('form').serializeobject()); console.log(formdata); $.ajax({ url: "http://localhost:3000/savedata/", type: "post", datatype: 'json', data: {objectdata: formdata}, contenttype: "application/json", complete: function() { console.log('process complete'); }, success: function(data) { console.log('process sucess'); }, error: function() { console.log('process error'); }, }); return false; }); then on server: var express = require('express'); var app = express(); app.set('views', __dirname + '/views'); app.set('view engine', 'jade'); app.use(express.bodyparser());...
Read more