Sitecore With Oracle

It is expected that Sitecore is supporting Oracle database as a part of cross-platform compatibility, but before you decide to use Oracle as a database for Sitecore, there are some areas you need to consider.

Sitecore Version Compatibility

Not all Sitecore's versions support Oracle database, have a look on this compatibility table to ensure that Sitecore version you are intending to use is supporting Oracle database

CMS 6.0

CMS 6.1

CMS 6.2

CMS 6.3

CMS 6.4

CMS 6.5

CMS 6.6

CMS 7.0

CMS 7.1

CMS 7.2

XP 7.5

XP 8.0

XP 8.1

XP 8.2

Oracle 12c

-

-

-

-

-

-

-

-

-

-

-

-

-

-

Oracle 11g R2

✓

✓

✓

✓

✓

✓

✓

✓[7]

✓

✓

-

-

-

✓[12]

Oracle 11g

✓

✓

✓

✓

✓

✓

✓

✓[7]

-

-

-

-

-

-

Oracle 10g

✓

✓

✓

✓

✓

-[8]

-

-

-

-

-

-

-

-

As you can see Sitecore 7.5, 8.0, and 8.1 are not supporting Oracle database, and until the moment of writing this blog Oracle database 12c is not supported yet.

Sitecore as CMS only

According to Sitecore installation guide: "The Oracle database storage setup is only available when Sitecore XP is running in Sitecore Experience Management mode (CMS-only mode), which lets you to run Sitecore XP without the Experience Database (xDB) enabled. Sitecore supports: 

 Oracle Database 11g R2 (11.2)"

Issues While Running Sitecore with Oracle DB

We faced multiple issues while connecting Sitecore to Oracle database, but with Sitecore support, and some of luck we managed to get over all of them, I felt it would be useful if I shared those issues with you, so here we go:

License Related Issues:

If you receive this error, it is most likely license related.

Object reference not set to an instance of an object.

Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.NullReferenceException: Object reference not set to an instance of an object.

Source Error:

An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below.

Stack Trace:

[NullReferenceException: Object reference not set to an instance of an object.] System.Web.HttpResponse.End() +11948915 System.Web.HttpResponse.Redirect(String url, Boolean endResponse, Boolean permanent) +472 Sitecore.Nexus.Licensing.NexusLicenseApi.DemandModule(String moduleName) +401 Sitecore.Data.Oracle.OracleDataApi..ctor(String connectionString) +38 Sitecore.Data.Oracle.OracleLinkDatabase..ctor(String connectionString) +41

Is the License Supports Oracle

You need to check if the license you are using is supporting Oracle database, if not then you may receive the above error.

License does not exist in the correct location

If the license used is supporting Oracle database and you still receiving the above issue then most likely the license is missing, if you installed the instance using Sitecore web application installer  then the license file will be placed inside [web root]/[instance]/Data folder, while the config files are pointing to [web root]/[instance]/Website/data folder so you can either move the license to correct location or change the config to point to correct location.

Item Creation, and Package Installation Issues

After we fixed above problems, we had our instance up and running, we started our party. Suddenly one of our team members jumped off his chair and said: "Stop the celebration, we have another problem" he discovered that we still unable to create any item, as well as we were unable to install any package the error we were receiving is below, 

ORA-00900: invalid SQL statement Server Error in '/' Application. ORA-00900: invalid SQL statement [OracleException (0x80004005): ORA-00900: invalid SQL statement] Oracle.DataAccess.Client.OracleException.HandleErrorHelper(Int32 errCode, OracleConnection conn, IntPtr opsErrCtx, OpoSqlValCtx* pOpoSqlValCtx, Object src, String procedure, Boolean bCheck) +1986 Oracle.DataAccess.Client.OracleCommand.ExecuteNonQuery() +5486 Sitecore.Data.DataProviders.Sql.DataProviderCommand.ExecuteNonQuery() +70 [DataException: Error executing SQL command: IF NOT EXISTS (SELECT * FROM "PublishQueue" WHERE "ItemID" = :p0 AND "Language" = :p1 AND "Date" = :p2 AND "Action" = :p3 ) INSERT INTO "PublishQueue" ( "ItemID", "Language", "Version", "Date", "Action" ) VALUES( :p4, :p5, :p6, :p7, :p8 )] [Exception: ORA-00900: invalid SQL statement] Sitecore.Data.DataProviders.Sql.DataProviderCommand.ExecuteNonQuery() +281 Sitecore.Data.DataProviders.Sql.<>c__DisplayClass15.<Execute>b__14() +74 Sitecore.Data.DataProviders.NullRetryer.Execute(Func`1 action, Action recover) +286 Sitecore.Data.DataProviders.Sql.SqlDataProvider.AddToPublishQueue(ID itemID, String action, DateTime date, String language, CallContext context) +337 Sitecore.Data.DataProviders.DataProvider.AddToPublishQueue(ID itemID, String action, DateTime date, String language, CallContext context, DataProviderCollection providers) +170 Sitecore.Publishing.DefaultPublishManager.AddToPublishQueue(Database database, ID itemId, String action, DateTime date, Boolean forceAddToPublishQueue, String language) +211 Sitecore.Publishing.DefaultPublishManager.AddToPublishQueue(Item item, String action, DateTime date, Boolean specificLanguage) +163 Sitecore.Publishing.DefaultPublishManager.AddToPublishQueue(Item item, ItemUpdateType updateType, Boolean specificLanguage) +260 Sitecore.Publishing.DefaultPublishManager.DataEngine_CreatedItem(Object sender, ExecutedEventArgs`1 e) +79 System.EventHandler`1.Invoke(Object sender, TEventArgs e) +0 Sitecore.Data.Engines.EngineCommand`2.RaiseEvent(EventHandler`1 handlers, Func`2 argsCreator) +129 Sitecore.Data.Engines.DataCommands.CreateItemCommand.Executed() +21 Sitecore.Data.Engines.EngineCommand`2.Execute() +173 Sitecore.Data.Engines.DataEngine.CreateItem(String itemName, Item destination, ID templateId, ID newId, DateTime created) +48

Fortentatilly, we contacted Sitecore support and they said that this behavior was registered as a bug in their tracking system.
However the solution was straight forward as described in the link below:

https://github.com/SitecoreSupport/Sitecore.Support.154851/releases/tag/8.2.2.0 

And finally everything worked as charm.

Please share your experience if you faced any other issues.

Happy Programming :)