Forum Stats

  • 3,853,509 Users
  • 2,264,229 Discussions


Form fill for Email > 4000 characters

Mr Neil
Mr Neil Member Posts: 113 Blue Ribbon
edited Dec 23, 2015 5:10AM in APEX Discussions

Hi all.

I'm after general information on storing/ retrieving a CLOB, then manupulate the data before some sort of output.

The reason I'm asking: I've got some emails set up where HTML text is stored (in the process) in VARCHAR2 variables. I pull page item data and fill some markers in the HTML and send the email. One of these emails has a large HTML source that I store in 3 x Varchar2(4000) variables and I concatenate these into one source to send. I know there is a limit quickly approaching with this method.

Is it feasible to be storing HTLM info (or MS Dopcument, or PDF document) in a CLOB or BLOB, retrieving the "template", filling the markers and then displaying, or emailing , or attaching om an email?

Any direction or info on this is appreciated.




  • Scott Wesley
    Scott Wesley Member Posts: 6,260 Gold Crown
    edited Nov 24, 2015 1:21AM

    The apex_mail.send procedure accepts VARCHAR2 or CLOB


      p_to IN VARCHAR2,

      p_from IN VARCHAR2,

      p_body IN [ VARCHAR2 | CLOB ],

    Are you concerned about building one item on the page that might have clob content, or just that a concatenation of values might?

  • Mr Neil
    Mr Neil Member Posts: 113 Blue Ribbon
    edited Dec 20, 2015 8:04PM

    Hey there.

    Apoogies for delay, I got a rush of work.

    My concern was to be able to get the BLOB content (Greater than 4000 chars) into the page to manipulate the content before sending.

    I should ask more broadly: Is it possible to upload a large text file (say 40,000 characters long), and manipulate the data (say, search and replace some words)  and then have this emailed either as an attchment or as part of the Email body?



  • Emu
    Emu Member Posts: 270 Bronze Badge
    edited Dec 20, 2015 11:56PM

    yes text functions to search and replace that work on varchar will also work on clob.  As noted above apex_mail.send allows sending cobs.  You should be ok...give it a try.

  • Hari_639
    Hari_639 Member Posts: 1,484 Silver Trophy
    edited Dec 21, 2015 1:37AM


    I think this is what you need, check

    Till 32K, there is no issue. If you are dealing with more than 32K, then you can use above approach.



  • Mr Neil
    Mr Neil Member Posts: 113 Blue Ribbon
    edited Dec 21, 2015 11:42PM

    Thanks for your input guys. I have been reading and experimenting. I've got a few test functions up and running.

    I can retrieve a saved file from a table (html file larger than 32k in this particular case), modify the content and then send with the content as the HTML body. This involved converting the BLOB type from the table to a CLOB type page variable because (I think) the mail send will only send a CLOB). Successful outcome!

    I can also retrieve a saved RTF file from a table and send as an email attachment. This was straight out of the book.

    But for the main goal, at this stage, I have...

    - Retrieve the RTF from the table into a page BLOB variable.

    - Convert from a BLOB to CLOB so I can

    - Make changes to the text as necessary

    - Convert back to BLOB so I can

    - Use APEX_MAIL.ADD_ATTACHMENT to send it out.

    Successful test, but I don't know how robust it is.

    Is this what you guys would do?



  • Hari_639
    Hari_639 Member Posts: 1,484 Silver Trophy
    edited Dec 23, 2015 5:10AM

    It depends. If there is way to avoid "pulling these RTF documents from DB (in chunks) and showing it to users" then I would go for it.

    For e.g. if RTF template is fixed and I know there are fixed number of places where user has to enter data, then I can show these details as fixed fields to users. Users can send data via these form items, and in backend, I can replace #PLACE_HOLDERS# with user entered data.



This discussion has been closed.