Re: Anyone have experience with SQL*TextRetriev

My organization recently purchased version 2.0 of SQL Text Retrieval (TR). At this point we have had it for about a month, so my comments are based on my limited knowledge, and are my own opinions, not those of my organization.

To this point our experience has not been very good.

Install

We purchased version 2.0 of TR for Oracle version 6 and received a cartridge tape with version 1.X. We reported the error and then received a CD-ROM with version 2.0. We installed it but it was the version that only works on Oracle 7. We reported the error and got a correct cartridge tape the third time.

Let me note that we have Oracle version 6 and Oracle 7. When we received TR for Oracle 7 we attempted to install it on Oracle 7 while we waited for the correct version. One of our DBA's spent 3-4 days with the install and was not able to get it successfully installed. Among other problems, our DBA says that the Oracle 7 install is menu driven and makes you start the install from the beginning if something goes wrong which just needs a minor tweak (The installation does retain the selections made on the prior installation, which is a plus). Our DBA also notes that TR cannot be installed remotely. She had to install it locally on her machine and then attempt to move the application (via export/import) to the machine where she wanted it installed. This process did not work smoothly at all.

When we got the tape for V6 we put the 7 install on hold (where it still remains). The install for V6 went relatively smoothly. There were minor problems (like the install grants script missed a table, etc.).

When we first tried to run the sample form rtguide provided with the system as described on page 4-3 of the Developers Manual (rtguide username/password), the form could not be found. We found that the install process had not generated the form. We located the source code and generated it ourselves.

After compiling the form, the first time we ran it we got error TXR-00036 "no free slots available". The manual said we should "Use SQL*Plus to run the CLEARSLOTS.SQL file to reset the session slots table." We were unable to find CLEARSLOTS.SQL. We did find a file called rtclears.sql which included a comment that this file cleared the slots from the session table, but running this did not solve the problem. We finally were able to fix this by locating and running a clear slots option from the Administrator menu.

By the way, TR requires that you have SQL*Menu.

When we run the sqltr program and immediately hit the exit key, the cursor moves to the bottom right of the screen and hangs as if it is placed on a field outside the current page.

When we run the sqltr program and choose "Host O/S" from the Utilities menu, unix users who are assigned to Korn shell are placed into the C shell.

After install the txttut tutorial account has a synonym S_TXH_HITLIST which points to a nonexistent table systext.s_txh_hitlist (this hasn't caused any problems that we know of).

Lack of Support

It appears there are only a couple of people in Oracle support who are familiar with TR 2.0. On Friday, April 2nd our DBA was told that all the people who knew TR were gone. On some occasions we have had to wait several days for assistance. We have been told the number of support personnel for TR will soon be increased.

Product Difficulties

Forms which use TR use a special set of user exits. According to Oracle Technical Support, there is no SQL*Forms executable which allows you to load, generate and execute forms which utilize these user exits. Instead, the procedure is:

1. Use SQL*FORMS to design your form.
2. Generate the form under SQL*Forms.
3. Exit SQL*Forms and run the form using TR's own version of the runform command (rtiap30mnu).

This process is cumbersome because you can't run the form from SQL*Forms Designer mode.

Using the TR application requires a lot of programming. Let's say you want to add the ability to query on specific text in a long field to an existing query on a form with 30 fields. The programmer must manually create a lengthy query SQL statement by concatenating "SELECT" || text_field || from || table || where || field1 like field1value || and field2 like field2value and field3 like field3value etc. which is passed to TR via a user exit. The statement which is passed might be 1000 characters long and the syntax for using quotes is difficult to get right. Logic must be added to change the SQL statement if one or more fields is null (i.e. If field7 is not null then do not include "field7 like field7value in the query statement. This is where the sample form rtguide had problems). Furthermore, if there is a syntax error, often the only error message returned from the user exit is that there was one.

To have a user review the results of such a query the programmer must manually create form blocks, fields and triggers to deal with the results of that query. A custom form basically needs to be set up to handle the returned text fields. We have been able to accomplish some of this, but it is VERY time-consuming.

When coding calls to the TR user exits, we sometimes got unexplained core dumps.

We tried to use user exit trtmke as described in the manual and got an error message ":global.txf_message_dest does not exist". We could not find anything in the manuals about this global variable. None of the sample forms provided with TR referenced this variable. We got around this problem by creating the variable in our pre-form trigger.

Sample form rtguide provided has significant bugs. The key-exeqry trigger provided in the form and also shown on page 4-7 of the Developer's Guide does not work if the user does a query after entering values in both the investigator and title fields.

Documentation

Overall, the documentation is barely adequate. There is an Administator's Guide and a Developer's Guide, which are mostly reference manuals. What is lacking is a section describing things like
-- what do you do once TR is installed?

• the things TR can do for you are: 1..... A detailed explanation of how to do this follows..... 2..... A detailed explanation of how to do this follows.....
• A tutorial for Developers. The Developers Guide has a very small chapter on "Designing SQL*FORMS Applications" and two substantial chapters entitled "Building a Simple SQL*Forms Application" and "Building a Complex SQL*Forms Application". These demonstrate two sample applications and are very helpful. However, each chapter starts with the disclaimer "this is not a tutorial. It simply shows one way that a designer might decide to build a custom SQL*Forms application."

   In fact, these chapters are NOT tutorials. Tutorials are what is    needed. Showing a full-blown application is nice, but it doesn't    help the novice user figure out 1) how to start; 2) how to analyze    the application; 3) how to map out the steps necessary to move from    analysis to implementation.

We have not found documentation on how to use TR on an existing table. Though experimentation we found we had to add a new field to these tables, which contained a unique numeric value. We did not find a section in the manual on using TR on existing tables.

Some minor documentation bugs:

Page 5-6 of the Administrator's Guide describes changing keyboard definitions but this is a typo -- they are referring to keyboard delimiters.

Page 4-5 of the Developers Guide incorrectly uses a backwards quote (`) instead of a forward quote (') in their example

Index in Developers Guide lists user exit trxtsm when the entry is really for trxtsn.

Jeff Busch                                 Internet: busch1_at_llnl.gov
Voice: (510) 423-9134                      Fax: (408) 263-1360