Insert of SQL Scripts of Certain Length Into Oracle Fails

[GoogleCodeExporter]

What steps will reproduce the problem?
1. Create an Oracle user/schema.
2. Run RoundhousE so that it will attempt to apply the 3 attached SQL scripts 
to the schema created in step 1. Use options to cause RoundhousE to place its 
tables in the same schema.
3. Observe first 2 scripts succeed.
4. Observe error:
roundhouse.databases.oracle.OracleDatabase with provider 
System.Data.OracleClient does not provide a facility for recording scripts run 
errors at this time.
could not insert: [roundhouse.model.ScriptsRunError#1][SQL: INSERT INTO 
RoundhousE_ScriptsRunErrors (repository_path, version, script_name, 
text_of_script, erroneous_part_of_script, error_message, entry_date, 
modified_date, entered_by, id) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)]
RoundhousE encountered an error.
NHibernate.Exceptions.GenericADOException: could not insert: 
[roundhouse.model.ScriptsRunError#1][SQL: INSERT INTO 
RoundhousE_ScriptsRunErrors (repository_path, version, script_name, 
text_of_script, erroneous_part_of_script, error_message, entry_date, 
modified_date, entered_by, id) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)] ---> 
System.Data.OracleClient.OracleException: ORA-01461: can bind a LONG value only 
for insert into a LONG column

   at System.Data.OracleClient.OracleConnection.CheckError(OciErrorHandle errorHandle, Int32 rc)
   at System.Data.OracleClient.OracleCommand.Execute(OciStatementHandle statementHandle, CommandBehavior behavior, Boolean needRowid, OciRowidDescriptor& rowidDescriptor, ArrayList& resultParameterOrdinals)
   at System.Data.OracleClient.OracleCommand.ExecuteNonQueryInternal(Boolean needRowid, OciRowidDescriptor& rowidDescriptor)
   at System.Data.OracleClient.OracleCommand.ExecuteNonQuery()
   at NHibernate.AdoNet.AbstractBatcher.ExecuteNonQuery(IDbCommand cmd)
   at NHibernate.AdoNet.NonBatchingBatcher.AddToBatch(IExpectation expectation)
   at NHibernate.Persister.Entity.AbstractEntityPersister.Insert(Object id, Object[] fields, Boolean[] notNull, Int32 j, SqlCommandInfo sql, Object obj, ISessionImplementor session)
   --- End of inner exception stack trace ---
   at NHibernate.Persister.Entity.AbstractEntityPersister.Insert(Object id, Object[] fields, Boolean[] notNull, Int32 j, SqlCommandInfo sql, Object obj, ISessionImplementor session)
   at NHibernate.Persister.Entity.AbstractEntityPersister.Insert(Object id, Object[] fields, Object obj, ISessionImplementor session)
   at NHibernate.Action.EntityInsertAction.Execute()
   at NHibernate.Engine.ActionQueue.Execute(IExecutable executable)
   at NHibernate.Engine.ActionQueue.ExecuteActions(IList list)
   at NHibernate.Engine.ActionQueue.ExecuteActions()
   at NHibernate.Event.Default.AbstractFlushingEventListener.PerformExecutions(IEventSource session)
   at NHibernate.Event.Default.DefaultFlushEventListener.OnFlush(FlushEvent event)
   at NHibernate.Impl.SessionImpl.Flush()
   at roundhouse.infrastructure.persistence.Repository.save_or_update[T](T item)
   at roundhouse.databases.DefaultDatabase`1.insert_script_run_error(String script_name, String sql_to_run, String sql_erroneous_part, String error_message, String repository_version, String repository_path)
   at roundhouse.migrators.DefaultDatabaseMigrator.record_script_in_scripts_run_errors_table(String script_name, String sql_to_run, String sql_erroneous_part, String error_message, String repository_version, String repository_path)
   at roundhouse.migrators.DefaultDatabaseMigrator.run_sql(String sql_to_run, String script_name, Boolean run_this_script_once, Boolean run_this_script_every_time, Int64 version_id, Environment environment, String repository_version, String repository_path)
   at roundhouse.runners.RoundhouseMigrationRunner.traverse_files_and_run_sql(String directory, Int64 version_id, MigrationsFolder migration_folder, Environment migrating_environment, String repository_version)
   at roundhouse.runners.RoundhouseMigrationRunner.run()
could not insert: [roundhouse.model.ScriptsRunError#1][SQL: INSERT INTO 
RoundhousE_ScriptsRunErrors (repository_path, version, script_name, 
text_of_script, erroneous_part_of_script, error_message, entry_date, 
modified_date, entered_by, id) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)]


What is the expected output? What do you see instead?
I expect to see a message indicating the script ran successfully. I see an 
error message from NHibernate.

What version of the product are you using? On what operating system?
0.8.0.300 on Windows XP SP3. The Oracle version is 11.1.0.6.0

Please provide any additional information below.
First of all, a LONG in Oracle is a large object character string. As I 
understand, it's a deprecated data type that's been replaced by CLOB. LONG can 
be longer than VARCHAR2 but not as long as CLOB. It appears that if the length 
of the script falls within a certain range, RoundhousE (or perhaps NHibernate 
or Oracle internally) interprets the text as a LONG instead of a CLOB, and 
Oracle fails to convert the LONG to a CLOB (which sounds really stupid to me).

Original issue reported on code.google.com by [email protected] on 2 Aug 2011 at 5:35

Attachments:

• BUG0001_ShortEnough.sql
• BUG0002_LongEnough.sql
• BUG0003_MiddleLength.sql