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

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