I'm building an installer for an application. The user gets to select a datasource they have configured and nominate what type of database it is. I want to confirm that the database type is indeed Oracle, and if possible, what version of Oracle they are running by sending a SQL statement to the datasource.
SQL> SELECT version FROM v$instance;
VERSION
-----------------
11.2.0.3.0
Here's a simple function:
CREATE FUNCTION fn_which_edition
RETURN VARCHAR2
IS
/*
Purpose: determine which database edition
MODIFICATION HISTORY
Person Date Comments
--------- ------ -------------------------------------------
dcox 6/6/2013 Initial Build
*/
-- Banner
CURSOR c_get_banner
IS
SELECT banner
FROM v$version
WHERE UPPER(banner) LIKE UPPER('Oracle Database%');
vrec_banner c_get_banner%ROWTYPE; -- row record
v_database VARCHAR2(32767); --
BEGIN
-- Get banner to get edition
OPEN c_get_banner;
FETCH c_get_banner INTO vrec_banner;
CLOSE c_get_banner;
-- Check for Database type
IF INSTR( UPPER(vrec_banner.banner), 'EXPRESS') > 0
THEN
v_database := 'EXPRESS';
ELSIF INSTR( UPPER(vrec_banner.banner), 'STANDARD') > 0
THEN
v_database := 'STANDARD';
ELSIF INSTR( UPPER(vrec_banner.banner), 'PERSONAL') > 0
THEN
v_database := 'PERSONAL';
ELSIF INSTR( UPPER(vrec_banner.banner), 'ENTERPRISE') > 0
THEN
v_database := 'ENTERPRISE';
ELSE
v_database := 'UNKNOWN';
END IF;
RETURN v_database;
EXCEPTION
WHEN OTHERS
THEN
RETURN 'ERROR:' || SQLERRM(SQLCODE);
END fn_which_edition; -- function fn_which_edition
/
Done.