Oracle 11g Release 2

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

ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS';
CREATE TABLE employee (id, name) AS
  SELECT 1, 'Alice' FROM DUAL UNION ALL
  SELECT 2, 'Betty' FROM DUAL UNION ALL
  SELECT 3, 'Carol' FROM DUAL UNION ALL
  SELECT 4, 'Alice' FROM DUAL;
4 rows affected

Select * 
FROM   employee 
WHERE  name = (
         SELECT name
         FROM   employee
         WHERE  ID = 1
       );


ID
NAME


1
Alice

4
Alice


SELECT id, name
FROM   (
  SELECT e.*,
         COUNT(CASE WHEN id = 1 THEN 1 END) OVER (PARTITION BY name)
           AS count_id_matches
  FROM   employee e
)
WHERE  count_id_matches > 0;


ID
NAME


1
Alice

4
Alice


DECLARE
  name_variable varchar(20);
BEGIN
  SELECT name
  INTO name_variable 
  FROM employee
  WHERE ID = 1;

  FOR r IN (
    SELECT * 
    FROM employee 
    WHERE name = name_variable
  )
  LOOP
    DBMS_OUTPUT.PUT_LINE( r.id || ' ' || r.name );
  END LOOP;
END;
/
1 rows affected

dbms_output:
1 Alice
4 Alice