1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

How can i allow this procedure to attach to email(pdf file in the directory), pdf file that is > 3

Discussion in 'SQL PL/SQL' started by Mzwanele Ngubane, Feb 20, 2019.

  1. Mzwanele Ngubane

    Mzwanele Ngubane Newly Initiated

    Messages:
    1
    Likes Received:
    0
    Trophy Points:
    30
    Location:
    RSA
    how can I allow this procedure to attach to email(pdf file in the directory), pdf file that is > 32000 bytes?


    --SPEC
    PROCEDURE mail_attach_binary
    (recipients VARCHAR2,
    cc VARCHAR2 DEFAULT NULL,
    subject VARCHAR2,
    message VARCHAR2 DEFAULT NULL,
    att_filename VARCHAR2 DEFAULT NULL,
    att_file_loc VARCHAR2);
    END SPP_EMAIL;

    --BODY
    PROCEDURE mail_attach_binary
    (recipients VARCHAR2,
    cc VARCHAR2,
    subject VARCHAR2,
    message VARCHAR2,
    att_filename VARCHAR2,
    att_file_loc VARCHAR2) AS
    --file attachment paramaters
    v_bfile BFILE;
    v_clob CLOB;
    destOffset INTEGER:=1;
    srcOffset INTEGER := 1;
    lang_context INTEGER := DBMS_LOB.default_lang_ctx;
    warning INTEGER;

    -- v_mime_type VARCHAR2(30) := 'application/pdf';

    BEGIN

    setup_smtp_server;
    --Get the file to attach to the e-mail
    v_bfile := BFILENAME (att_file_loc, att_filename);
    DBMS_LOB.OPEN (v_bfile);
    DBMS_LOB.CREATETEMPORARY(v_clob, TRUE, DBMS_LOB.SESSION);
    DBMS_LOB.LOADCLOBFROMFILE(
    dest_lob => v_clob,
    src_bfile => v_bfile,
    amount => DBMS_LOB.GETLENGTH(v_bfile),
    dest_offset => destOffset,
    src_offset => srcOffset,
    bfile_csid => DBMS_LOB.default_csid,
    lang_context => lang_context,
    warning => warning);
    DBMS_LOB.CLOSE(v_bfile);

    EXCEPTION WHEN
    INVALID_ARGUMENT THEN
    alert('EMAIL',1000,'Invalid argument passed to e-mail attachment from utl_mail.send_attach_varchar2');
    END mail_attach_binary;
     
  2. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,664
    Likes Received:
    375
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    Provided the error message reported when this is attempted.