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;
/