Delphi 6 App. occurs a error message: Table is not indexed.

Question

I got a app developed in Delphi 6 with master/detail constrution. Both master and detail take TQuery to open the data source. It works fine when the master opens first, but after that an error message of "Table is not indexed" occured when the detail opens the data source.

The working account for app. is the SCHEMA_USER, which has a after login trigger to switch itself to SCHEMA_OWNER, it looks like this:

CREATE OR REPLACE TRIGGER AFTER_LOGON_TRG
AFTER LOGON ON SCHEME_USER.SCHEMA
BEGIN
  --switch to  SCHEMA_OWNER schema
  DBMS_APPLICATION_INFO.set_module(USER,'Inilialized');
  EXECUTE IMMEDIATE 'ALTER SESSION SET current_schema=SCHEMA_OWNER';

END;

I use SCHEMA_USER to login in DB and execute deltail's SQL, and it works fine. I don't know what's going wrong.

So, any idea is appreciated.

Thx adv.!!

David, Regards

Answer 1

The error message tells you exactly what the problem is, I think.

An index on the detail table's linking field is required so that, when the master table's record pointer is moved, the appropriate rows in the detail table can be found. The error message indicates that there is no index on the necessary column in the detail table.

The solution is to add an index to the table before opening the detail table. You should only need to do this once (I say should because you didn't indicate which database you're using).

EDIT: After the third comment describing the schema details.

Your problem is here: "The detail query's SQL: ... WHERE order_no = :order_no", while you say "4) order_no & custID are the two-columns index". You're only using half of the index value, and the detail table can't figure out what to do.

You need to either:

1) Create a new index that's only on order_no (which seems like a bad idea)

or

2) (Preferred) Add the custID to the detail query's SQL:

WHERE order_no = :order_no AND custID = :custID

Answer 2

Dear Gurus,

Finally we find out why Delphi shows the error message while App. logons as another user by setting CURRENT_SCHEMA on AFTER LOGON triiger.

The below is the steps to avoid the error :

1. Using TTable instead of TQuery to be the DETAIL(Master is still the TQuery).
2. Setting IndexFieldNames property instead of IndeName.

Then it works.

We guess that Delphi maybe uses USER_INDEXES view to query the indexes it could access rather than ALL_INDEXES view, so that Delphi cannot "correctly" get the indexes considered as that it should have. And that is!

Thanks guys!