How do I detect Oracle XE?

Question

We use local XEs for rapid development, but deploy on "real" Oracles. Some of our scripts use partitioning and bitmap indexes and we'd like to skip those parts for XE, as they're not supported, but without having a separate set of scripts. Is there any SQL we can use to detect that a script is being run on XE so that we can adjust the statements?

Answer 1

One of the following will likely give you what you need:

select * from v$version 

select * from product_component_version

XE will likely show up here, and you can check for the presense of that string.

Shamelessly ripped off from here

Answer 2

I've run into the same problem. My solution has been to embed these version specific features in anonymous blocks and test for specific error codes. Something like this :

BEGIN
    EXECUTE IMMEDIATE 'CREATE BITMAP INDEX myIndex ON myTable';
EXCEPTION
    WHEN OTHERS THEN
        IF SQLCODE=-439 THEN -- bitmap index not supported
            EXECUTE IMMEDIATE 'CREATE INDEX myIndex ON myTable';
        END IF;
END;

Not exactly what you asked for, but it's been working very well for me.