Oracle APEX测试报表查询遇ORA-29273错误排查求助

问题场景 #

我有一个Oracle APEX应用，在Shared Components/Report Queries中创建并测试报表查询，语句为Select * from LtrTBL;，测试时触发错误：ORA-29273: HTTP request failed。

已执行操作 #

已成功运行以下PL/SQL代码，将APEX应用所有者账号（APEX_200200）添加至网络ACL：

DECLARE
ACL_PATH VARCHAR2(4000);
BEGIN
SELECT ACL INTO ACL_PATH FROM DBA_NETWORK_ACLS
WHERE HOST = '*' AND LOWER_PORT IS NULL AND UPPER_PORT IS NULL;
IF DBMS_NETWORK_ACL_ADMIN.CHECK_PRIVILEGE(ACL_PATH, 'APEX_200200',
'connect') IS NULL THEN
DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE(ACL_PATH,
'APEX_200200', TRUE, 'connect');
END IF;
EXCEPTION
-- 当未给'*'分配ACL时触发
WHEN NO_DATA_FOUND THEN
DBMS_NETWORK_ACL_ADMIN.CREATE_ACL('power_users.xml',
'允许超级用户连接任意地址的ACL',
'APEX_200200', TRUE, 'connect');
DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL('power_users.xml','*');
END;
/

相关配置文件 #

listener.ora #

# listener.ora 网络配置文件: C:\Oracle193\NETWORK\ADMIN\listener.ora
# 由Oracle配置工具生成
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = CLRExtProc)
(ORACLE_HOME = C:\Oracle193)
(PROGRAM = extproc)
(ENVS = "EXTPROC_DLLS=ONLY:C:\Oracle193\bin\oraclr19.dll")
)
)
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
)
)

tnsnames.ora #

# tnsnames.ora 网络配置文件: C:\Oracle193\NETWORK\ADMIN\tnsnames.ora
# 由Oracle配置工具生成
LISTENER_ORCL =
(ADDRESS = (PROTOCOL = TCP)(HOST = DESKTOP-D62QNTA)(PORT = 1521))
ORACLR_CONNECTION_DATA =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
)
(CONNECT_DATA =
(SID = CLRExtProc)
(PRESENTATION = RO)
)
)
ORCL =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = DESKTOP-D62QNTA)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcl)
)
)

sqlnet.ora #

# sqlnet.ora 网络配置文件: C:\Oracle193\NETWORK\ADMIN\sqlnet.ora
# 由Oracle配置工具生成。
# 此文件实际由netca生成，但如果客户选择“仅安装软件”，该文件将不存在，
# 若无原生身份验证，他们将无法在NT上连接数据库。
SQLNET.AUTHENTICATION_SERVICES= (NTS)
NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT)

TNSPING结果 #

C:\Users\Administrator>TNSPING LISTENER_ORCL
TNS Ping Utility for 64-bit Windows: Version 19.0.0.0.0 - Production on 11-MAY-2024 01:32:45
Copyright (c) 1997, 2019, Oracle. 保留所有权利。
使用的参数文件:
C:\Oracle193\network\admin\sqlnet.ora
使用TNSNAMES适配器解析别名
尝试连接 (ADDRESS = (PROTOCOL = TCP)(HOST = DESKTOP-D62QNTA)(PORT = 1521))
成功 (10 毫秒)

问题排查方向 #

• 验证ACL权限生效状态：运行以下查询确认APEX_200200的网络权限：

  SELECT host, lower_port, upper_port, privilege, status
  FROM dba_network_acl_privileges
  WHERE principal = 'APEX_200200';
  

  需确保privilege为connect且status为GRANTED。

• 检查APEX数据库连接配置：在APEX的Shared Components > Database Connections中，确认应用使用的连接字符串指向正确的服务名（如ORCL），避免使用可能导致解析异常的别名。

• 修正Listener绑定地址：当前Listener绑定localhost，但TNSNames使用主机名DESKTOP-D62QNTA。修改listener.ora中的HOST为DESKTOP-D62QNTA或0.0.0.0，重启Listener后重试：

  LISTENER =
  (DESCRIPTION_LIST =
  (DESCRIPTION =
  (ADDRESS = (PROTOCOL = TCP)(HOST = DESKTOP-D62QNTA)(PORT = 1521))
  (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
  )
  )
  

• 确认表权限：确保APEX_200200账号对LtrTBL有SELECT权限，若表属于其他用户，需执行对应授权：

  -- 若表属于当前用户
  GRANT SELECT ON LtrTBL TO APEX_200200;
  -- 若表属于其他用户
  GRANT SELECT ON [用户名].LtrTBL TO APEX_200200;
  

• 检查版本兼容性补丁：APEX 20.2与Oracle 19c理论兼容，但可尝试应用对应版本的APEX官方补丁，排查是否存在已知网络连接bug。

内容的提问来源于stack exchange，提问作者Es Kaboodvand