Oracle, SQL, WOrd and a batch file

Guest · Jun 14, 2004

I have written a SQL statement to extract data from an Oracle database with a variable &1. I have tested the sql statement and am confident that the statement is correct (in place of the variable I used an identifier for this test - otherwist, the statement would not work using SQLPLUS alone). The aim is to use a "print" button on the front end gui (client 6i engine 8i) to run a Word output. I am not interested in doing a mail merge etc. as I want to use preformatted SQL and a batch file triggered by the database to spool the SQL into a Word document.

I already have a batch file which does not suit the purpose but at least copies the sql, connects to sqlplus and opens word (I will attach the batch file a the end with it's result).

*******************************************

copy start.sql+%3+finish.sql temp.sql
set FILEN=dt%username%
sqlplus %4/%5@%6 @temp.sql %1 %TEMP% %FILEN%
"C:\Program Files\Microsoft Office\Office\winword.exe" /t %2 / mmerge
exit

*******************************************

the variables from %2 to %6 are from the Oracle database and I presume that %1 is already set to the identifier I specify.

Do you have the solution?

Cindy M -WordMVP- · Jun 15, 2004

Hi =?Utf-8?B?cmlja3Nvbg==?=,

I have written a SQL statement to extract data from an Oracle database with a variable

&1. I have tested the sql statement and am confident that the statement is correct (in
place of the variable I used an identifier for this test - otherwist, the statement would
not work using SQLPLUS alone). The aim is to use a "print" button on the front end gui
(client 6i engine 8i) to run a Word output. I am not interested in doing a mail merge
etc. as I want to use preformatted SQL and a batch file triggered by the database to
spool the SQL into a Word document.

I already have a batch file which does not suit the purpose but at least copies the

sql, connects to sqlplus and opens word (I will attach the batch file a the end with it's
result).

*******************************************

copy start.sql+%3+finish.sql temp.sql
set FILEN=dt%username%
sqlplus %4/%5@%6 @temp.sql %1 %TEMP% %FILEN%
"C:\Program Files\Microsoft Office\Office\winword.exe" /t %2 / mmerge
exit

*******************************************

the variables from %2 to %6 are from the Oracle database and I presume that %1 is

already set to the identifier I specify.

Do you have the solution?

What's the problem?

Cindy Meister
INTER-Solutions, Switzerland
http://homepage.swissonline.ch/cindymeister (last update Jun 8 2004)
http://www.word.mvps.org

This reply is posted in the Newsgroup; please post any follow question or reply in the
newsgroup and not by e-mail :-)

Guest · Jun 16, 2004

As a result of the - much appreciated and bountiful - adept knowledge transferred with this post I have decided - as a result - to re run the post. Once again many thanks for all the info.

OK

I want to spool the preformatted SQL statement into Word using the batch file. The intention is to allow a user to click on a "Print" button via the GUI (Oracle which uses the batch commands) allowing the batch file to run the SQL statement and fill in the parameters where there are variables and open a Wod Doc with the results. I have now modified the batch file and am getting different results

************************************************

copy start.sql+%3+finish.sql
set FILEN=%username%
plus80 %4/%5@%6 @%3 %1 %TEMP% %FILEN%
"C:/Program Files/Microsoft Office/Office/winword.exe" /t %2 /mmerge
exit

**************************************************

this is the result when the program is run:

**************************************************

<DIR>copy start.sql+<file_name>.sql+finish.sql
start.sql
<file_name>.sql
finish.sql
1 file(s) copied.

<DIR>set FILEN=<USERNAME>

<DIR>plus80 <userid>/<password>@<version> @<file_name>.sql <the client identifier> <the temp dir> <USERNAME>

SQL*Plus: Release 8.0.6.0.0 - Production on Tue Jun 15 15:57:16 2004

(c) Copyright 1999 Oracle Corporation. All rights reserved.

Connected to:
Oracle8i Enterprise Edition Release 8.1.7.4.0 - Production
JServer Release 8.1.7.4.0 - Production

========================================================================

<HEADING>
========================================================================

1. <HEADING>
-------------------

no rows selected

2. <HEADING 3>
---------------------

no rows selected

not spooling currently
Input truncated to 4 characters
Disconnected from Oracle8i Enterprise Edition Release 8.1.7.4.0 - Production
JServer Release 8.1.7.4.0 - Production

<DIR>"C:/Program Files/Microsoft Office/Office/winword.exe" /t <file_name>.doc /mmerge

**********************************************************

the <> indicate values which I have substituted for the submission of this post publically.

Within the SQL statement I have a variable "&1" which infers the "%1" in the batch file.

The "mmerge" requests a data source and I need a similar command to open word with the results of the sql query.At the moment after the batch file is run, Word opens and request a data source but I want to spoll the sql results into word.

cheers tn

************************************************************

Cindy M -WordMVP- said:
Hi =?Utf-8?B?cmlja3Nvbg==?=,

&1. I have tested the sql statement and am confident that the statement is correct (in
place of the variable I used an identifier for this test - otherwist, the statement would
not work using SQLPLUS alone). The aim is to use a "print" button on the front end gui
(client 6i engine 8i) to run a Word output. I am not interested in doing a mail merge
etc. as I want to use preformatted SQL and a batch file triggered by the database to
spool the SQL into a Word document.
sql, connects to sqlplus and opens word (I will attach the batch file a the end with it's
result).
already set to the identifier I specify.
What's the problem?

Cindy Meister
INTER-Solutions, Switzerland
http://homepage.swissonline.ch/cindymeister (last update Jun 8 2004)

This reply is posted in the Newsgroup; please post any follow question or reply in the
newsgroup and not by e-mail

Cindy M -WordMVP- · Jun 17, 2004

Hi =?Utf-8?B?cmlja3Nvbg==?=,

As a result of the - much appreciated and bountiful - adept knowledge transferred with

this post I have decided - as a result - to re run the post.1. This is a peer-to-peer support group, all volunteers, and everyone here has a life
outside the group. We work, same as you, to support ourselves. So answers may not come
"turn around"; they can take a few days when things get busy.

2. You've asked a high-level developer question in an end-user group. How should you know
it's an end-user group? Read a few postings. If you want a high-level, developer
discussion with quicker responses, you should post in a developer-oriented newsgroup.
There are, on this server, office.developer and word.vba newsgroups that would be more
appropriate.

3. You put the meat of the message at the very end, after a lot of code, which means that
*I* will only take up the conversation when I have an extra bit of time on my hands to
sift through all this.

"The "mmerge" requests a data source and I need a similar command to open word with the
results of the sql query.At the
moment after the batch file is run, Word opens and request a data source but I want to
spoll the sql results into word. "

The document should NOT be linked to a data source. IOW, it shouldn't / may NOT be a mail
merge main document. Word has no way to let you "spool" your results into the document
without mail merge; and in order to do so, you have to automate Word and place the data,
item by item, into "target ranges". Most often, bookmarks are used for this purpose.

so, this doesn't work:

"allowing the batch file to run the SQL statement and fill
in the parameters where there are variables and open a Wod Doc with the results."

It would have to be
- gather the data into variables (the programming kind)
- open the document in Word
- pass the information into the "target ranges"
- present the result to the user

Now, you show starting Word from the command line, executing a macro in the Normal.dot
template. The only way you could go with this approach is if the data were first saved to
a separate file (delimited text or an HTML table, for example) and the macro would link to
this as the data source. OR it could go the route of picking up the data and dumping it
into "targets" (as above, with no mail merge).

The only other approach available to you would be if Word 2003 is available, and the
document in question were saved as WordML. Then you could use XML processing on the file,
without first opening it in Word, to substitute the data into the file, whereever
required. In this case, the targets could be XML tags.

I'm guessing you're not going to be happy with this situation, probably because you want
the user to be able to set up documents as he likes? If this is so, you must be giving the
user a list of available fields? How are you doing this?

Or, wait a couple of years and use VSTO 2.0 that's coming out with the next release of
Visual Studio .NET.

Cindy Meister
INTER-Solutions, Switzerland
http://homepage.swissonline.ch/cindymeister (last update Jun 8 2004)
http://www.word.mvps.org

This reply is posted in the Newsgroup; please post any follow question or reply in the
newsgroup and not by e-mail :-)

Guest · Jun 22, 2004

Cindy, respect where respect is due, thanks for the lesson in newsgroup ethiquette.

Just getting back to solving the problem... I have the solution:

To get a sql output in word without using mail merge, write a batch file to spool the sql to a temp location on the users c:\ then get the spool file from the temp location to open in word.
copy start.sql+%3+finish.sql
set FILEN=%username%
plus80 %4/%5@%6 @%3 %1 %TEMP% %FILEN%
"C:/Program Files/Microsoft Office/Office/winword.exe" %TEMP%\%FILEN%
exit
the batch file should look as above and should be placed in the database according to the settiings in the database for it to work properly. the sql also needs to have the spool command as follows:-

spool &2.\&3..doc

about the batch file:-

- the batch file copies the sql file
- then sets a file name to = the username
- it then connects to the database using a user id, password and the database name on which connection is made and runs the pre-formatted sql in sql plus
- the results of the run are then placed in the temp location on the users c:\a with - - the file name set to the user's user id
- the batch file then requests msword to open with the results of the spool file.

see? easy! no macros, no need for a data source just basic logic.

One last coment if I may though, I would like thank Cindy again for spending her time trying to resolve my query and also like to show my appreciation for my new found "newsgroup ethiquette".

Cindy M -WordMVP- · Jun 22, 2004

Hi =?Utf-8?B?cmlja3Nvbg==?=,

- the results of the run are then placed in the temp location on the users c:\a with

- - the file name set to the user's user id

- the batch file then requests msword to open with the results of the spool file.

see? easy! no macros, no need for a data source just basic logic.

No macros for you, apparently :-)

something in the tool you're using (sql plus?) must be
taking care of "plugging the data into the Word document" part for you. Then you just
open that document from the CommandLine. Cool :-)

Glad you managed to track down the
solution.

Cindy Meister

Oracle, SQL, WOrd and a batch file

Guest

Cindy M -WordMVP-

Guest

Cindy M -WordMVP-

Guest

Cindy M -WordMVP-