Categories
12c 12cR2 18c 19c

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…

By Laurent Schneider

Oracle Certified Master

One reply on “changing container in plsql”

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.