Hello,
In the last week or so we’ve started seeing periodically INFO and ERROR messages in the console complaining about a table or view called RX_NUMBER_TABLE on our production server. But there is no table of view of that name, and never was, not even in a near-fresh install I keep on my PC for comparison purposes. We haven’t installed a patch recently. It then dumps page after page of XML and java.sql.SQLException stuff. Seems to be something to do with cxSearch.
Any ideas?
Thanks,
Andrew.
Can you post the exact ERROR and INFO level messages you are receiving? What version of Rx are you working with? What database software/version are you using?
Thanks. There is an awful lot of it, so I’ve attached it in a text file rather than pasting it in here. We are running 6.5.2, recently patched to RX-15816 (although I think the problem predates that) using Oracle 11g.
Sometimes when importing a customer database I’ve found errors about missing RX_NUMBER_TABLE in the import log, but I’ve never personally noticed any errors or functionality issues while using the product once the clone is fully set up. TS has done some cursory research and discovered that Rx isn’t complaining about a table, it’s complaining about a missing Table Type. We’re not sure why yet, but the reported table type isn’t part of an out of the box installation using any version of the product (that we’ve noticed).
The workaround we use locally is to create the table type using the following DDL. Don’t forget to replace <yourschema> with your actual schema name:
CREATE TYPE “<yourschema>”.“RX_NUMBER_TABLE” AS TABLE OF NUMBER
We haven’t yet researched this issue in depth because we haven’t noticed or received any reports of it causing any functionality or performance issues, but if it’s causing you any, please create a TS ticket so we can track the behavior and come up with a firm resolution.</yourschema></yourschema>
This table is used only when the db is Oracle. It is used as part of the work-around for Ora’s IN clause limitations, (searching might run into it.) It is created by the installer, so I don’t understand why you wouldn’t have one. Creating the table should resolve the problem.
Our 6.5.2 Build 200710P01 (3234) [RX-13785] installation on Oracle 10g also does not have a “RX_NUMBER_TABLE” table. I have not noticed any errors in our logs with not having it. There is a table called “NEXTNUMBER” that might be related.
It turns out this ‘table’ does not appear with the list of all other tables (at least in the Enterprise Manager Console.) You need to look under ‘User Types’/‘Table Types’. If you don’t see it there, open the [rxroot]/rxconfig/Installer/install.log and search for that name. Verify that it was successfully installed. On my system, it had failed to install due to insufficient privileges.
Thanks for the info. Looking in our install.log I find the following:
INFO [RxISLogger] 01/08/08 12:04:20 Executing statement : CREATE OR REPLACE TYPE FBURHYTHMYX.RX_NUMBER_TABLE AS TABLE OF NUMBER
INFO [RxISLogger] 01/08/08 12:04:21 Succesfully executed statement.
But there were no types until I executed the same SQL statement myself on our Oracle 11g system this week. It hasn’t complained about lacking RX_NUMBER_TABLE since, but then it was only sporadically doing so before. Is there anything else I need to do now this type exists?
No, there is nothing else. It was sporadic because it only gets used if a search (or some other query function) needs to put more than ~1k entries in an IN clause. I wonder how it got deleted?
I had to import a bunch of Oracle databases this week and they all reported a failure with this table type. If the environment the customer is using is a clone, that’s probably why they don’t have it. I’m not sure if this is a permissions thing or not, but the TS database users have Connect and Resource privileges (per documentation). Admittedly, I haven’t really looked into the issue since the workaround is very simple.