Just a quick tip for this post today. I had a question come in asking how to detect whether a database was running as a container database. My quick response was to use SYS_CONTEXT(‘USERENV’,’CON_ID’) for which the values will be
- 0 – this database is not configured as a container database at all (tisk tisk )
- 1- this database is a container database and you are currently in the root
- >1 – this database is a container database and you are currently in a pluggable
However, the poster then responded with an additional complication – they were building installation scripts for their application which customers would run, and that some of those customers would be running version 11 or ever lower! (The list of problems with lingering on those old versions is a topic for another blog post!). But if you try use SYS_CONTEXT(‘USERENV’,’CON_ID’) before version 12 you’ll get the error “ORA-02003: invalid USERENV parameter”.
Rather than resort to unwieldy dynamic SQL, here’s a solution to that dilemma taking advantage of a feature that people still don’t use much even though its very useful – PL/SQL conditional compilation and if you click on that link, you’ll see that its been around for nearly 2 decades!
SQL> set serverout on SQL> declare 2 has_container varchar2(1); 3 in_container varchar2(1); 4 begin 5 $IF DBMS_DB_VERSION.VER_LE_11_2 6 $THEN 7 has_container := 'N'; 8 in_container := 'N'; 9 $ELSE 10 has_container := case when to_number(sys_context('USERENV','CON_ID')) = 0 then 'N' else 'Y' end; 11 in_container := case when to_number(sys_context('USERENV','CON_ID')) > 1 then 'Y' else 'N' end; 12 if has_container = 'Y' and in_container = 'N' then 13 for i in ( select name from v$pdbs ) 14 loop 15 dbms_output.put_line(i.name); 16 end loop; 17 end if; 18 $END 19 dbms_output.put_line('has_container='||has_container); 20 dbms_output.put_line('in_container='||in_container); 21 22 end; 23 / PDB$SEED PDB1 PDB2 has_container=Y in_container=N PL/SQL procedure successfully completed.
No dynamic SQL needed and we even get to list the pluggables if we are in the root container.
TL;DR: When it comes to writing cross-version database code, conditional compilation is your friend.