changing container in plsql

One of the today’s challenge, since Oracle 12c deprecated non-cdb, is to make the dba scripts CDB-aware.

If you are lucky enough to have no 11g around, you can mostly replace DBA_* by CDB_*

OLD:

SQL> select count(*) from dba_users;
  COUNT(*)
----------
       121

NEW: non-cdb

SQL> select con_id, count(*) from cdb_users group by con_id;

CON_ID   COUNT(*)
------ ----------
     0        121

NEW: single-tenant

SQL> select con_id, count(*) from cdb_users group by con_id;
    CON_ID   COUNT(*)
---------- ----------
         1         23
         3         39

As mentioned in a white paper :
The set container privilege has certain restrictions in PL/SQL
multitenant-security-concepts-12c-2402462.pdf

Sometimes the certain restrictions will puzzle you

SQL> set feed off serverout on
SQL> exec dbms_output.put_line('root')
root
SQL> alter session set container=dora1;
SQL> sho serverout
serveroutput ON SIZE UNLIMITED FORMAT WORD_WRAPPED
SQL> exec dbms_output.put_line('dora1');
SQL> -- NO OUTPUT WTF !!!!
SQL> set serveroutput ON 
SQL> exec dbms_output.put_line('dora1');
dora1
SQL> 

The security model prevents you from using alter session (with execute immediate or like in the previous example) to execute plsql.

Now you know…

1 Comment

  • Hi Laurent,

    I just want to mention two workarounds that you are aware of that allow to see dbms_output after switching a container:

    1. use a custom script to switch a container that could call in a sequence:
    alter session set container=dora1;
    a custom login.sql or any script that could call set serverout on in SQL*Plus.
    It probably makes sense to do in such a script something to restore the whole SQL*Plus state, so that SET SERVEROUTPUT ON will be reexecuted again:
    store set sqlplus_settings.sql replace
    alter session set container=dora1;
    @sqlplus_settings.sql

    I have seen that people called such scripts as setcon.sql or goto.sql and called them when they wanted to switch a container.

    2. an AFTER SET CONTAINER trigger ( I am not fond of that approach):

    CDB$ROOT> set serverout on
    CDB$ROOT> exec dbms_output.put_line(‘root1’)
    root1

    PL/SQL procedure successfully completed.

    CDB$ROOT> alter session set container=pdb;

    Session altered.

    PDB> exec dbms_output.put_line(‘pdb1’)

    — NO OUTPUT —
    PL/SQL procedure successfully completed.

    PDB> sho serverout
    serveroutput ON SIZE UNLIMITED FORMAT WORD_WRAPPED
    PDB> create or replace trigger set_container
    after set container on database
    declare
    begin
    dbms_output.enable();
    dbms_output.put_line(‘set_container trigger fired!’);
    end;
    / 2 3 4 5 6 7 8

    Trigger created.

    PDB> conn / as sysdba
    Connected.
    CDB$ROOT> sho serverout
    serveroutput OFF
    CDB$ROOT> set serverout on
    CDB$ROOT> exec dbms_output.put_line(‘root1’)
    root1

    PL/SQL procedure successfully completed.

    CDB$ROOT> alter session set container=pdb;

    Session altered.

    — NOTICE the line after the query output.
    — SQL*Plus seems to call DBMS_OUTPUT.GET_LINES only after certain statements and ALTER SESSION is not one of them
    — Now we are able to see any output without using SET SERVEROUTPUT ON explicitly as the output state of the package DBMS_OUTPUT is in line with what SQL*Plus “thinks” it should be

    PDB> select * from dual;

    D

    X

    set_container trigger fired!
    PDB> exec dbms_output.put_line(‘pdb1’)
    pdb1

    PL/SQL procedure successfully completed.

Leave a Reply

Your email address will not be published.