Oracle 11g Release 2

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE plate(cod_plate INT, name VARCHAR2(20))
CREATE TABLE detail_ped(plate INT)
INSERT INTO plate VALUES(1, 'chicken')
1 rows affected

INSERT INTO plate VALUES(1, 'chicken')
1 rows affected

INSERT INTO plate VALUES(1, 'chicken')
1 rows affected

INSERT INTO detail_ped VALUES(1)
1 rows affected

create or replace FUNCTION NUMBER_PLATES (p_name VARCHAR2)
RETURN INT
IS
    num_plates INT;
BEGIN

    SELECT count(*) INTO num_plates
    FROM plate p
    JOIN  detail_ped dt
      ON dt.plate = p.cod_plate
    WHERE p.name = p_name;

    RETURN (num_plates);

END NUMBER_PLATES;
/
DECLARE a INT;
BEGIN
    a := NUMBER_PLATES('chicken');
    DBMS_OUTPUT.PUT_LINE(a);
END;
/
1 rows affected

dbms_output:
3

SELECT count(*)
    FROM plate p, detail_ped dt
    WHERE dt.plate = p.cod_plate AND p.name = 'chicken';


COUNT(*)


3