Home » SQL & PL/SQL » SQL & PL/SQL » Complex Report using SQL*PLUS (Orcale 10g)
Complex Report using SQL*PLUS [message #333727] Mon, 14 July 2008 04:58 Go to next message
Messages: 266
Registered: April 2008
Location: India
Senior Member
I have to create a Report in SQL*PlUS. The report is a sort of Mail Merge. I saw the example in the link
The report I require is a full fledged letter with many field values to be embedded from Database while running Mail Merge. It will have multiple letters generated and spooled as per the date selection from a form.

The letter goes something like this :

Mr. <NAME>
<Address line 1>
<Address line 2>
<Address line 3>

Dear Mr. <Firstname>
   We thank you for your Application NO. <Application_number> dated <DATE> for <PRODUCT> and giving us an oppurtunity to server you.
BAsed on facts provided in Application form, we request the life to be assured to complete the medical requirement listed below:

1. <Medical requirement>

The above mentioned test(s) will have to be conducted either of the following Center(S)

<Doctor1 Address 1>     <Doctor2 Address 1>
<Doctor1 Address 2>     <Doctor2 Address 2>
<Doctor Address 3>      <Doctor2 Address 2>
<Doctor Address 4>      <Doctor2 Address 4>

Kindly contact your advisor/consultant within 30 days of receipt of this letter to complete the requirements.

Looking forward to hearing from you soon,

Warm Regards

Cc To Advisor <Advisor NUmber>

<Advisor address 1>
<Advisor address 2>
<Advisor address 3>

I am new to Report creation using SQL *PLUS.

Please guide me how to create such a complex report.

Re: Complex Report using SQL*PLUS [message #333756 is a reply to message #333727] Mon, 14 July 2008 06:45 Go to previous messageGo to next message
Messages: 7880
Registered: March 2000
Senior Member
Select 'Dear Mr ' || last_name || ',
I want to inform you that ' ||... etc
from <your_table>

is the easy way.

There is no template/fields/merge functionality in sqlplus

[Updated on: Mon, 14 July 2008 06:46]

Report message to a moderator

Re: Complex Report using SQL*PLUS [message #333793 is a reply to message #333756] Mon, 14 July 2008 08:00 Go to previous message
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
I'd use a tool other than SQL*Plus.

- MS Word will do mail merges from files.
- Oracle Reports will do this easily when you know how to fly it, but there's a fairly steep learning curve.
- You could write a piece of code to build the whole letter up a bit at a time, and then write the finished text to a table, and just select the output from there.
Previous Topic: Concentrating multiple rows?
Next Topic: Inserting a directory of images into a table of blobs
Goto Forum:

Current Time: Fri Oct 28 18:52:38 CDT 2016

Total time taken to generate the page: 0.27411 seconds