Package home | Report new bug | New search | Development Roadmap Status: Open | Feedback | All | Closed Since Version 1.5.0b4

Bug #8531 tableInfo() fails due to lob locator index
Submitted: 2006-08-22 10:51 UTC
From: dv at allyoucanthink dot de Assigned: lsmith
Status: Closed Package: MDB2_Driver_oci8 (version 1.0.1)
PHP Version: Irrelevant OS: irrelevant
Roadmaps: (Not assigned)    

 [2006-08-22 10:51 UTC] dv at allyoucanthink dot de (Dmitri Vinogradov)
Description: ------------ call of tableInfo() on tables containing some LOB fields fails on getTableIndexDefinition() for lob locator index. Oracle creates automatically lob locator index starting with SYS_IL*** for every LOB field but there's no records for this index in user_ind_columns.


 [2006-08-22 10:55 UTC] lsmith (Lukas Smith)
do you have a proposal for a fix? it seems like listIndexes() should be changed to ignore these indexes to begin with?
 [2006-08-22 11:09 UTC] dv at allyoucanthink dot de
well... adding following lines at the begin of MDB2_Driver_Reverse_oci8::getTableIndexDefinition() solves this problem in my particular case. if(substr(strtoupper($index_name),0, 6) == 'SYS_IL') { return null; }
 [2006-08-22 11:25 UTC] lsmith (Lukas Smith)
Ok, I will talk to people at Oracle how to best approach this. The way I see it I have the following options: 1) ignore these indexes in listTableIndexes() 2) ignore these indexes in tableInfo() 3) ignore these indexes in getTableIndexDefinition() 4) improve getTableIndexDefinition() to query other information schema table when it sees that the prefix indicates that its not a user created index
 [2006-08-22 11:52 UTC] dv at allyoucanthink dot de
It seems like any system generated index has a flag in user_indexes: user_indexes.generated = 'Y'. So the changing the query in MDB2_Driver_Manager_oci8::listTableIndexes() as bellow solves the problem more elegant. AFAIK all table names in this view are upper case $query = 'SELECT index_name name FROM user_indexes'; $query.= ' WHERE table_name='.strtoupper($table); $query.= ' AND generated=' .$db->quote('N', 'text');
 [2006-08-22 11:59 UTC] lsmith (Lukas Smith)
Yeah, that would be option 1) which I currently think is the most viable one. I will see what my Oracle contacts say. Thank you for the investigation into this.
 [2006-08-26 15:05 UTC] lsmith (Lukas Smith)
This bug has been fixed in CVS. If this was a documentation problem, the fix will appear on by the end of next Sunday (CET). If this was a problem with the website, the change should be live shortly. Otherwise, the fix will appear in the package's next release. Thank you for the report and for helping us make PEAR better. I decided to go with 1) and ignore Oracle internal indexes.