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

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;

/