IBM Support

SELECT-INTO statement returns SQLCODE -438 instead of +100 when resultset is empty

Troubleshooting


Problem

SELECT-INTO statement in a Store Procedure returns SQLCODE -438 instead of +100 when resultset is empty

Symptom

This problem only happens when the database is created with Oracle compatibility. It usually happens in a Store Procedure, when a SELECT-INTO statement returns empty result, SQLCODE would be set to -438:
------------------------------------------------------------
BEGIN
SELECT COL2
INTO VCOL1
FROM TEST
WHERE COL1=100;--
EXCEPTION
WHEN NO_DATA_FOUND THEN
P_SQL_CODE := TO_CHAR(SQLCODE);--
END;--
------------------------------------------------------------

In above simple code, when the SELECT-INTO statement returns empty result, SQLCODE is set to -438. So, the user may receive following error when call the procedure:

SQL0438N Application raised error or warning with diagnostic text: "".
SQLSTATE=45000

[{"Product":{"code":"SSEPGG","label":"Db2 for Linux, UNIX and Windows"},"Business Unit":{"code":"BU058","label":"IBM Infrastructure w\/TPS"},"Component":"Routines (SP \u0026 UDF) - SQL","Platform":[{"code":"PF002","label":"AIX"},{"code":"PF010","label":"HP-UX"},{"code":"PF016","label":"Linux"},{"code":"PF033","label":"Windows"}],"Version":"9.7;10.1;10.5","Edition":"","Line of Business":{"code":"LOB10","label":"Data and AI"}}]

Log InLog in to view more of this document

This document has the abstract of a technical article that is available to authorized users once you have logged on. Please use Log in button above to access the full document. After log in, if you do not have the right authorization for this document, there will be instructions on what to do next.

Document Information

Modified date:
01 May 2025

UID

swg21689836

Page Feedback