Search code examples
pythonpython-3.xoracle-databasecx-oracle

alter session set CONTAINER with CX_Oracle


Now i use cx_oracle with python to execute oracle statements. I have oracle CDB and PDBs. I need to execute this statement from CDB to PDB (MYPDB)

ALTER SESSION SET CONTAINER=MYPDB;
SELECT (SELECT instance_name FROM v$instance) as CDB_NAME, 
(SELECT SYS_CONTEXT('USERENV', 'CON_NAME') FROM dual) as PDB_NAME ,
a.owner ,a.table_name ,a.column_name ,b.comments 
FROM dba_tab_columns a 
LEFT JOIN dba_col_comments b 
ON a.owner = b.owner AND a.table_name = b.table_name AND a.column_name = b.column_name 
WHERE a.OWNER 
LIKE 'USR_%' 
ORDER BY a.owner, a.table_name, a.column_name ASC

It works in sql developer but not in cx_oracle. My script is:

conn = cx_Oracle.connect(user=self.user, password=self.password, dsn=self.connection_string)

cursor = conn.cursor()

cursor.execute("""alter session set CONTAINER=PB24
                    SELECT a.owner, a.table_name, b.comments FROM dba_tables a LEFT JOIN dba_tab_comments b ON a.owner = b.owner AND a.table_name = b.table_name WHERE a.OWNER LIKE 'USR_%' ORDER BY a.owner, a.table_name ASC;
""")

and return ORA-00922: missing or invalid option

Python version: 3.10.14 Oracle version: 18g cx_version: 8 latest


Solution

  • To give a concrete answer that explains the comments on the question:

    Install python-oracledb:

    $ python3 -m pip install oracledb --upgrade
    

    Set the environment variables used in the script below, and then run it:

    import getpass
    import os
    
    import oracledb
    
    un = os.environ.get('PYTHON_USERNAME')
    cs = os.environ.get('PYTHON_CONNECTSTRING')
    pw = getpass.getpass(f'Enter password for {un}@{cs}: ')
    
    with oracledb.connect(user=un, password=pw, dsn=cs) as  connection:
        with connection.cursor() as cursor:
            sql1 = "ALTER SESSION SET CONTAINER=MYPDB"
            cursor.execute(sql1)
            sql2 = """select
                          (
                              select
                                  instance_name
                              from
                                  v$instance
                          ) as cdb_name,
                          (
                              select
                                  sys_context('USERENV', 'CON_NAME')
                              from
                                  dual
                          ) as pdb_name,
                          a.owner,
                          a.table_name,
                          a.column_name,
                          b.comments
                      from
                          dba_tab_columns  a
                          left join dba_col_comments b
                          on a.owner = b.owner
                          and a.table_name = b.table_name
                          and a.column_name = b.column_name
                      where
                          a.owner like 'USR_%'
                      order by
                          a.owner,
                          a.table_name,
                          a.column_name asc"""
    
            for r in cursor.execute(sql2):
                print(r)