Skip to Main Content
  • Questions

  • Apex 23.1 minimum compatible parameter setting

Breadcrumb

XMAS_TREE

The AskTOM team is taking a break over the holiday season, so we're not taking questions or responding to comments, but we might still sneak in a few Office Hours sessions! Thanks for being a member of the AskTOM community.

Question and Answer

Connor McDonald

Thanks for the question.

Asked: November 06, 2023 - 3:19 pm UTC

Last updated: November 01, 2024 - 5:40 am UTC

Version: 22.2

Viewed 1000+ times

You Asked

Hi TOM,

We are planning on upgrading from Apex 22.1 to Apex 23.1. We are currently on Oracle 19c using Apex 22.2:
SQL> 
SELECT * FROM v$version;


BANNER
--------------------------------------------------------------------------------
BANNER_FULL
----------------------------------------------------------------------------------------------------
BANNER_LEGACY CON_ID
-------------------------------------------------------------------------------- ---------------
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.16.0.0.0
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production 0

1 row selected.

SQL> 
SELECT * FROM apex_release;


VERSION_NO
----------------------------------------------------------------------------------------------------
API_COMPATIBILITY
----------------------------------------------------------------------------------------------------
PATCH_APPLIED
----------------------------------------------------------------------------------------------------
22.2.1
2022.10.07
APPLIED

1 row selected.

From what I read here:
https://docs.oracle.com/en/database/oracle/apex/23.1/htmig/apex-installation-requirements.html#GUID-02BE4A34-B631-412C-8A82-EB92DABBACE0

it states "Oracle APEX release 23.1 requires an Oracle Database release 19c or later". Based on the above query results and this statement, it would seem we are OK to upgrade, at least with regards to minimum Oracle Database release.

I have a question, though. Our current compatible parameter is set to 12.1:

SQL> 
SELECT name, type, value FROM v$parameter WHERE name = 'compatible';


NAME TYPE
-------------------------------------------------------------------------------- ---------------
VALUE
----------------------------------------------------------------------------------------------------
compatible 2
12.1.0.2.0

1 row selected.

Despite being on the minimum required Oracle DB release, would it be correct to assume we need to increase our compatible parameter setting? I'm guessing yes. If so, what is the minimum we can/should set it to? I'm guessing no less than 19.0.0, but please confirm, especially since I'm wondering if a compatible parameter of 12.2 would work.

Thanks!

SQL*PLUS-kinda'-guy

and Connor said...

"compatible" is a setting for the datafile headers. It is setting the ability to downgrade to that release without needing to restore from backup, so it doesn't play a role in the features/options in the version of the database you're using.

I'm guessing you upgraded from 12.1 a while back, so you may as well bump up compatible to 19 during your next scheduled outage, assuming you never plan to go back to 12.1

And yes, you're good to go with APEX 23

Rating

  (3 ratings)

We're not taking comments currently, so please try again later if you want to add a comment.

Comments

Requesting clarification on the compatibility parameter

A reader, November 07, 2023 - 4:20 pm UTC

Hi Connor,

I appreciate the reply! I was a bit confused with this statement about the compatible parameter:
"... it doesn't play a role in the features/options in the version of the database you're using."

Let me illustrate with an example:

-- 1) Confirm Oracle database version
SELECT * FROM v$version;

BANNER
--------------------------------------------------------------------------------
BANNER_FULL
----------------------------------------------------------------------------------------------------
BANNER_LEGACY                                                                             CON_ID
-------------------------------------------------------------------------------- ---------------
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.19.0.0.0
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production                         0

1 row selected.

-- 2) Confirm compatible parameter setting
SELECT name, type, value FROM v$parameter WHERE name = 'compatible';

NAME                                                                                        TYPE
-------------------------------------------------------------------------------- ---------------
VALUE
----------------------------------------------------------------------------------------------------
compatible                                                                                     2
12.1.0.2.0

1 row selected.

-- 3) Try to create a table with a name longer than 30 bytes, which is allowed in 12.2 (up to 128 byte names are allowed), but not 12.1
CREATE TABLE test_a_table_name_31_bytes_long (col1 VARCHAR2(5));

CREATE TABLE test_a_table_name_31_bytes_long (col1 VARCHAR2(5))
             *
ERROR at line 1:
ORA-00972: identifier is too long

-- 4) Now try to create a table with a name exactly 30 bytes long, which is allowed in 12.2 and 12.1 (and earlier releases)
CREATE TABLE test_a_table_name_30bytes_long (col1 VARCHAR2(5));

Table created.


The above error would seem to indicate not only does the compatible parameter play a role in the features/options in the version of the database you're using, it effectively *limits* what you can do to only what is allowed in the version of the database you have set the compatible parameter to, unless I'm misinterpreting this test case! Although I didn't include it above, another example that fails for me, is copying data of type LONG over a database link, which again, works in 12.2 but not 12.1.

In addition, here:
https://docs.oracle.com/en/database/oracle/oracle-database/19/upgrd/what-is-oracle-database-compatibility.html#GUID-4711E0D1-9FCF-4F35-85B5-52EBB437C00E

it states "The COMPATIBLE initialization parameter enables or disables Oracle Database features based on release compatibility", which again, sounds like what's happening in the above illustration.

So am I misinterpreting either the above test case results, or your statement? Any clarification would be appreciated. FYI: this is a huge part of the reason why I asked my question, I'm looking to avoid going through the motions of upgrading to Apex 23.1, yet either running into incompatibilities, or, being unable to make use of any of the new features I supposedly upgraded to!

Thanks!

Connor McDonald
November 09, 2023 - 4:53 am UTC

OK... I was a bit flippant with "it doesn't play a role in the features/options" :-)

Its purpose is to allow downgrade, and if you were to use a feature that would prohibit that capacity, then compatible would come into play. The long identifiers is a perfect example of this - its not the long identifier per se thats the issue, its that if you used them, you would no longer be able to downgrade to a pre-long identifier version.

And in the interests of full disclosure...the use of compatible has been (for lack of better term) more "fluid" in recent releases, being used to control things like blockchain, in-memory etc

I'm not a fan of that but we are where we are.

A reader, November 09, 2023 - 6:23 am UTC

Got it! Thanks for the clarification!
Chris Saxon
November 17, 2023 - 11:11 am UTC

You're welcome

Features to COMPATIBLE Matrix

Russell Cannon, October 31, 2024 - 2:14 pm UTC

Is there some documentation somewhere that relates features to specific COMPATIBLE values?

Cheers,
Russ
Connor McDonald
November 01, 2024 - 5:40 am UTC

From

https://mikedietrichde.com/2019/04/17/when-and-how-should-you-change-compatible/

But still, where is the list which features depend exactly on COMPATIBLE?
Unfortunately there is no such list to share. It would be good to have such a list

So sadly no
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

Administration

Need more information on Administration? Check out the Administrators guide for the Oracle Database