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…

Published by Laurent Schneider

Oracle Certified Master

Join the Conversation

1 Comment

  1. 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 comment

Your email address will not be published.