Why is INTO needed in this PL/SQL function if the variable doesn't get used?

Question

I have an Oracle 18c PL/SQL function that I can use to find problem values in a user-defined spatial type called SDE.ST_GEOMETRY.

Find row with problem shape (SDE.ST_GEOMETRY spatial type)

with function check_shape(anno_shape sde.st_geometry, boundary_shape sde.st_geometry) return varchar2 
is
    v_test_result varchar2(10);                       --<--look here
begin
    select
        sde.st_intersects (boundary_shape, anno_shape)
    into                                              --<--look here
        v_test_result                                 --<--look here
    from
        dual;
    return 'no error';
exception
    when others then
        return 'error';
end;

select 
    anno.objectid, 
    anno.shape as anno_shape,
    check_shape(anno.shape, boundary.shape) as check_shape
from 
    city.boundary boundary
cross join     
    infrastr.gcsm_hc_anno anno
where 
    check_shape(anno.shape, boundary.shape) = 'error'

I pieced together that function from sample scripts online and it works as expected. With that said, I want to make sure the code is as simple and correct as possible.

To my untrained eye, the into and v_test_result seem unnecessary; they don't seem to do anything. The function selects a value into the variable, but the variable doesn't appear to get used by the return.

My intuition is to remove these lines:

v_test_result varchar2(10);
...
into     
    v_test_result

But when I remove those lines, I get an error:

with function check_shape(anno_shape sde.st_geometry, boundary_shape sde.st_geometry) return varchar2 
is
--    v_test_result varchar2(10);                       --<--look here
begin
    select
        sde.st_intersects (boundary_shape, anno_shape)
--    into                                              --<--look here
--        v_test_result                                 --<--look here
    from
        dual;
    return 'no error';
exception
    when others then
        return 'error';
end;

select 
    anno.objectid, 
    anno.shape as anno_shape,
    check_shape(anno.shape, boundary.shape) as check_shape
from 
    city.boundary boundary
cross join     
    infrastr.gcsm_hc_anno anno
where 
    check_shape(anno.shape, boundary.shape) = 'error'

ORA-06553: PLS-428: an INTO clause is expected in this SELECT statement
06553. 00000 -  "PLS-%s: %s"

Why is the into needed in this function? Is there a way to simplify the function by removing it?

Answer 1

Why is the into needed in this function?

Because the PL/SQL language requires that a SELECT statement always has either an INTO or a BULK COLLECT INTO clause.

From the Database PL/SQL Language Reference, the syntax for a SELECT statement is:

select_into_statement ::=

From which you can see that either an INTO or a BULK COLLECT INTO clause is mandatory.

If you do not have either of those clauses then your code is syntactically invalid and an exception will be raised.

Is there a way to simplify the function by removing it?

If you are executing the query in the PL/SQL scope then, no, it cannot be removed.

The only way to not need an INTO (or BULK COLLECT INTO) statement is to execute the SELECT in an SQL scope - which would require using something like EXECUTE IMMEDIATE:

WITH function check_shape(
  anno_shape     infrastr.gcsm_hc_anno.shape%TYPE,
  boundary_shape city.boundary.shape%TYPE
) return varchar2 
is
begin
  EXECUTE IMMEDIATE 'select sde.st_intersects (:1, :2) from dual'
    USING boundary_shape, anno_shape;
  RETURN 'no error';
EXCEPTION
  WHEN OTHERS THEN
    RETURN 'error';
END;
select anno.objectid, 
       anno.shape as anno_shape,
       check_shape(anno.shape, boundary.shape) as check_shape
from   city.boundary boundary
       inner join infrastr.gcsm_hc_anno anno
       on check_shape(anno.shape, boundary.shape) = 'error';