Board index » delphi » BDE 5.1 can't see Oracle8i function index

BDE 5.1 can't see Oracle8i function index

Hi,

I hope this has a simple solution because otherwise it could be a bigish
problem for me.

I've created a function based index under Oracle8i as follows:
create index usr.ddb_department_idx on usr.department_tbl
(upper(department_fld)) tablespace users;

and the BDE does not list it with a call to DbiGetIndexDescs. In
addition the BDE returns an error of unknown index with a call to
DbiSwitchToIndex. In each case I'm using the correct case to specify the
index name, that is, upper case eg DDB_DEPARTMENT_IDX.

To test, I created an index as follows:
create index usr.ddb_department_idx on usr.department_tbl
(department_fld) tablespace users;

and the functions work as expected.

What I need is a case insensitive index. Attempting to create a case
insensitive index directly from the BDE using DbiAddIndex with the case
insensitive parameter to TRUE, fails.

Is there an easy work around?

Many thanks, Ian

--
Ian Mitchell
Research and Development Office
University of Tasmania
Research House
GPO Box 252-01
Hobart, Tasmania, Australia, 7001

Phone (03) 6226 7457
Fax   (03) 6226 2765

Intl Phone +61 3 6226 7457
Intl Fax +61 3 6226 2765

http://www.research.utas.edu.au/
"...for who can escape what he desires."
Genesis, And Then There Were Three, 1978

 

Re:BDE 5.1 can't see Oracle8i function index


Are you sure the index was created?  You can't create an index of that
kind in Oracle 7.3.4, and I didn't think you could in 8i.

John

Re:BDE 5.1 can't see Oracle8i function index


Please forgive the second post.  It was intended for another thread!

John

Re:BDE 5.1 can't see Oracle8i function index


In message <3835A668.2439A...@healthplanning.com>, John Pierce stated:
John,

you can cancel any message that you've posted. I've taken care of it
for now.

===
Regards
Ralph (TeamB)
===

Re:BDE 5.1 can't see Oracle8i function index


Hi John,

It is documented clearly under create index. I needed to be connected as
system to create it and the documentation says the following:

"After creating a function-based index, collect statistics on both the
index and its base table using the ANALYZE statement (see "ANALYZE").
Oracle cannot use the function-based index until these statistics have
been generated."

so I did the following:
analyze index usr.ddb_department_idx compute statistics;
analyze table usr.department_tbl compute statistics;

I guess if there is no easy way to create a case insensitive index which
the BDE recognises, I have 2 options:
1. use a select <key field> from table where upper('department
name')=upper(department)
and then use the key field value in the DbiGetRecord function, or
2. create another column which I maintain as the upper case of department
and create a standard index on that.

Ian

Quote
John Pierce wrote:
> Are you sure the index was created?  You can't create an index of that
> kind in Oracle 7.3.4, and I didn't think you could in 8i.

> John

--
Ian Mitchell
Research and Development Office
University of Tasmania
Research House
GPO Box 252-01
Hobart, Tasmania, Australia, 7001

Phone (03) 6226 7457
Fax   (03) 6226 2765

Intl Phone +61 3 6226 7457
Intl Fax +61 3 6226 2765

http://www.research.utas.edu.au/
"...for who can escape what he desires."
Genesis, And Then There Were Three, 1978

Re:BDE 5.1 can't see Oracle8i function index


Ian,

Don't give up yet.  If you use a TQuery with requestlive := false, you
can send your SQL query directly to Oracle, even including a hint to use
this index.  Are you using a TTable?  If so, don't.  TQueries are much
more efficient.

John

Re:BDE 5.1 can't see Oracle8i function index


Thanks John, haven't given up just yet! I thing I've found a work around as
follows:

For the tables which require a case insensitive index I've create a normal
index. Then I build an SQL statement like the following:
select department from department_tbl where upper(department)='value to
search for'

I then use the returned value via the normal index to retreive the actual
record. Works well, so far. Why do I do it this way? I'm porting an
application which uses BDE API calls. The application was written for BC++
before the C++ wrapper functions even existed.

Cheers, Ian

Quote
John Pierce wrote:
> Ian,

> Don't give up yet.  If you use a TQuery with requestlive := false, you
> can send your SQL query directly to Oracle, even including a hint to use
> this index.  Are you using a TTable?  If so, don't.  TQueries are much
> more efficient.

> John

Re:BDE 5.1 can't see Oracle8i function index


Ian,

I'm still intrigued by this.  You said you had to create the index as
System.  Have you tried prefixing the index name with System.?  It seems
as though the problem is that the BDE does not associate the index with
the table.

John

Re:BDE 5.1 can't see Oracle8i function index


Hi John,

Sorry to take so long to reply, I've been working through this with Oracle
support. It turns out that for a user to create a function index they need
the "query rewrite" privilege which is easy to grant when logged on as
system as follows:
GRANT QUERY REWRITE TO USR;

Once this is done, usr can then create function indexes and they are end up
in the default tablespace for the usr. You can list the indexes as follows:
SELECT INDEX_NAME FROM USER_INDEXES WHERE TABLE_OWNER='USR';

and sure enough the function index appears along with all the standard
indexes. But it still does not appear to the BDE.

Cheers, Ian

Quote
John Pierce wrote:
> Ian,

> I'm still intrigued by this.  You said you had to create the index as
> System.  Have you tried prefixing the index name with System.?  It seems
> as though the problem is that the BDE does not associate the index with
> the table.

> John

Other Threads