Avoid Exception when there is no data

Question

I am working on a project and I have a database schema written in Oracle DB. I have some issues.

I'll show you my existing code ->

PACKAGE BODY pkg_customer_overview
IS
 PROCEDURE get_invest_agreement_info (
    opassport_no    OUT VARCHAR2,
    onational_id_no OUT VARCHAR2,
  BEGIN
  SELECT MAX(CASE WHEN doc_type=2 THEN doc_number ELSE '' END),
         MAX(CASE WHEN doc_type=1 THEN doc_number ELSE '' END)
         INTO opassport_no,
              onational_id_no
  FROM cusmm_cus_document
  WHERE customer_number=c.customer_number
  GROUP BY customer_number;

Now I want to make sure to run sp even if cusmm_cus_document table is empty.

Answer 1

Just remove the GROUP BY. An aggregation query with no GROUP BY always returns exactly one row. The columns will be NULL:

SELECT MAX(CASE WHEN doc_type = 2 THEN doc_number END),
       MAX(CASE WHEN doc_type = 1 THEN doc_number END)
INTO opassport_no, onational_id_no
FROM cusmm_cus_document c
WHERE c.customer_number = in_customer_number;

Notes:

• customer_number appears to be a parameter but it conflicts with the column name. Give it a different name!
• I removed the ELSE '' in the CASE expressions. '' is NULL anyway, and the default returned value is NULL with no ELSE.
• The result will be NULL if no rows match. If you want different values, use COALESCE().