Flask调用Oracle报ORA-00911无效字符错误，但SQL在Oracle中可执行

你的问题根源在于SQL语句末尾的**分号;**以及多余的空白字符。Oracle客户端可以自动忽略语句末尾的分号，但oracledb驱动会将分号视为SQL的一部分，从而触发无效字符错误。

修复步骤： #

• 移除SQL字符串末尾的分号;
• 清理SQL末尾的多余空行，避免不必要的空白字符干扰

修改后的代码： #

cur = con.cursor()
user_id = session["user_id"]  # Type int
cur.execute(
    """
    SELECT DISTINCT p.*,
    (CASE 
        WHEN user_id = :1 THEN 'joined'
        ELSE 'not'
    END) AS Result,
    (CASE 
        WHEN pub.request_to_join IS NULL THEN 'Private'
        WHEN pub.request_to_join = '1' THEN 'Public (Request)'
        ELSE 'Public'
    END) AS STATUS
    FROM ipm_project p
    LEFT JOIN ipm_project_public pub ON pub.project_id = p.id
    LEFT JOIN ipm_project_private pri ON pri.project_id = p.id
    JOIN ipm_project_team_junction pt ON p.id = pt.project_id
    JOIN ipm_project_team i ON i.id = pt.project_team_id
    ORDER BY p.id ASC
    """, (user_id,)
)

columns = [col[0] for col in cur.description]
cur.rowfactory = lambda *args: dict(zip(columns, args))
all_projects = cur.fetchall()

补充说明： #

在使用oracledb执行SQL时，不需要像在Oracle客户端那样添加语句结束符;，驱动会直接处理纯SQL内容，多余的分号或空白字符会被识别为无效语法。

内容的提问来源于stack exchange，提问作者muhammad aqil