Oracle 12c：创建查询dba_users用户名列表的存储过程执行失败

Hey there! Let's troubleshoot why your stored procedure to pull usernames from dba_users is failing in Oracle 12c. I'll start with a couple of working procedure examples, then break down the most common issues that might be causing your execution errors.

Working Stored Procedure Examples #

First, let's cover two common approaches: one that prints usernames directly, and another that returns a result set (useful for app integrations).

1. Procedure to Print Usernames via DBMS_OUTPUT #

CREATE OR REPLACE PROCEDURE get_all_dba_users
IS
    CURSOR user_cursor IS
        SELECT username FROM dba_users;
    v_username dba_users.username%TYPE;
BEGIN
    OPEN user_cursor;
    LOOP
        FETCH user_cursor INTO v_username;
        EXIT WHEN user_cursor%NOTFOUND;
        DBMS_OUTPUT.PUT_LINE('Username: ' || v_username);
    END LOOP;
    CLOSE user_cursor;
EXCEPTION
    WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('Error message: ' || SQLERRM);
        RAISE; -- Re-throw the error to retain stack trace
END;
/

2. Procedure to Return a Result Set with REF CURSOR #

If you need to pass the user list to an application or another PL/SQL block, use a SYS_REFCURSOR output parameter:

CREATE OR REPLACE PROCEDURE get_all_dba_users(p_user_list OUT SYS_REFCURSOR)
IS
BEGIN
    OPEN p_user_list FOR
        SELECT username FROM dba_users ORDER BY username;
EXCEPTION
    WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('Error message: ' || SQLERRM);
        RAISE;
END;
/

Common Execution Failure Causes & Fixes #

Let's go through the most likely issues that could be breaking your procedure:

1. Insufficient Privileges #

Your database user needs direct SELECT access to DBA_USERS (roles like DBA won't work for stored procedures, since role privileges aren't enabled during execution). Run this as a privileged user (like SYS or SYSTEM):

GRANT SELECT ON dba_users TO your_database_user;

2. DBMS_OUTPUT Isn't Enabled #

If your procedure uses DBMS_OUTPUT.PUT_LINE to print results, you need to enable output in your SQL client first:

SET SERVEROUTPUT ON;

Then call the procedure like this:

-- Option 1: Short syntax
EXEC get_all_dba_users;

-- Option 2: Full PL/SQL block
BEGIN
    get_all_dba_users;
END;
/

3. Incorrectly Calling the REF CURSOR Procedure #

If you're using the REF CURSOR version, you can't just run EXEC and expect results. You need to fetch the cursor in a PL/SQL block:

DECLARE
    v_user_cursor SYS_REFCURSOR;
    v_username VARCHAR2(30); -- Matches dba_users.username data type
BEGIN
    get_all_dba_users(v_user_cursor);
    LOOP
        FETCH v_user_cursor INTO v_username;
        EXIT WHEN v_user_cursor%NOTFOUND;
        DBMS_OUTPUT.PUT_LINE('Username: ' || v_username);
    END LOOP;
    CLOSE v_user_cursor;
END;
/

In SQL Developer, you can also right-click the procedure and select "Run" to auto-generate a test block with the cursor output.

4. Invalid Procedure Status #

If your procedure compiled with hidden errors, it will fail when executed. Check its status:

SELECT object_name, status FROM user_objects WHERE object_name = 'GET_ALL_DBA_USERS';

If status is INVALID, recompile it and check for errors:

ALTER PROCEDURE get_all_dba_users COMPILE;
SHOW ERRORS PROCEDURE get_all_dba_users;

Fix any syntax or reference errors shown, then recompile.

Final Checks #

Double-check that:

• You're using the correct user to execute the procedure (the one you granted SELECT access to)
• Your procedure doesn't have typos (e.g., misspelling dba_users or username)
• You're handling exceptions properly to catch error messages

内容的提问来源于stack exchange，提问作者Evghen Tester