Search code examples
htmloracle-databaseplsqlconcatenationclob

Issue in concatenation - Oracle Email using html and SYS_REFCURSOR

I am new to oracle and trying to send mail using UTL_MAIL.SEND. I am using HTML in between to create table and all. Using a CLOB variable to store the HTML and SYS_REFCURSOR to store the query result. Inside the query trying to fetch data from SYS_REFCURSOR using loop. All the structure is coming fine, but values inside HTML table i.e. fetching from SYS_REFCURSOR two hash(#) is appended to last value. My query is as follows.

DECLARE postComments SYS_REFCURSOR;
v_Message CLOB;

liscname DAN_DANAPP_TRADE_LIC.OTL_LIC_NAME%TYPE;

lisctype DAN_DANAPP_TRADE_LIC.OTL_LIC_TYPE%TYPE;

authority DAN_DANAPP_TRADE_LIC.OTL_LIC_AUTH%TYPE;

lastdate DAN_DANAPP_TRADE_LIC.OTL_EXPIRY_DT%TYPE;

daysrem number;

BEGIN OPEN postComments
FOR
SELECT otl.OTL_LIC_NAME,
       otl.OTL_LIC_TYPE,
       otl.OTL_LIC_AUTH,
       to_char(to_date(otl.OTL_EXPIRY_DT,'DD/MM/YYYY')) AS expirydate,
       to_date(otl.OTL_EXPIRY_DT,'dd/mm/yyyy')-to_date(sysdate,'dd/mm/yyyy') AS daystoexpire
FROM DAN_DANAPP_TRADE_LIC otl
WHERE OTL_EFF_TO_DT IS NULL
  AND (to_date(otl.OTL_EXPIRY_DT,'dd/mm/yyyy')-to_date(sysdate,'dd/mm/yyyy'))<=45
  AND OTL_CLO_STATUS=0
ORDER BY (to_date(otl.OTL_EXPIRY_DT,'dd/mm/yyyy')-to_date(sysdate,'dd/mm/yyyy')) DESC;

v_Message := q'#<html>
<body>Dear Sir/Madam,<br><br>
The following trade license(s) will expire soon. Please followup accordingly.
<br><br>
<table border="1"  width="90%">
<tr>
<th>
Company
</th>
<th>
License Type
</th>
<th>
Licensing Authority
</th>
<th>
Expiry Date
</th>
<th>
Days Left
</th>
</tr>#';

LOOP FETCH postComments INTO liscname,
                             lisctype,
                             authority,
                             lastdate,
                             daysrem;

EXIT WHEN postComments%NOTFOUND; --  dbms_output.put_line(liscname);

dbms_lob.append(v_Message, q'#<tr><td>#'|| liscname || q'#</td> 
<td>#'|| lisctype || q'#</td>
<td>#'|| authority || q'#</td>
<td>#'|| lastdate || q'#</td>
<td>#'|| daysrem || '#</td></tr>#');

END LOOP;

CLOSE postComments;

dbms_lob.append(v_Message, q'#</table>
<br><br>
<font size="2">Sent from <font color="red">DanApps</font></font>
</body>
</html>#');

UTL_MAIL.SEND(sender=>'c@xyz.com', recipients=>'a.b@xyz.com', subject=>'Trade license expiry', message => v_Message, priority => 1, mime_type => 'text/html; charset=us-ascii'); --send out emails in HTML format.

END;

Output

enter image description here

As you said If I put q' then format changes as follows(Sorry I couldn't provide data.)

Please help me to sort the issue. Thanks in advance.

Solution

  • The problem is that you are using q' as quote delimiter and are not completing it with the closing # in this line .So the variables end up interpreted as strings.

    q'#<tr><td>'|| liscname || '</td>   -- Wrong

q'#<tr><td>#'|| liscname || '</td>  -- Right

    And you are terminating # in this line which is also not correct

     <td>'|| daysrem || '</td></tr>#');

    In your case , since you don't have single quotes within tags, q' notation would not be required.