I have gathered some issues while implementing hibernate using Oracle9i. See below:
My main issues implementing hibernate for Oracle were related to the usage of tablespace, indexes (constraints) and executing the generated queries.
1.INDEXES
- Oracle uses the following syntax to create indexes:
create index ind1 ON t1(KEY1);
instead of alter table t1 add index ind1..
in 2.1.8 this is not created for indexes, instead switching from MySql to Oracle no change in code reflecting this occurred. For unique columns and PK no code is necessary as Oracle adds indexes for all such columns implicitly. Note on FK: since FK are made unique because of their relation to the PK in the corresponding tablecolumn, these will be indexed too automatically.
Note: when executing CREATE TABLE, indexes will be created for the specific columns. Therefore, when trying to index these indexed columns a “SQLException: ORA-01408: such column list already indexed.” error will occur. For all those columns that are automatically indexed therefore, the proper place to specify info about the index is in the CREATE TABLE statement. Only columns that were not indexed by this, should be appended after the createSQLString.
alter table t1 drop constraint ind1;
drop table t1 cascade constraints;
Similarly sort of, when dropping a constraint newly generated with a new unique name, an error will occur: “SQLException: ORA-02443: Cannot drop constraint – nonexistent constraint.” The proper way however to drop constraints while dropping a table is to leave out the first line, which is superfluous.
2. TABLESPACES
A separate tablespace may be indicated for indexes in Oracle. Since indexes use a fixed space for admin reasons, this is to be preferred in Oracle.
To add a separate tablespace use syntax:
Using index tablespace ts_index;
After the column declaration. I added a property in the hibernate properties to allow a separate index tablespace, this on top of the default_schema property.
Indicating tablespace for tables use the following syntax after the closing parenthesis of the column declaration:
Tablespace ts_defaulschema
3. IF EXISTS
Currently, there is 1 single output file generated with all SQL code starting with DROP INDEXES/ DROP TABLE. However, usage differs very likely. Some tables might be new, or the whole schema might be newly initialized. In MYSql the IF EXISTS clause will prevent failure. Oracle SQL does not allow this clause, and will fail unless all tables exist already. Separate output files for dropping schema and creating schema will add flexibility.
http://opensource.atlassian.com/projects/hibernate/browse/HB-1459
Allowing the choice to run the drop schema part with ant, e.g. from a remote computer, also favores this option and greatly improves user friendliness.
4. NAMING RESTRICTIONS
Naming restrictions differ greatly per database. E.g. Mysql has a maximum column length of 64, Oracle of 30, keywords differ too. It would be nice to allow a abbreviations map to be used according to a certain hibernate schema. This map would contain user abbreviations key-value pairs (longname-abbreviate2name) with a naming restrictions meta file. Let Hibernate abbreviate until certain maximum length is reached using the abbreviations map, or report the unsolved issues.
Junior Developer
Sanno Point Capital Management LLC
623 Fifth Ave, 16th Floor
New York, NY 10022
Direct: (212) 588 7102
Main: (212) 588 7100
Fax: (212) 588 7199
IMPORTANT: E-mails from this company normally contain confidential material for the sole use of the intended recipient. The use, distribution, transmittal or re-transmittal by an unintended recipient of any communication is prohibited without our express approval in writing or by e-mail. Any use, distribution, transmittal or re-transmittal by persons who are not intended recipients of this e-mail may be a violation of law and is strictly prohibited. If you are not the intended recipient please contact the sender and delete all copies. E-mail transmission cannot be guaranteed to be secure or error-free. The sender therefore does not accept liability for any errors or omissions in the contents of this transmission. This message is provided for informational purposes only and should not be construed as an offer to sell or the solicitation of an offer to buy any securities or related financial instruments. All e-mails sent to or from Sanno Point Capital Management L.L.C. and its affiliates are to be used for our business purposes only. E-mails sent from or to the company are subject to review by the company.
