Problem:

Example:

CREATE OR REPLACE PROCEDURE check_user_status (myusername in VARCHAR2)
AS
BEGIN
   select d.username,d.account_status,d.Lock_date,d.expiry_date,d.created,r.granted_role
   from dba_users d join DBA_ROLE_PRIVS r  on D.USERNAME = R.GRANTEE
   where D.USERNAME= UPPER (myusername)
   AND d.username NOT IN (select distinct owner schema_name from dba_segments);
END;
/
Warning: Procedure created with compilation errors.

SQL> show err

Errors for PROCEDURE CHECK_USER_STATUS:

LINE/COL ERROR

-------- -----------------------------------------------------------------

4/4      PLS-00428: an INTO clause is expected in this SELECT statement

 

Solution:

CREATE OR REPLACE PROCEDURE test_check_user_status (myusername in VARCHAR2)
AS
      v_username             dba_users.username%type;
      v_account_status       dba_users.account_status%type;
      v_lock_date            dba_users.lock_date%type;
      v_expiry_date          dba_users.expiry_date%type;
      v_created              dba_users.created%type;
      v_granted_role         dba_role_privs.granted_role%type;   
BEGIN
   select d.username,d.account_status,d.Lock_date,d.expiry_date,d.created,r.granted_role
   into v_username,v_account_status,v_Lock_date,v_expiry_date,v_created,v_granted_role
   from dba_users d join DBA_ROLE_PRIVS r  on D.USERNAME = R.GRANTEE
   where D.USERNAME= UPPER (myusername)
   AND d.username NOT IN (select distinct owner schema_name from dba_segments);
   DBMS_OUTPUT.put_line ('USERNAME:'||v_username);
   DBMS_OUTPUT.put_line ('ACCOUNT_STATUS:'||v_account_status);
   DBMS_OUTPUT.put_line ('LOCK_DATE:'||v_Lock_date);
   DBMS_OUTPUT.put_line ('EXPIRY_DATE:'||v_expiry_date);
   DBMS_OUTPUT.put_line ('CREATED:'||v_created);
   DBMS_OUTPUT.put_line ('GRANTED_ROLE:'||v_granted_role);
END;
/