Dynamic Switching with SQL

Recently i wanted to run a script which would automatically switch to a PDB without too much effort to run show pdbs, alter session set container like commands, i found a Smarter way to use SQL to dynamically switch to a PDB without any effort , here is how to do it, make sure to pack this whole stuff in a .sql file and run it, however i am showing all steps 1 by 1 properly

SQLcl: Release 24.4 Production on Thu May 29 00:30:48 2025



Copyright (c) 1982, 2025, Oracle.  All rights reserved.



Last Successful login time: Thu May 29 2025 00:30:49 +05:30



Connected to:

Oracle Database 23ai Enterprise Edition Release 23.0.0.0.0 - for Oracle Cloud and Engineered Systems

Version 23.8.0.25.05



SQL>

SQL>

SQL> -- Show current container

SQL> PROMPT === Current container:

=== Current container:

SQL> SHOW CON_NAME;

CON_NAME

------------------------------

RE2D3BGG7YWWTP7_PYDB

SQL>

SQL> -- Find the first open PDB

SQL> COLUMN pdb_name NEW_VALUE pdb_var

SQL> SELECT name AS pdb_name

  2  FROM   v$pdbs

  3  WHERE  open_mode = 'READ WRITE'

  4* AND    rownum = 1;



PDB_NAME

_______________________

RE2D3BGG7YWWTP7_PYDB



SQL>

SQL> -- Switch to that PDB

SQL> PROMPT === Switching to PDB: &pdb_var

=== Switching to PDB: RE2D3BGG7YWWTP7_PYDB

SQL> ALTER SESSION SET CONTAINER = &pdb_var;

old:ALTER SESSION SET CONTAINER = &pdb_var

new:ALTER SESSION SET CONTAINER = RE2D3BGG7YWWTP7_PYDB



Session altered.



SQL>

SQL> -- Confirm switch

SQL> PROMPT === Current container after switch:

=== Current container after switch:

SQL> SHOW CON_NAME;

CON_NAME

------------------------------

RE2D3BGG7YWWTP7_PYDB

SQL>

SQL> -- Sample SQL in the PDB

SQL> SELECT name FROM v$database;



NAME

___________

FCEG3XDN



SQL> show con_name

CON_NAME

------------------------------

RE2D3BGG7YWWTP7_PYDB

Now lets try to pack the commands in a script and run script

   SQLcl: Release 24.4 Production on Thu May 29 00:37:51 2025

Copyright (c) 1982, 2025, Oracle.  All rights reserved.

Last Successful login time: Thu May 29 2025 00:37:53 +05:30

Connected to:
Oracle Database 23ai Enterprise Edition Release 23.0.0.0.0 - for Oracle Cloud and Engineered Systems
Version 23.8.0.25.05

SQL> set echo on
SQL> @s
SQL>
SQL> -- Show current container
SQL> PROMPT === Current container:
=== Current container:
SQL> SHOW CON_NAME;
CON_NAME
------------------------------
RE2D3BGG7YWWTP7_PYDB
SQL>
SQL> -- Find the first open PDB
SQL> COLUMN pdb_name NEW_VALUE pdb_var
SQL> SELECT name AS pdb_name
  2  FROM   v$pdbs
  3  WHERE  open_mode = 'READ WRITE'
  4  AND    rownum = 1;

PDB_NAME
_______________________
RE2D3BGG7YWWTP7_PYDB

SQL>
SQL> -- Switch to that PDB
SQL> PROMPT === Switching to PDB: &pdb_var
=== Switching to PDB: RE2D3BGG7YWWTP7_PYDB
SQL> ALTER SESSION SET CONTAINER = &pdb_var;
old:ALTER SESSION SET CONTAINER = &pdb_var
new:ALTER SESSION SET CONTAINER = RE2D3BGG7YWWTP7_PYDB

Session altered.

SQL>
SQL> -- Confirm switch
SQL> PROMPT === Current container after switch:
=== Current container after switch:
SQL> SHOW CON_NAME;
CON_NAME
------------------------------
RE2D3BGG7YWWTP7_PYDB
SQL>
SQL> -- Sample SQL in the PDB
SQL> SELECT name FROM v$database;

NAME
___________
FCEG3XDN

SQL>
SQL>
SQL> show pdbs

   CON_ID CON_NAME                OPEN MODE     RESTRICTED
_________ _______________________ _____________ _____________
      244 RE2D3BGG7YWWTP7_PYDB    READ WRITE    NO
      

There you go, enjoy