How to eliminate empty query error in ORACLE?

Question

Oracle

The procedure contains the following query:

AS 
  INSERTED VARCHAR(200);
  ID_ NUMBER;
...
SELECT ID_MATERIAL INTO INSERTED FROM SOURCE WHERE SHORTNAME_MATERIAL=SHORT_NAME;

In the case when the select should return an empty record, oracle gives an error
ORA-01403: no data found
ORA-06512: at "********************", line 11
ORA-06512: at line 12
how to fix? the procedure should work further after this request, and depending on the result (null or non-null) perform other operations.

Answer 1

The simplest way, however, is to handle the no_data_found error.
The reference script is

AS 
  INSERTED VARCHAR(200);
  ID_ NUMBER;
BEGIN
...
BEGIN
SELECT ID_MATERIAL INTO INSERTED FROM SOURCE WHERE SHORTNAME_MATERIAL=SHORT_NAME;
EXCEPTION
WHEN NO_DATA_FOUND THEN NULL ; (или INSERTED := NULL)
END;
...
IF INSERTED IS NULL THEN
...
ELSE
...
END IF ;
...
END <PROCEDURE_NAME> ;

In general, according to the above example, we can say that it is necessary to work on the design of the code (design standard). Names of variables and tables are not customary. Use prefixes VAR_, V_, GV_, LV_ for variables and prefixes TBL_, T_ or suffix _TBL for tables. Instead of VARCHAR, it should be VARCHAR2. Be sure to specify the dimension type for strings - BYTE or CHAR. Table aliases are good practice even in such simple queries. It is impossible to understand the condition SHORTNAME_MATERIAL=SHORT_NAME is a comparison of the fields of a tuple, two scalar internal or external variables, or constants, or a comparison of a field with a variable/constant.

Answer 2

The simplest way is to make this query a cursor and check for the presence or absence of rows there.