Home Oracle PL SQL function has no INTO clause, but it actually has a SELECT INTO

Questions

Oracle PL SQL function has no INTO clause, but it actually has a SELECT INTO

April 16, 2022

I have a function inside a package.

The body:

CREATE OR REPLACE PACKAGE BODY pkg_developers AS

     FUNCTION lookup_developer_studio(p_studio_name IN DEVELOPERSTUDIOS.STUDIONAME%type)
            RETURN INTEGER
        IS
            f_developerstudioid INTEGER;
        BEGIN
            SELECT developerstudioid INTO f_developerstudioid
            FROM developerStudios
            WHERE studioName = p_studio_name;
            RETURN (f_developerstudioid);
        END lookup_developer_studio;
END pkg_developers;

The header:

CREATE OR REPLACE PACKAGE pkg_developers
AS

    FUNCTION lookup_developer_studio(p_studio_name IN DEVELOPERSTUDIOS.STUDIONAME%type) RETURN INTEGER;
END pkg_developers;

I’m testing the function like so:

BEGIN
    select pkg_developers.lookup_developer_studio('some name') from dual;
END;

The Error I get:

[2022-04-16 13:06:06] [65000][6550]
[2022-04-16 13:06:06]   ORA-06550: line 6, column 5:
[2022-04-16 13:06:06]   PLS-00428: an INTO clause is expected in this SELECT statement
[2022-04-16 13:06:06] Summary: 1 of 1 statements executed, 1 failed in 15 ms (285 symbols in file)

But as far as I can see, I do have an INTO clause?

When I run the query statically like: SELECT developerstudioid FROM developerStudios WHERE studioName = 'some name'; I do get 1 result which is an integer.

>Solution :

Well, code you used to test it isn’t equal to what you initially posted – this is actually a PL/SQL block which then requires an INTO clause:

SQL> BEGIN
  2      select pkg_developers.lookup_developer_studio('some name') from dual;
  3  END;
  4  /
    select pkg_developers.lookup_developer_studio('some name') from dual;
    *
ERROR at line 2:
ORA-06550: line 2, column 5:
PLS-00428: an INTO clause is expected in this SELECT statement

If it were only SELECT (at SQL level) then yes – you don’t need INTO:

SQL> select * from developerstudios;

STU DEVELOPERSTUDIOID
--- -----------------
MGM               100

SQL>     select pkg_developers.lookup_developer_studio('MGM') from dual;

PKG_DEVELOPERS.LOOKUP_DEVELOPER_STUDIO('MGM')
---------------------------------------------
                                          100

SQL>

If you want PL/SQL, then declare a variable which will store that value:

SQL> SET SERVEROUTPUT ON
SQL>
SQL> DECLARE
  2    l_id developerstudios.developerstudioid%type;
  3  BEGIN
  4      select pkg_developers.lookup_developer_studio('MGM') INTO l_id from dual;
  5      dbms_output.put_line('Result = ' || l_id);
  6  END;
  7  /
Result = 100

PL/SQL procedure successfully completed.

SQL>