Problem: Example: CREATE OR REPLACE PROCEDURE check_user_status (myusername in VARCHAR2)
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; / |