SQLWord User's Guide and Reference

SQLWord

User’s Guide and Reference

Release 10.0

July 2009

Contents

General information.................................................................................................................................. 3

Introduction....................................................................................................................................... 3

Software requirements..................................................................................................................... 5

Installation......................................................................................................................................... 6

License key........................................................................................................................................ 8

Configuring the Oracle HTTP server............................................................................................. 9

SQLWord-Developer............................................................................................................................. 12

Introduction..................................................................................................................................... 12

Scriptlets.......................................................................................................................................... 15

Examples........................................................................................................................................... 18

Steps to create a source document.............................................................................................. 29

Options............................................................................................................................................. 33

SQLWord Run......................................................................................................................................... 36

Introduction..................................................................................................................................... 36

Command line syntax..................................................................................................................... 38

Integration with Web-enabled Oracle*Forms.................................................................................... 40

Example............................................................................................................................................. 40

Migration from SQLWord 2.1............................................................................................................... 42

How to do it..................................................................................................................................... 42

Frequently asked questions.................................................................................................................. 43

How can I remove unwanted empty new lines?......................................................................... 43

How can I skip a row in a Word table?........................................................................................ 44

How can I display carriage-returns in the output document?................................................. 44

How can I display images in the output document?................................................................. 44

How can I suppress input-parameters in the web interface?................................................... 45

How can I change the presentation of decimal values in the output document?................. 45

How can I create new pages in the output document?............................................................. 46

How can I send an output document by e-mail from my Oracle database?........................... 46

How can I write an output document on the Oracle database server using UTL_FILE?.... 48

How can I save the output document into an Oracle table?.................................................... 49

How can I create dynamic hyperlinks?........................................................................................ 49

Hints & Tips............................................................................................................................................ 50

Compile several documents.......................................................................................................... 50

Clearing formatting code............................................................................................................... 51

Clearing all scriptlets...................................................................................................................... 51

About....................................................................................................................................................... 52

Company information..................................................................................................................... 52

All Oracle database users know that it is quite difficult to retrieve Oracle data into Microsoft Word documents. Sequel Solutions has developed a report-generator which gives you a powerful tool to solve this problem.

You can define your source documents in Microsoft Word and use SQLWord to generate output documents merged with the data from your Oracle database.

The existing reporting tools mostly use their own specific format and don’t integrate at all with Microsoft Word documents. Oracle Reports is a powerful, but complex tool and it has a long learning curve for developers. Reports created by Oracle Reports can't be used in Microsoft Word for further modifications. Mailmerge in Microsoft Word has very limited possibilities and is difficult to use. It is not possible to create master-detail documents.

When using SQLWord you can create your own standard letters, contracts and reports, integrating with the data of your Oracle database.

Using the SQLWord Developer application, you can place several SQL-statements enclosed by <% tag %> scriptlets inside the text of a Microsoft Word document. SQLWord follows the syntax of Oracle PL/SQL Server Pages <% tag %> declarations.

Microsoft Word documents with <% tag %> scriptlets can be stored by the SQLWord Developer application into your Oracle database, compiled as PL/SQL-procedures. By calling the PL/SQL-procedure that you created from your Microsoft Word document, SQLWord retrieves the data from your Oracle database and integrates it in the generated output document.

If you run SQLWord “web-enabled” through the Oracle HTTP server, the output document is send to the webbrowser on the client, where the received output document is opened with Microsoft Word (Windows) or OpenOffice (Linux).

If you run SQLWord “client-server” by using the SQLWord Run application, the output document is created on the LAN and is opened with Microsoft Word.

SQLWord can be implemented in several "web-enabled" applications, such as Oracle Forms9i or higher. An implementation example is included.

SQLWord architecture

Software requirements

Server side

· Oracle 9i / Oracle 10g / Oracle 11g

· Oracle HTTP Server (optional)

Clients

· Windows XP / Windows Vista / Windows 2000 / Windows NT.

· Microsoft Word 2002 / Microsoft Word 2007.

· SQLWord-Developer and SQLWord-Run require Oracle*Net client.

· Webbrowser (any).

Installation

Setup

From the file manager double-click on the file named install_sqlword10.exe to start the setup program. Please follow the on-screen installation directions.

All necessary files will be installed by default in a folder at C:\Program Files\SQLWord10

Create SQLWord-tables and the SQLWordi-package

Before installing the SQLWordi tables and package we suggest to create a new user SQLWORD_DEMO for evaluation purpose with SQL*Plus:

SQL> connect to a user with DBA-rights …

SQL>

SQL> create user SQLWORD_DEMO identified by SQLWORD_DEMO

default tablespace USERS;

SQL> grant connect, resource to SQLWORD_DEMO;

SQL>

Create a shortcut on your desktop for SQL*Plus and specify the default-directory “Start in”:

“C:\Program Files\SQLWord\SQL”

Ø If your Oracle database is XE the first you must grant several sys-privileges to the SQLWORD_DEMO user. Start SQL*Plus from this shortcut and run script sys_grants.sql (as sysdba).

SQL> @sys_grants

Grant succeeded.

Ø Now start SQL*Plus from this shortcut, connect to user SQLWORD_DEMO and run the installation script install_sqlword10.sql

SQL> @install

Table created.

Index created.

Table altered.

Table created.

Index created.

Table altered.

Table created.

Index created.

Table altered.

Table created.

Index created.

Table altered.

Package created.

Package-body created.

SQL>

License key

To install the SQLWord license key, you need to run the supplied license script license.sql with SQL*Plus.

SQL> @license

The license key is inserted into table SQLWORDI_PARAMETER will be verified every time you run SQLWord.

You can display the license information with SQL*Plus:

SQL> select sqlwordi.show_license from dual;

SHOW_LICENSE

--------------------------------------------------------------------

SQLWord is licensed to <company-name> for <number> users on Oracle database server <oracle-database-server-name>

Configuring the Oracle HTTP server

If you want to run SQLWord “web-enabled” through the Oracle HTTP server you need to define a Database Access Descriptor.

The Oracle HTTP Server is a release of the Apache Webserver that contains modules that provide HTTP interfaces with different components of the Oracle database. The Oracle database itself also supports direct communication to these components via native support for the HTTP protocol, so the Oracle HTTP Server (stand alone) is not required.

However, if you want to service HTTP requests on a different machine than what the Oracle database is installed, you may want to download and configure the Oracle HTTP Server to act as an independent web server.

We must set up a DAD to allow the Oracle HTTP server to connect to the database whenever a specific URL is requested.

Oracle HTTP Server (stand alone):

If mod_plsql is part an Oracle Application Server, it is recommended to use the Oracle Enterprise Manager Application Server Control Console, to create a DAD.

You can also edit the DAD configuration file on the server at:

$ORACLE_HOME\Apache\modplsql\conf/dads.conf.

$ORACLE_HOME \ohs\modplsql\conf/dads.conf.

Example:

# ===============================================================

# dads.conf: mod_plsql DAD Configuration File

# ===============================================================

# 1. Please refer to dads.README for a description of this file

# ===============================================================

# Note: This file should typically be included in your plsql.conf

# file with the "include" directive.

# Hint: You can look at some sample DADs in the dads.README file

# ===============================================================

SetHandler pls_handler

Order allow,deny

Allow from All

AllowOverride None

PlsqlDatabaseUsername SQLWORD_DEMO

PlsqlDatabasePassword SQLWORD_DEMO

PlsqlDatabaseConnectString MY_ORACLE_SERVER:1521:MY_INSTANCE

PlsqlSessionStateManagement StatelessWithResetPackageState

</Location>

You can find a dads.conf at: C:\Program Files\SQLWord10\Examples\DAD\dads.conf

For more details on configuration DAD refer the Oracle HTTP Server Administration Guide.

Embedded Oracle HTTP Server (DBMS_EPG):

Since the introduction of XML DB in Oracle 9i Release 2, the Oracle server has contained an embedded HTTP server in addition to the Apache HTTP server. In Oracle 10g Release 2 this HTTP server can be used as an embedded PL/SQL gateway to run PL/SQL applications via mod_plsql. The administration of Database Access Descriptors for the XML DB HTTP server is performed using the DBMS_EPG package:

BEGIN

DBMS_EPG.create_dad(dad_name => 'DAD_SQLWORD '

,path => '/DAD_SQLWORD /*');

END;

-- Set the database-username.

BEGIN

DBMS_EPG.set_dad_attribute(dad_name => 'DAD_SQLWORD '

,attr_name => 'database-username'

,attr_value => 'SQLWORD_DEMO');

END;

-- Enable access to the specified schema.

BEGIN

DBMS_EPG.authorize_dad(dad_name => 'DAD_SQLWORD '

,user => 'SQLWORD_DEMO');

END;

You can find an example SQL-script at:

C:\Program Files\SQLWord10\SQL\ create_DAD.sql

Port: 8080

The default port is 8080. You can find out your configured port :

select dbms_xdb.gethttpport from dual;

The port can be changed and must be changed if the port is already in use by another service. You can change the port from SQL*Plus connected as a DBA user:

call dbms_xdb.sethttpport(7777);

alter system register;

How to specifify the DAD in SQLWord

1. Start SQLWord-Developer from the Windows Start Menu or directly at:

C:\Program Files\SQLWord10\Bin\SQLWordDeveloper.exe

Open the options screen by clicking on the first button

The SQLWord Options screen now shows up:

2. Specify the “URL mod_plsql” with the following syntax:

http://<hostname>:<port>/ or http://<IP-adress>:<port>/

Press on this button to get the default URL from your database connection

Example: http://my_oracle_server:8080/

3. Specify the “Database Access Descriptor” with the name of the one that you created before (DAD_SQLWORD)

Press on the button “Test DAD” to see:

SQLWord-Developer

Introduction

SQLWord-Developer is a 32-bits Windows application to support users in the development of source documents (templates).

The SQLWord-Developer application window is always displayed on top of other applications, so you can edit Microsoft Word documents and always have access to the SQLWord-Developer application.

The SQLWord-Developer application contains a button toolbar and a work area where you can edit <% tag %> scriptlets with PL/SQL-statements.

Toolbar buttons:

Shows a submenu where you can:

§ Connect to your Oracle database.

§ Display the options-window (described later in this section).

§ Display this Users Guide & Reference.

§ Show the about box.

Create a new Word document.

Open a Word document. If you select several files you will get a new screen where you can compile the selected files in one run.

Save the active Word document.

Undo the last Word command.

Find <% tag %> scriptlets in the active Word document and copy to the work area.

Paste the <% tag %> scriptlets from the work area into the active Word document.

Clear work area.

Insert standard <% tag %> scriptlets into the work area from a submenu.

Clear all <% tag %> scriptlets in the active Word document from invisible formatting code.

Compile the active Word document to a stored procedure.

Show the stored procedure from the active Word document and edit its parameters (descriptions & lookup SQL-statements).

Remove stored procedures & content from the Oracle database.

Run SQLWord from throug the webbrowser. The HTML page below shows up where you can select a report and specify values for input-parameters.

Run SQLWord client-server. The screen below shows up where you can select a report and specify values for file locations. When pressing on the “Run” button the screen below shows up where you can specify the input-parameters.

Popup-menu

Pressing on the right mouse button in the work area displays a popup-menu:

Scriptlets

SQLWord follows the syntax of Oracle PL/SQL Server Pages <% tag %> declarations.

Scripting tags are enclosed within the <% and %> delimiters and the first character(s) after the opening delimiter <% determine the type of the scripting tag.

The following describe each scripting tag in detail.

Declarations <%! {plsql_declaration} %>

The declaration tag can be used to declare types, cursors and also define local procedures as well. Note that you need the ! sign in this syntax.

Example: Declaring variables.

<%!

v1 number;

v2 varchar2(10) := '1234567890';

Example: Declaring a cursor c1.

<%!

cursor c1

select ename

, job

, sal

from emp

order by ename;

Statements <% {plsql_statement} %>

All PL/SQL statements can be used, such as for loops, assignments, calls to other stored procedures, etc. Note that a terminating semicolon is needed where PL/SQL requires it.

Example: for loop.

<%for r1 in c1 loop%>

<%end loop;%>

Example: assignments.

--
a := 'ABC' || 'DEF';
b := a || 'GHI';
c := my_procedure(a, b);

--
%>

Example: local block.

--
a := 'ABC' || 'DEF';
b := a || 'GHI';

--
declare
b varchar2(9);
begin
b := a || 'GHI';
end;
--

Example: exception handler.

<%
exception
when no_data_found then
null;
%>

Expressions <%= {plsql_expression} %>

The expression tag returns the value of any PL/SQL expression including PL/SQL function calls and places the value into the output document. Note that a terminating semicolon is not allowed.

Example:

<%= 10 + 2 %>
<%= 'ABC' || 'EFG' %>
<%= to_char(sysdate, 'dd.mm.yyyy hh24:mi:ss') %>

<%= r1.job%>

Example:

<%!

cursor c1

select ename

, job

, sal

from emp

order by ename;

<%for r1 in c1 loop%>

<%= r1.ename%> <%= r1.sal%> <%= r1.job%>

<%end loop;%>

Parameters <%@plsql parameter={ ... } %>

The parameter tag declares an input parameter to the document:

parameter="<name>" [ type="( varchar2 | number | date )" ] [ default="<default_value>" ]

The VARCHAR2 is default type. Default text values must be enclosed within single quotes within the double quotes, eg default="'xyz'".

Example:

<%@ plsql parameter="P_EMPNO" "type="number"%>
<%@ plsql parameter="P_ENAME" default="'KING'" %>

Page directive <%@ page subdocument="…"%>

The page directive tag is used to indicate to SQLWord that the source document is a subdocument.

subdocument=" [TRUE|FALSE]"

SQLWord can generate an output document out of several pieces of standard “text-blocks” (subdocuments). To indicate to SQLWord that a source document must be treated as a subdocument, you must use this page directive. Please examine Example3.doc in the next section which uses this page directive.

Example:

<%@ page subdocument="TRUE"%>

Examples

You can find SQLWord templates at:

C:\Program Files\SQLWord10\Examples

Example1.doc

The first example shows how you can generate a standard letter by using the <% tags %> described in the previous section. It uses the following <% tags %>:

· Declaration-tag:

<%!

cursor c1

select a.empno

, initcap(a.ename) MANAGER_NAME

, to_char(sysdate,'dd month yyyy') TODAY

from emp a

where empno = P_EMPNO;

cursor c2 (P_EMPNO number)

select initcap(ename) EMPLOYEE

, initcap(job) JOB

, sal

from emp

where mgr = P_EMPNO

order by ename;

· Parameter-tag:

<%@ plsql parameter="P_EMPNO" "type="number"%>

· Statement-tags:

<%for r1 in c1 loop%>

<%for r2 in c2 (r1.empno) loop%>

<%end loop;--SKIPROW%> Note: the comment --SKIPROW is used to

prevent an empty table-row!

<%end loop;%>

· Assignment-tags:

<%=to_char(sysdate,'fm dd month yyyy')%>

<%=r1.manager_name%>

<%=r2.employee%>

<%=r2.job%>

<%=r2.sal%>

<%for r1 in c1 loop%>

Redwood, <%=to_char(sysdate,'fm dd month yyyy')%>

Dear mr <%=r1.manager_name%>,

We inform you about the current salary of your employees:

Employee

Job

Salary

<%for r2 in c2 (r1.empno) loop%><%=r2.employee%>

<%=r2.job%>

<%=r2.sal%>

<%end loop;--SKIPROW%>

Sincerely,

Larry Ellis

<%end loop;%>

<%@ plsql parameter="P_EMPNO" "type="number"%>

<%!

--

cursor c1

is

select a.empno

, initcap(a.ename) MANAGER_NAME

, to_char(sysdate,'dd month yyyy') TODAY

from   emp a

where empno = P_EMPNO;

--

cursor c2 (P_EMPNO number)

is

select initcap(ename) EMPLOYEE

, initcap(job)   JOB

,      sal

from   emp

where mgr = P_EMPNO

order by ename;

--

%>

Example1.doc

After compiling example1.doc with SQLWord-Developer a stored procedure EXAMPLE1 is created.

Examine the PL/SQL code to see where the <% tags %> are placed.

CREATE OR REPLACE PROCEDURE EXAMPLE1

( p_empno number

)

IS

--

cursor c1

is

select a.empno

, initcap(a.ename) MANAGER_NAME

, to_char(sysdate,'dd month yyyy') TODAY

from   emp a

where empno = P_EMPNO;

--

cursor c2 (P_EMPNO number)

is

select initcap(ename) EMPLOYEE

, initcap(job)   JOB

,      sal

from   emp

where mgr = P_EMPNO

order by ename;

--

BEGIN

--

if sqlwordi.init_report('MSWORD') then

--

sqlwordi.put_content('EXAMPLE1',1);

sqlwordi.put_content('EXAMPLE1',2);

sqlwordi.put_content('EXAMPLE1',3);

--

for r1 in c1 loop

--

sqlwordi.put_content('EXAMPLE1',4);

(… until)

sqlwordi.put_content('EXAMPLE1',70);

--

sqlwordi.put_data(to_char(sysdate,'fm dd month yyyy'));

sqlwordi.put_content('EXAMPLE1',71);

sqlwordi.put_content('EXAMPLE1',72);

sqlwordi.put_content('EXAMPLE1',73);

sqlwordi.put_data(r1.manager_name);

sqlwordi.put_content('EXAMPLE1',74);

sqlwordi.put_content('EXAMPLE1',75);

sqlwordi.put_content('EXAMPLE1',76);

sqlwordi.put_content('EXAMPLE1',77);

--

for r2 in c2 (r1.empno) loop

--

sqlwordi.put_content('EXAMPLE1',78);

sqlwordi.put_data(r2.employee);

sqlwordi.put_content('EXAMPLE1',79);

sqlwordi.put_content('EXAMPLE1',80);

sqlwordi.put_data(r2.job);

sqlwordi.put_content('EXAMPLE1',81);

sqlwordi.put_data(r2.sal);

sqlwordi.put_content('EXAMPLE1',82);

--

end loop;

--

sqlwordi.put_content('EXAMPLE1',83);

--

end loop;

--

sqlwordi.put_content('EXAMPLE1',84);

--

end if;

--

sqlwordi.end_report;

--

exception

when others then

sqlwordi.put_data(sqlerrm);

--

END;

Redwood, 7 july 2009

Dear mr King,

We inform you about the current salary of your employees:

Employee

Job

Salary

Blake

Manager

2850

Clark

Manager

2450

Jones

Manager

2975

Sincerely,

Larry Ellis

Output document generated from example1.doc

Example2.doc

The second example calculates the summary of employee salaries per manager and uses an IF-ELSIF-ELSE construction to display different texts depending of the value of the calculated total salary. Note the use of parameter p_comment to display some flexible proza.

· Declaration-tag:

<%!

cursor c1

select a.empno

, initcap(a.ename) MANAGER_NAME

, to_char(sysdate,'dd month yyyy') TODAY

from emp a

where empno = P_EMPNO;

cursor c2 (P_EMPNO number)

select initcap(ename) EMPLOYEE

, initcap(job) JOB

, sal

from emp

where mgr = P_EMPNO

order by ename;

l_tot_salary number(10,2);

· Parameter-tag:

<%@ plsql parameter="P_EMPNO" "type="number"%>

<%@ plsql parameter="P_COMMENT" type="varchar2"%>

· Statement-tags:

<%for r1 in c1 loop%>

select sum(sal)

into l_tot_salary

from emp

where mgr = r1.empno;

<%for r2 in c2 (r1.empno) loop%>

<%end loop;--SKIPROW%> Note: the comment --SKIPROW is used to

prevent an empty table-row!

<%if l_tot_salary < 5000 then%>

<%elsif l_tot_salary between 5000 and 6000 then%>

<%else%>

<%end if;%>

<%end loop;%>

· Assignment-tags:

<%=to_char(sysdate,'fm dd month yyyy')%>

<%=r1.manager_name%>

<%=r2.employee%>

<%=r2.job%>

<%=r2.sal%>

<%=l_tot_salary%>

<%=p_comment%>

<%for r1 in c1 loop%>

Redwood, <%=to_char(sysdate,'fm dd month yyyy')%>

Dear mr <%=r1.manager_name%>,

We inform you about the current salary of your employees:

<%

select sum(sal)

into   l_tot_salary

from   emp

where mgr = r1.empno;

%>

Employee

Job

Salary

<%for r2 in c2 (r1.empno) loop%><%=r2.employee%>

<%=r2.job%>

<%=r2.sal%>

<%end loop;--SKIPROW%>

----------------- +

<%=l_tot_salary%>

<%if l_tot_salary < 5000 then%>

The total of the salary's is less than 5000.

<%elsif l_tot_salary between 5000 and 6000 then%>

The total of the salary's is somewhere between 5000 and 6000.

<%else%>

The total of the salary's is more than 6000.

<%end if;%>

Sincerely,

Larry Ellis

P.S. <%=p_comment%>

<%end loop;%>

<%@ plsql parameter="P_EMPNO" "type="number"%>

<%@ plsql parameter="P_COMMENT" type="varchar2"%>

<%!

--

cursor c1

is

select a.empno

, initcap(a.ename) MANAGER_NAME

, to_char(sysdate,'dd month yyyy') TODAY

from   emp a

where empno = P_EMPNO;

--

cursor c2 (P_EMPNO number)

is

select initcap(ename) EMPLOYEE

, initcap(job)   JOB

,      sal

from   emp

where mgr = P_EMPNO

order by ename;

--

l_tot_salary number(10,2);

--

%>

Example2.doc

After compiling example2.doc using SQLWord-Developer, a stored procedure EXAMPLE2 is created.

Examine the PL/SQL code to see where the <% tags %> are placed.

CREATE OR REPLACE PROCEDURE EXAMPLE2

(      p_empno number

, p_comment varchar2

)

IS

--

cursor c1

is

select a.empno

, initcap(a.ename) MANAGER_NAME

, to_char(sysdate,'dd month yyyy') TODAY

from   emp a

where empno = P_EMPNO;

--

cursor c2 (P_EMPNO number)

is

select initcap(ename) EMPLOYEE

, initcap(job)   JOB

,      sal

from   emp

where mgr = P_EMPNO

order by ename;

--

l_tot_salary number(10,2);

--

BEGIN

--

if sqlwordi.init_report('MSWORD') then

--

sqlwordi.put_content('EXAMPLE2',1);

--

for r1 in c1 loop

--

sqlwordi.put_content('EXAMPLE2',4);

sqlwordi.put_data(to_char(sysdate,'fm dd month yyyy'));

sqlwordi.put_content('EXAMPLE2',3);

sqlwordi.put_data(r1.manager_name);

sqlwordi.put_content('EXAMPLE2',4);

--

select sum(sal)

into l_tot_salary

from   emp

where mgr = r1.empno;

--

sqlwordi.put_content('EXAMPLE2',5);

--

for r2 in c2 (r1.empno) loop

--

sqlwordi.put_content('EXAMPLE2',6);

sqlwordi.put_data(r2.employee);

sqlwordi.put_content('EXAMPLE2',7);

sqlwordi.put_data(r2.job);

sqlwordi.put_content('EXAMPLE2',8);

sqlwordi.put_data(r2.sal);

sqlwordi.put_content('EXAMPLE2',9);

--

end loop;

--

sqlwordi.put_content('EXAMPLE2',10);

sqlwordi.put_data(l_tot_salary);

sqlwordi.put_content('EXAMPLE2',11);

--

if l_tot_salary < 5000 then

--

sqlwordi.put_content('EXAMPLE2',12);

--

elsif l_tot_salary between 5000 and 6000 then

--

sqlwordi.put_content('EXAMPLE2',13);

--

else

--

sqlwordi.put_content('EXAMPLE2',14);

--

end if;

--

sqlwordi.put_data(p_comment);

--

etc ..

Example3.doc

The third example is a mailing showing the use of two subdocuments implemented as stored procedures which are called from the main source document.

Note the page directive tags in the subdocuments: <%@ page subdocument="TRUE"%> and the use of procedure sqlwordi.new_page to create new pages.

Because of dependencies you must first compile “example3_textblock_1.doc” and “example3_textblock_2.doc” and then compile “example3.doc”!

<%for r1 in c1 loop%>

<%if c1%rowcount > 1 then sqlwordi.new_page; end if;%>

<%example3_textblock_1;%>

Dear mr <%=r1.manager_name%>,

We inform you about the current salary of your employees:

<%example3_textblock_2(r1.empno);%>

Sincerely,

Larry Ellis

<%end loop;%>

<%!

--

cursor c1

is

select a.empno

, initcap(a.ename) MANAGER_NAME

from emp a

where exists (select 'x'

from emp b

where a.empno = b.mgr)

order by a.ename;

--

%>

Example3.doc

<%@ page subdocument="TRUE"%>

Redwood, <%=to_char(sysdate,'fm dd month yyyy')%>

Example3_textblock_1.doc

<%@ page subdocument="TRUE"%>

<%@ plsql parameter="p_empno" type="varchar2"%>

Employee

Job

Salary

<%for r_emp in c_emp (p_empno) loop%><%=r_emp.employee%>

<%=r_emp.job%>

<%=r_emp.sal%>

<%end loop;--SKIPROW%>

<%!

--

cursor c_emp (p_empno number)

is

select initcap(ename) EMPLOYEE

, initcap(job)   JOB

,      sal

from   emp

where mgr = p_empno

order by ename;

--

%>

Example3_textblock_2.doc

Example4.doc

The fourth example shows how you can include display images. Depending on the salary the employee an image of the company car is displayed. At the at the end of every letter the handwritten signature from mr Ellis is also included as an image.

In this example we use images from our website at http://www.sequel.nl/examples, so you must have access to the Internet to display them.

To display an image you can use function SQLWORDI.DISPLAY_PICTURE that needs to know the location of the image by parameter p_url.

Note that this example also shows how you can place text from cursor c1 in the header of your document.

<%open c1; fetch c1 into l_header;close c1;%>

<%=l_header.company%>

<%=l_header.adress%>

<%=l_header.city%>

<%=l_header.tel%>

<%=l_header.website%>

<%for r2 in c2 loop%>

<%if c2%rowcount > 1 then sqlwordi.new_page; end if;%>

Redwood, <%=to_char(sysdate,'fm dd month yyyy')%>

Dear mr <%=r2.ename%>,

We send you a picture of the company car that you can afford according to your current salary: <%=r2.sal%>.

<%

if r2.sal < 1000 then

l_url1 := 'http://www.sequel.nl/examples/bmw1.jpg';

elsif r2.sal between 1000 and 2000 then

l_url1 := 'http://www.sequel.nl/examples/bmw2.jpg';

else

l_url1 := 'http://www.sequel.nl/examples/bmw3.jpg';

end if;

%>

<%=SQLWORDI.DISPLAY_PICTURE(l_url1)%>

I hope I have informed you well.

Sincerely,

<%=SQLWORDI.DISPLAY_PICTURE(l_url2)%>

Larry Ellis

<%end loop;%>

<%!

--

cursor c1

is

select 'Sunsail Yachting'           COMPANY

,      '29th Floor, Capital Square' ADRESS

, '10066 Redwood' CITY

,      'tel: 203-789-9954'          TEL

, 'www.sunsail.com' WEBSITE

from   dual;

--

cursor c2

is

select initcap(ename) ENAME

,      sal

from   emp

where empno in (7876, 7698, 7900)

order by ename;

--

l_header c1%rowtype;

l_url1 varchar2(100);

l_url2 varchar2(100) := 'http://www.sequel.nl/examples/signature.jpg';

--

%>

Example4.doc

Sunsail Yachting

29th Floor, Capital Square

10066 Redwood

tel: 203-789-9954

www.sunsail.com

Redwood, 7 july 2009

Dear mr Adams,

We send you a picture of the company car that you can afford according to your current salary: 1100.

I hope I have informed you well.

Sincerely,

Larry Ellis

Page one of the output document generated from example4.doc

Example5.doc

The fifth example shows a table report from an Oracle user. If you like to see some more complicated constructions examine the constructions that we used.

Steps to create a source document

Step 1: Start SQLWord-Developer

When starting SQLWord-Developer, the Oracle logon screen shows up.

Ø Connect to the Oracle user where SQLWord was installed by your DBA.

After connecting to the Oracle database, the SQLWord Developer window shows up:

You can resize the SQLWord-Developer application window to enlarge the work area where you can edit the scriptlets.

This SQLWord-Developer application window can be moved to another position on your desktop. The best place is to position it in the upper left corner of your screen.

The next time when SQLWord-Developer is started, this window is positioned on the last positon where you left it.

Step 2: Create a new source document

Press the button in SQLWord-Developer toolbar. A new document is opened in Microsoft Word.

Step 3: Type in your “static” text

For this of course you can use all Microsoft Word features. Be sure that the layout of all static text is done before you go to the next step.

Step 4: Place <% tag %> scriptlets

Prepare your <% tag %> scriptlet in the work area. You can use the button to select the tag that you need and paste it from the submenu into the work area.

Ø Type in your SQL-statement:

Ø Now copy the prepared scriptlet from the work area to the Word source document by pressing the copy button. Do this for all the scriptlets that you need.

Step 5: Save the source document

Press the button to save the Word source document.

Step 6: Compile the source document to a stored procedure

Compile the active source document to a stored procedure by pressing the button.

After finishing this screen shows up (if you did not make mistakes):

If the generated stored procedure contains errors, you probably want to see the invalid PL/SQL code. In that case choose “Yes” to examine the invalid read-only PL/SQL source code.

Always fix errors in your source document and recompile the source document until it is valid.

Step 7: Specify input-parameters (if necessary)

If you want to specify input-parameters, press the button. Change to the second tab “Parameters” to fill in a prompt and description or specify a look-up SQL-statement.

Note that lookup SQL-statements always must have two columns:

1. The first column must give an unique identifier that will be assigned to the input parameter after choosing.

2. The second column must give the description (varchar2) that will be displayed in the lookup-list.

Step 8: Run it!

You can run the report that you just created by pressing the or button from the SQLWord-Developer toolbar.

Run SQLWord client-server. The screen below shows up where you can select a report and specify values for file locations.

When pressing the “Run” button a parameter screen shows up where you can specify input parameters:

Run SQLWord through the web interface. The HTML page below shows up where you can select a report and specify values for input-parameters:

Options

Press the button from the toolbar and choose “Options” from the submenu.

The options window shows up. This window contains 4 tabs, which are described below.

HTTP tab

On this tab you can specify the HTTP settings that SQLWord needs to know for calling the Oracle HTTP server.

URL mod_plsql: http://<hostname>:<port>/

Usually the port is 8080, 7777 or 7778.

Example: http://my_oracle_server:8080/

Database Access Descriptor: The DAD name that you want to use (for more information about configuring DAD examine page 9)

Pressing this button a suggestion for the URL is done.

Test if your DAD is working to see this message:

Yes, the Database Access Descriptor (DAD) is working!

Email tab

On this tab you can specify e-mail settings, in case you want to send an output document by e-mail from your Oracle database. SQLWord uses for this the UTL_SMTP package.

SMTP host: The name of your SMTP-server or IP-address.

SMTP domain: The domain name of the local sending host. This is used for identification purposes.

SMTP port: The port number. Usually this is port 25.

For more information how to send e-mail, examine section “Frequently asked questions”.

Client Server tab

On this tab you can specify the Client Server settings specific for your PC.

Word editor: If you want to use another editor than Microsoft Word you must specify which executable SQLWord should call to show the output document.

Show maximized: Indicates if Microsoft Word should maximize on opening.

License tab

On this tab you can see the license information (read-only).

The installation of a license key is not possible in this screen (see page 8).

SQLWord Run

Introduction

SQLWord Run is a 32-bits Windows application for running SQLWord reports through the web interface or client-server.

Menu options:

File: Shows a submenu where you can:

§ Connect to your Oracle database.

§ Display the options-window (described on page 34).

Run: Run SQLWord client-server. The screen below shows up where you can select a report and specify values for file locations.

When pressing the “Run” button a parameter screen shows up where you can specify input parameters:

Web: Run SQLWord through the web interface. The HTML page below shows up where you can select a report and specify values for input-parameters:

Help: Shows a submenu where you can:

§ Display this Users Guide & Reference.

§ Show the about box.

Command line syntax

You can run the SQLWordRun client-server executable from the command-line with the following syntax:

SQLWordRun.exe keyword=<value> keyword=<value> keyword=<value> etc.

Keywords

Keyword	Description
userid=username/password@host	Oracle connect-string.
procedure=stored procedure name	Name of the stored procedure to execute.
outfile=filename	Name and location of the output-file. In the run-window the output-file name can be specified and the directory can be choosen using the button on the right of the field.
[parfile=filename]	Name and location of the parameter-file. In the run-window the parameter-file can be choosen using the button on the right of the field.
[logfile=filename]	Name and location of the log-file. This file contains the logging information of the execution. In the run-window the log-file can be choosen, using then select button on the right of the field.
[editor=Y/N]	Open Word editor with the output document.
[readonly=Y/N]	Protect the output document by setting a secret password on the output document. This option is only available for Microsoft Word.
[print=Y/N]	Print the output document. This option is only available for Microsoft Word.
[copies=number]	The number of copies to print. This option is only available for Microsoft Word.
[printer=printername]	The name of the printer. This option is only available for Microsoft Word.
[showerror=Y/N]	To suppress all interactive messages (usefull for batch jobs).
[wordmacro=macroname]	Run a Word-Macro on opening of the output document. This option is only available for Microsoft Word.
[role=rolename/password]	You can enable a database-role when running SQLWord (the same way as Oracle*Reports does)

The keywords between the straight [brackets] are optional keywords.

Example1:

SQLWordRun.exe" userid=sqlword_demo/sqlword_demo@orcl procedure=example1 outfile="C:\Windows\Temp\example1_out.doc" parfile="C:\Program Files\SQLWord10\Examples\Templates\example1.par"

Example2:

SQLWordRun.exe" userid=sqlword_demo/sqlword_demo@orcl procedure=example1 outfile="C:\Windows\Temp\example1_out.doc" parfile="C:\Program Files\SQLWord10\Examples\Templates\example1.par"

readonly=Y

Example3:

SQLWordRun.exe" userid=sqlword_demo/sqlword_demo@orcl procedure=example1 outfile="C:\Windows\Temp\example1_out.doc" parfile="C:\Program Files\SQLWord10\Examples\Templates\example1.par"

editor=N print=Y copies=1 printer=" HP OFFICEJET G SERIES"

You can find a sample batch script at:

C:\Program Files\SQLWord10\Examples\Templates\run_cs_example1.bat

Parameter file

In a parameter file you can specify the values for the input-parameters.

SQLWordRun reads the parameter file before execution the stored procedure and assigns the values to the input-parameters of the stored procedure, when calling.

The parameter file has the following syntax:

Example:

DEPTNO=10

HIRE_DATE=02-07-2009

ENAME='JONES'

You can find a sample parameter file at:

C:\Program Files\SQLWord10\Examples\Templates\example1.par

Integration with Web-enabled Oracle*Forms

Example

SQLWord can be integrated into an Web-enabled Oracle*Forms application. An implementation example is available.

Ø Start Oracle Forms Developer and open example1.fmb at:

C:\Program Files\SQLWord10\Examples\WebForms\example1.fmb

Edit forms-procedure create_SQLWord_letter and modify the URL with your settings for:

--------------------------------------------------------------------

PROCEDURE create_SQLWord_letter

l_url varchar2(2000);

BEGIN

if to_char(:emp.empno) is not null then

l_url := 'http://<host>:<port>/<DAD_SQLWORD>/example1?p_empno='

|| to_char(:emp.empno);

web.show_document(l_url, '_blank');

else

message('Please first select an employee!');

message('.');

end if;

END;

--------------------------------------------------------------------

Ø Now compile example1.fmb to example1.fmx and run it with your webbrowser using the Oracle JInitiator Java Applet:

Ø Select an employee and press button “Create letter”

The standard forms procedure “web.show_document”calls Oracle stored procedure “example1” with this URL:

http://<host>:<port>/<DAD_SQLWORD>/example1?p_empno=7698

and the output document is opened in a new webbrowser window:

Migration from SQLWord 2.1

How to do it

A migration tool to upgrade your SQLWord 2.1 source documents to the new SQLWord syntax is available at:

C:\Program Files\SQLWord10\Bin\Migrate_SQLWord_v2_to_v10.exe

After connecting to your Oracle database and pressing the button this screen shows up:

Select the source document that you want to migrate and press the “Convert” button. The conversion starts and when finished the new source document is opened by Microsoft Word.

Frequently asked questions

How can I remove unwanted empty new lines?

When unwanted empty new lines are generated, probably Microsoft Word has done some underwater formatting around a <% tag %>.

Example:

Suppose that in a Microsoft Word source document you see this sentence:

<%for r1 in c1 loop%>

If you typed in this sentence straight away then in RTF-code you probably see:

\par <%for r1 in c1 loop%>

SQLWord skips those new lines, if only the string "\par " is found before a <% tag %>.

But now you do some editing because you may want to change the name of the “for loop” for some reason. Now Microsoft Word unfortunately sometimes puts extra "underwater" formatting code. The same sentence then might be coded in RTF as:

\par <%for r1 in c1 loop%>\par }{\b\lang1033

or even worse:

\par <%for r1 in c}{\b\insrsid13720673\charrsid13720673 1 loop%>

In the first case this results in an unwanted extra new line.

In the second case, SQLWord-Developer doesn't recognise the <% tag %> and you get a syntax-error on compiling.

A workaround is to clear all <% tag %> scriptlets from the invisible underwater formatting code by pressing the button on the SQLWord-Developer toolbar.

How can I skip a row in a Word table?

If you place in a Word table-row inside a scriptlet the comment --SKIPROW then that table-row is omitted in the output document.

Example:

Employee	Job	Salary
<%for r2 in c2 (r1.empno) loop%><%=r2.employee%>	<%=r2.job%>	<%=r2.sal%>
<%end loop;--SKIPROW%>

Example:

Employee	Job	Salary
<%for r2 in c2 (r1.empno) loop%><%=r2.employee%>	<%=r2.job%>	<%=r2.sal%>
<%end loop;--SKIPROW%>
		---------- +
		<%=l_tot_salary%>

How can I display carriage-returns in the output document?

When you use SQLWord to display text from a field that contains several carriage-returns or linefeeds, they are ignored in the output document.

A solution is to change the carriage-return chr(10) by the rtf code "\par " in your select-statement.

Example:

select replace(txt_text,chr(10), chr(92) || 'par ') description

from rm_text_lines

where ... etc

How can I display images in the output document?

To display an image you can use function SQLWORDI.DISPLAY_PICTURE that needs to know the location of the image by parameter p_url.

Examine Example4 on page 28 for more details.

How can I suppress input-parameters in the web interface?

If you don’t want to ask the end user every input-parameter which is defined in a stored procedure then you can suppress some input-parameters by giving the values to the second parameter P_HIDE_PARAMETERS to procedure SQLWORDI.RUN_SQLWORDI.

If you have more than one parameter to suppress you must separate them with semi-columns;

Example:

Suppose you want to give parameter P_EMPNO a value straight from your Oracle*Forms9i application and ask the end-user only for entering some flexible proza into input-parameter P_COMMENT in example2.doc then try this URL:

http://my_oracle_server:8080/dad_sqlword_demo/sqlwordi.run_sqlwordi?p_procedure=example2&p_hide_parameters=p_empno=7839

How can I change the presentation of decimal values in the output document?

The presentation of decimal values in the output document depend on:

§ The language settings from your Oracle database. Ask your DBA to check the value of parameter NLS_LANGUAGE (select * from V$NLS_PARAMETERS).

§ The Microsoft Windows language settings on your PC. You can change this in the Windows Control-panel in the “Regional and Language” options screen:

How can I create new pages in the output document?

You can call stored procedure SQLWORDI.NEW_PAGE to do this.

If you want to create new pages inside a for loop try this construction:

<%!

cursor c1

select ename

from emp

order by ename;

<%for r1 in c1 loop%>

<%if c1%rowcount > 1 then sqlwordi.new_page; end if;%>

Employee: <%=r1.ENAME%>,

<%end loop;%>

How can I send an output document by e-mail from my Oracle database?

SQLWord can send an e-mail from the Oracle database with the output document as an attachment. SQLWord uses the Oracle UTL_SMTP-package:

You need to configure the e-mail-settings in the options screen from SQLWord-Developer (described on page 30).

Ø Edit script “send_email.sql” at: C:\Program Files\SQLWord10\SQL\send_email.sql and change the address for the email_sender and email_recipients.

----------------------------------------------------------------

begin

sqlwordi.open_report_session( p_email_subject => 'Hi, we send

you this document generated by

SQLWord!'

, p_email_sender =>

'scott@somewhere.com'

, p_email_recipients =>

'info@sequel.nl');

example1(p_empno => 7839);

sqlwordi.close_report_session;

end;

----------------------------------------------------------------

Ø Start SQL*Plus and run:

SQL> @ C:\Program Files\SQLWord10\SQL\send_email.sql

PL/SQL-procedure successfully completed.

SQL>

Ø Now check your e-mail and see if there’s an new e-mail with an MSWord-document attached!

How can I write an output document on the Oracle database server using UTL_FILE?

Edit script “write_utl_file.sql” at:

C:\Program Files\SQLWord10\Examples\SQL\write_utl_file.sql

and modify the file-locations to your environment.

First you must use the Oracle “create directory” command (ask your DBA to do this).

---------------------------------------------------------------

create or replace directory MY_OUTPUT_DIR as 'C:\Windows\Temp';

grant read, write on directory MY_OUTPUT_DIR to public;

---------------------------------------------------------------

begin

sqlwordi.open_report_session( p_utl_file_location =>

'MY_OUTPUT_DIR'

, p_utl_file_filename =>

'example1_out.doc');

example1(p_empno => 7839);

sqlwordi.close_report_session;

end;

---------------------------------------------------------------

Ø Start SQL*Plus and run: write_utl_file.sql

SQL> @write_utl_file.sql

PL/SQL-procedure successfully completed.

SQL>

Ø Check if the file is created on the specified file-location on your Oracle database server.

How can I save the output document into an Oracle table?

The output document is available as a CLOB through function GET_CLOB which is available in package SQLWORDI.

Examine the script “clob_example.sql” at:

C:\Program Files\SQLWord10\SQL\ clob_example.sql:

-- First create a table where you want to save the generated output

create table MY_OUTPUT

( doc_name varchar2(30)

, dd_created date

, source CLOB);

-- Now call stored procedure EXAMPLE1 and immediately get the

-- generated word-document by calling function SQLWORDI.GET_CLOB.

prompt Press any key ...

pause

BEGIN

example1(7839);

insert into my_output

( doc_name

, dd_created

, source)

values

( 'EXAMPLE1_' || to_char(sysdate,'ddmmyyyy-hh24:mi:ss')

, sysdate

, sqlwordi.get_clob);

commit;

END;

How can I create dynamic hyperlinks?

To create dynamic hyperlinks you can use function SQLWORDI.DISPLAY_HYPERLINK that needs two parameters:

§ The text to display in the Word-document

§ The URL

Please examine example6 at

C:\Program Files\SQLWord10\Examples\Templates\Example6.doc to see an implementation example of this feature.

Hints & Tips

Compile several documents

You can quickly create stored procedures or recompile several source documents in one run.

Press the button from the SQLWord-Developer toolbar and select all your source documents:

The next screen shows up:

Press the button “Create all” and the selected source documents are processed. If one or more is invalid after the compilation then the color of the row changes to red.

You can open the source document in Word by double-clicking on the row in the grid.

Clearing formatting code

Sometimes an error may occur because the generated output document has invalid RTF-syntax.

This is caused because in the original source document the layout is done in such a way, that the code starts somewere above a FOR LOOP and ends inside the FOR LOOP.

A simple but effective method to get rid of all invisible formatting is to select the text in the Word document, copy it (Ctrl+C) , paste it (Ctrl+V) in a simple ascii-editor (notepad), then select the text again from there, copy it (Ctrl+C) and paste it (Ctrl+V) back again into the Word document.

Clearing all scriptlets

All your scriptlets should be without any formatting code.

You can clear all <% tag %> scriptlets from the invisible underwater formatting code by pressing the button on the SQLWord-Developer toolbar.