500 Error and ORA-1403 in a Mod_plsql Procedure Trying To Use Owa Package for Authentication (Doc ID 2671978.1)

Last updated on JANUARY 03, 2025

Applies to:

Symptoms

Using ORDS 19.2 version, Migration from OHS. 

When attempting to use an upgraded (from DB 11 to 19) custom mod_pls application
URL : http://<Tomcat_host>:<Port>/ords/<map>/<package>.<procedure>
the following error occurs:

ERROR
-----------------------
500 Internal Server Error in the Browser
and in the Log
<date-time> SEVERE [http-nio-<PORT>-exec-8] . java.sql.SQLException: ORA-01403: no data found
ORA-06512: at "<SCHEMA>.<PROCEDURE>", line 818
ORA-06512: at line 2
  InternalServerException [statusCode=500, reasons=[]]
  at oracle.dbtools.apex.ModApexContext.handleError(ModApexContext.java:288)
  at oracle.dbtools.apex.OWA.execute(OWA.java:206)
  at oracle.dbtools.apex.ModApex.handleRequest(ModApex.java:310)
  at oracle.dbtools.apex.ModApex.doGet(ModApex.java:147)
  at oracle.dbtools.apex.ModApex.service(ModApex.java:110)
  at oracle.dbtools.http.entrypoint.Dispatcher.dispatch(Dispatcher.java:143)
  at oracle.dbtools.http.entrypoint.EntryPoint$FilteredServlet.service(EntryPoint.java:243)
....
 Caused by: java.sql.SQLException: ORA-01403: no data found
ORA-06512: at "<SCHEMA>.<PROCEDURE>", line 818
ORA-06512: at line 2
  at oracle.jdbc.driver.T4CTTIoer11.processError(T4CTTIoer11.java:509)
...
  at
oracle.ucp.jdbc.proxy.oracle$1ucp$1jdbc$1proxy$1oracle$1StatementProxy$2oracle$1jdbc$1internal$1OracleC
allableStatement$$$Proxy.execute(Unknown Source)
  at oracle.dbtools.apex.OWA.execute(OWA.java:161)
  ... 75 more
  Caused by: Error : 1403, Position : 0, Sql = begin
 <procedure>.app;
commit;
  end;, OriginalSql = begin
 <procedure>.app;
commit;
  end;, Error Msg = ORA-01403: no data found
ORA-06512: at "<Schema>.<Procedure>", line 818
ORA-06512: at line 2
....


The issue can be reproduced at will with the following steps:
1. Configure ORDS to run mod_plsql application owned by <schema> db user.
2. Ran the application with ORDS, it displays the menu as expected.
3. When the application needs to authenticate the user it fails with the 500 error and ORA-01403: no data found behind scenes.

Cause

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.