Home » SQL & PL/SQL » SQL & PL/SQL » Create table with email body (Oracle 10g)
Create table with email body [message #382223] Wed, 21 January 2009 07:08 Go to next message
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 #382227 is a reply to message #382223] Wed, 21 January 2009 07:09 Go to previous messageGo to next message
Michel Cadot
Messages: 68733
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Post a Test case: create table and insert statements along with the result you want with these data.

Please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter), use code tags and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version (4 decimals).

Regards
Michel
Re: Create table with email body [message #382231 is a reply to message #382223] Wed, 21 January 2009 07:26 Go to previous messageGo to next message
Jim_Fisher
Messages: 15
Registered: January 2009
Location: CANADA
Junior Member



Perhaps a better approach is to ignore the post, or move it to a thread called ‘Will answer when phrased properly’.
Re: Create table with email body [message #382234 is a reply to message #382223] Wed, 21 January 2009 07:42 Go to previous messageGo to next message
joy_division
Messages: 4963
Registered: February 2005
Location: East Coast USA
Senior Member
I would make an Oracle Report and do a lookup to some master table for all of the variables. QED.
Re: Create table with email body [message #382293 is a reply to message #382234] Wed, 21 January 2009 16:18 Go to previous message
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.
Previous Topic: Select from 3 tables
Next Topic: How to select the MAX(PK_ID) value in a procedure
Goto Forum:
  


Current Time: Sun Feb 09 09:58:09 CST 2025