使用cx_Oracle切换Oracle PDB时触发ORA-00922错误求助

核心问题 #

cx_Oracle默认不支持在单次cursor.execute()中执行多条独立SQL语句，将ALTER SESSION和SELECT写在同一条execute调用里会触发语法解析错误，导致ORA-00922报错。

解决办法 #

方法1：分两次执行语句 #

先执行切换PDB的会话语句，再执行查询逻辑：

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

# 切换到目标PDB
cursor.execute("ALTER SESSION SET CONTAINER=MYPDB")
# 执行查询语句
cursor.execute("""
    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
""")

# 获取查询结果
results = cursor.fetchall()

方法2：连接时直接指定PDB（更高效） #

无需切换会话，直接在连接DSN中指定目标PDB，省略额外的会话切换步骤：

# 修改DSN格式，示例：//host:port/MYPDB
conn = cx_Oracle.connect(user=self.user, password=self.password, dsn=self.connection_string)
cursor = conn.cursor()

# 直接执行查询
cursor.execute("""
    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
""")

results = cursor.fetchall()

额外说明 #

• 若要强制在单次execute中执行多条语句，可开启特定参数，但这种方式容易引发会话状态异常，不适合ALTER SESSION这类会话级操作。
• 确保执行用户拥有ALTER SESSION权限，以及查询dba_tab_columns、dba_col_comments的权限。

内容的提问来源于stack exchange，提问作者Exe Carballeda