Stored procedure failed: SP_PUBLICATIONS_FIND_CONFLICTS ORA-06550: line %s, column %s:

Question

After running the Tridion CM installer (Oracle 11.2.0.4) all seemed fine. However, when trying to add a new Publication, the following error is observed:

(80040356) Unable to save new item Stored procedure failed: SP_PUBLICATIONS_FIND_CONFLICTS ORA-06550: line %s, column %s: %s

The ORA-06550 indicates that a variable declaration was missed in the code, a developer error. Surely this can't be the real root cause in this case. When we ran the installer, everything looked normal, i.e. we didn't notice any errors. All was smooth.

The Event Viewer log shows this:

Stored procedure failed: SP_PUBLICATIONS_FIND_CONFLICTS
ORA-06550: line %s, column %s:
%s


Component: Tridion.ContentManager.CoreService
Errorcode: 0
User: NT AUTHORITY\NETWORK SERVICE

StackTrace Information Details:
   at Oracle.DataAccess.Client.OracleException.HandleErrorHelper(Int32 errCode, OracleConnection conn, IntPtr opsErrCtx, OpoSqlValCtx* pOpoSqlValCtx, Object src, String procedure, Boolean bCheck, Int32 isRecoverable)
   at Oracle.DataAccess.Client.OracleException.HandleError(Int32 errCode, OracleConnection conn, String procedure, IntPtr opsErrCtx, OpoSqlValCtx* pOpoSqlValCtx, Object src, Boolean bCheck)
   at Oracle.DataAccess.Client.OracleCommand.ExecuteReader(Boolean requery, Boolean fillRequest, CommandBehavior behavior)
   at Oracle.DataAccess.Client.OracleCommand.ExecuteDbDataReader(CommandBehavior behavior)
   at System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader()
   at Tridion.AudienceManagement.DomainModel.Dal.DataSession.OpenRecordsetByStoredProcedure(UserContext userContext, String sProcName, Dictionary`2 parameters, Dictionary`2 outputParameters, Int32 timeoutSeconds)
   at Tridion.ContentManager.Extensibility.EventSubscription.DeliverEvent(IEnumerable`1 subjects, TcmEventArgs eventArgs, EventPhases phase)
   at Tridion.ContentManager.Extensibility.EventSystem.DeliverEvent(IEnumerable`1 subjects, TcmEventArgs eventArgs, EventDeliveryPhase deliveryPhase)
   at Tridion.ContentManager.Extensibility.EventSystem.DeliverEvent(IdentifiableObject subject, TcmEventArgs eventArgs, EventDeliveryPhase deliveryPhase)
   at Tridion.ContentManager.IdentifiableObject.Save(SaveEventArgs eventArgs)
   at Tridion.ContentManager.IdentifiableObject.Save()
   at Tridion.ContentManager.CoreService.CoreServiceBase.Create(IdentifiableObjectData data, ReadOptions readBackOptions)
   at SyncInvokeCreate(Object , Object[] , Object[] )
   at System.ServiceModel.Dispatcher.SyncMethodInvoker.Invoke(Object instance, Object[] inputs, Object[]& outputs)
   at Tridion.ContentManager.CoreService.CoreServiceInvoker.Invoke(Object instance, Object[] inputs, Object[]& outputs)
   at System.ServiceModel.Dispatcher.DispatchOperationRuntime.InvokeBegin(MessageRpc& rpc)
   at System.ServiceModel.Dispatcher.ImmutableDispatchRuntime.ProcessMessage5(MessageRpc& rpc)
   at System.ServiceModel.Dispatcher.ImmutableDispatchRuntime.ProcessMessage31(MessageRpc& rpc)
   at System.ServiceModel.Dispatcher.MessageRpc.Process(Boolean isOperationContextSet)

Any ideas on what went wrong?

Answer 1

The stored procedure SP_PUBLICATIONS_FIND_CONFLICTS is actually part of the Audience Manager feature. When we ran the Tridion 2013 SP1 installer, we left it checked. For some reason this stored proc has an invalid object and as a result does not compile. The Tridion Core Service makes a call to this procedure when saving a Publication and since it couldn't compile an error was thrown.

To work around the issue, we have re-ran the Tridion CM installer and unchecked Audience Manager to uninstall it. Once this was completed and the CM server got restarted, new Publications can now be saved.

As to why some of the stored procs in the Audience Manager database could not compile, we're reaching out to Support for help here.