Error: Cannot invoke a PL/SQL function. Encountered the symbol "SELECT"-CodePudding

I trying to make a function with a parameter in PL/SQL but getting following error: PLS-00103: Encountered the symbol "SELECT". Here is my code:

CREATE OR REPLACE FUNCTION select_by_id(id_in IN NUMBER) RETURN clients%ROWTYPE
AS
  result clients%ROWTYPE;
BEGIN
  SELECT * INTO result FROM clients WHERE client_id = id_in;
  RETURN result;
END;

SELECT select_by_id(2) FROM DUAL;

What is the cause of the error, and how can the problem be fixed?

CodePudding user response：

Your function works (for a certain degree of works) if you terminate it with / on a newline.

If you have the table:

CREATE TABLE clients (client_id, other) AS
SELECT 2, 'XYZ' FROM DUAL;

Then the function (terminated by a / on a newline):

CREATE OR REPLACE FUNCTION select_by_id(
  id_in IN NUMBER
) RETURN clients%ROWTYPE
AS
  result clients%ROWTYPE;
BEGIN
  SELECT * INTO result FROM clients WHERE client_id = id_in;
  RETURN result;
END;
/

Compiles and can be called from PL/SQL:

DECLARE
  client CLIENTS%ROWTYPE := select_by_id(2);
BEGIN
  DBMS_OUTPUT.PUT_LINE(client.client_id || ', ' || client.other);
END;
/

and outputs:

2, XYZ

However, a %ROWTYPE is a PL/SQL record type and cannot be used in an SQL statement so:

SELECT select_by_id(2) FROM DUAL;

Will not work because the data type is invalid in the SQL scope.

fiddle