Create table with email body [message #382223] |
Wed, 21 January 2009 07:08  |
athene
Messages: 11 Registered: January 2009
|
Junior Member |
|
|
Hi,
I need to create a table which contains the email body.The email body should be editable .But some parts of the email body is retrieved from DB,which is non editable.
For eg:
----------------
The email body
---------------
This is a notice to you as MANAGER to disburse the LN_1234
The details of the loan are as follows
LOAN NO : LN_1234
DUE DATE : 21/12/2008
LOANEE : abcde
Please do not reply.This is a system generated mail.
-----------------------------------------------
The details in the bold should come from other tables in DB
Can u pls help me on this.
Thanks in advance
|
|
|
|
|
|
Re: Create table with email body [message #382293 is a reply to message #382234] |
Wed, 21 January 2009 16:18  |
 |
Littlefoot
Messages: 21823 Registered: June 2005 Location: Croatia, Europe
|
Senior Member Account Moderator |
|
|
Here's how I understood it: variable information is stored in some table and selected per request:
SQL> CREATE TABLE non_editable
2 (id NUMBER,
3 job VARCHAR2(20),
4 disburse_wtf VARCHAR2(20),
5 due_date DATE
6 );
Table created.
SQL> INSERT INTO non_editable
2 (id, job, disburse_wtf, due_date)
3 VALUES
4 (431, 'MANAGER', 'LN_1234', SYSDATE);
1 row created.
SQL>
Another table which contains e-mail body for this ID:SQL> CREATE TABLE e_mail_body
2 (id NUMBER,
3 body VARCHAR2(4000)
4 );
Table created.
SQL> INSERT INTO e_mail_body
2 (id, body)
3 (SELECT n.id,
4 'This is a notice to you as a ' || n.job
5 ||' to disburse the ' || n.disburse_wtf
6 ||CHR(10)
7 ||'The details of the loan are as follows'
8 ||CHR(10)
9 ||'Loan no ' || n.disburse_wtf
10 ||CHR(10)
11 ||'Due date ' || n.due_date
12 FROM non_editable n
13 WHERE n.id = 431
14 );
1 row created.
SQL>
Now, once the body is created:SQL> select body from e_mail_body;
BODY
--------------------------------------------------------------
This is a notice to you as a MANAGER to disburse the LN_1234
The details of the loan are as follows
Loan no LN_1234
Due date 21.01.2009 23:08
SQL> it can be edited; Forms might be a choice as the "body" column can be used as an item in a form block, edited by an end-user (i.e. someone who isn't going to use SQL*Plus and UPDATE this in order to get that). That includes an INSERT statement which I have used to create an e-mail body - put it into a form (push button, perhaps?), easily create and edit text.
Reports might be a good choice in order to get the output, but - you can't use it to edit a message.
|
|
|