Publishing Error - Unable to upgrade database schema.

Publishing Error - Unable to upgrade database schema.

  
Hi,

I initially created a InitiativeType as a text string and put some data in it.

I changed it to InitiativeType - a new type so It could be a drop down box instead. ( it has records )

However when I try to compile I get the below error. Can we access the databases so I can drop the table
and let the appication recreate it ? It is obviously protecting the data I have put in previously, but seeing as I am just using test data it does not matter. It is kind of good that it won't destroy data, but in this I am sure it is ok to destroy it.




Publishing Error

Unable to upgrade database schema. Error trying to change database field 'OSUSR_ztz_Project.INITIATIVETYPE' type (New Def: NUMBER(10); DB Def: VARCHAR2(0)).
Exception: ORA-01439: column to be modified must be empty to change datatype
SQL: ALTER TABLE "OSADMIN_LT1"."OSUSR_ZTZ_PROJECT" MODIFY "INITIATIVETYPE" NUMBER(10) DEFAULT NULL

Exception Details:
[1] Publishing Error: Unable to upgrade database schema. Error trying to change database field 'OSUSR_ztz_Project.INITIATIVETYPE' type (New Def: NUMBER(10); DB Def: VARCHAR2(0)).
Exception: ORA-01439: column to be modified must be empty to change datatype
SQL: ALTER TABLE "OSADMIN_LT1"."OSUSR_ZTZ_PROJECT" MODIFY "INITIATIVETYPE" NUMBER(10) DEFAULT NULL
at OutSystems.HubEdition.DeploymentController.Database.DBScriptsExecutor.UpdateDb(String eSpaceName, HEMessageArrayList warnings, String eSpaceKey, Int32 eSpaceVersionId, DeployPhase phase, String uniqueId)
   at OutSystems.HubEdition.DeploymentController.Compiler.DeployEspaceUpdateDB(String eSpaceName, Int32 eSpaceVersionId, String uniqueId, String username, DeployPhase phase)
   at OutSystems.HubEdition.DeploymentController.Compiler.DeployEspaceUpdateDB(ICompilerContext context, String eSpaceName, Int32 eSpaceVersionId, String uniqueId, String username, DeployPhase phase)
   at System.Runtime.Remoting.Messaging.StackBuilderSink._PrivateProcessMessage(IntPtr md, Object[] args, Object server, Object[]& outArgs)
   at System.Runtime.Remoting.Messaging.StackBuilderSink.SyncProcessMessage(IMessage msg)
   at System.Runtime.Remoting.Messaging.ServerObjectTerminatorSink.SyncProcessMessage(IMessage reqMsg)
   at System.Runtime.Remoting.Messaging.ServerContextTerminatorSink.SyncProcessMessage(IMessage reqMsg)
   at System.Runtime.Remoting.Channels.CrossContextChannel.SyncProcessMessageCallback(Object[] args)
   at System.Runtime.Remoting.Channels.ChannelServices.DispatchMessage(IServerChannelSinkStack sinkStack, IMessage msg, IMessage& replyMsg)
   at System.Runtime.Remoting.Channels.BinaryServerFormatterSink.ProcessMessage(IServerChannelSinkStack sinkStack, IMessage requestMsg, ITransportHeaders requestHeaders, Stream requestStream, IMessage& responseMsg, ITransportHeaders& responseHeaders, Stream& responseStream)
   at OutSystems.HubEdition.DeploymentController.Compiler.ClientIPServerSink.ProcessMessage(IServerChannelSinkStack sinkStack, IMessage requestMsg, ITransportHeaders requestHeaders, Stream requestStream, IMessage& responseMsg, ITransportHeaders& responseHeaders, Stream& responseStream)
   at System.Runtime.Remoting.Channels.Tcp.TcpServerTransportSink.ServiceRequest(Object state)
   at System.Runtime.Remoting.Channels.SocketHandler.ProcessRequestNow()
   at System.Runtime.Remoting.Channels.SocketHandler.BeginReadMessageCallback(IAsyncResult ar)
   at System.Net.LazyAsyncResult.Complete(IntPtr userToken)
   at System.Threading.ExecutionContext.RunInternal(ExecutionContext executionContext, ContextCallback callback, Object state, Boolean preserveSyncCtx)
   at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state, Boolean preserveSyncCtx)
   at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state)
   at System.Net.ContextAwareResult.Complete(IntPtr userToken)
   at System.Net.Sockets.BaseOverlappedAsyncResult.CompletionPortCallback(UInt32 errorCode, UInt32 numBytes, NativeOverlapped* nativeOverlapped)
   at System.Threading._IOCompletionCallback.PerformIOCompletionCallback(UInt32 errorCode, UInt32 numBytes, NativeOverlapped* pOVERLAP)
[2] Oracle.ManagedDataAccess.Client.OracleException (0x0000059F): ORA-01439: column to be modified must be empty to change datatype
   at OracleInternal.ServiceObjects.OracleCommandImpl.VerifyExecution(OracleConnectionImpl connectionImpl, Int32& cursorId, Boolean bThrowArrayBindRelatedErrors, OracleException& exceptionForArrayBindDML, Boolean& hasMoreRowsInDB, Boolean bFirstIterationDone)
   at OracleInternal.ServiceObjects.OracleCommandImpl.ExecuteNonQuery(String commandText, OracleParameterCollection paramColl, CommandType commandType, OracleConnectionImpl connectionImpl, Int32 longFetchSize, Int64 clientInitialLOBFS, OracleDependencyImpl orclDependencyImpl, Int64[]& scnFromExecution, OracleParameterCollection& bindByPositionParamColl, Boolean& bBindParamPresent, OracleException& exceptionForArrayBindDML, Boolean isFromEF)
   at Oracle.ManagedDataAccess.Client.OracleCommand.ExecuteNonQuery()
   at OutSystems.HubEdition.Extensibility.Data.ExecutionService.BaseExecutionService.ExecuteNonQuery(IDbCommand cmd)
   at OutSystems.Internal.Db.Command.ExecuteNonQuery(String description, Boolean isApplication, Boolean skipLog, Boolean applyTransformationsToParameters)
   at OutSystems.Internal.Db.Command.ExecuteNonQuery(Boolean skipLog)
   at OutSystems.HubEdition.DeploymentController.Database.DBScriptsExecutor.ExecuteSql(HEMessageArrayList warnings, DBAdminTransactions adminTrans, String fullQuery, Int32 updateDBTimeout)
Hi Michael,

You should try searching google more before posting to find the answer:
http://www.outsystems.com/forums/discussion/12680/drop-table-and-attributes-from-a-cloud-database/
- short answer - just cut and paste the entity back in and it will re-create the table.
Thanks that worked!
I amm curious though, if the table does contain data that can't be dropped, how can this be fixed ? Cutting and pasting removed the database completely.
The cut+past workaround is good in an early stage of development (if you don't care about the existing data). Like you said, it creates a new physical table.

The alternative would be clearing the data for this column only - either through code (e.g. advanced query with update {Project} set {Project}.[InitiativeType] = null) or by accessing the DB directly and deleting the data (or simply removing the column).

Until this is done, the database can't convert the column to the new sql type.