如何在SCHEME1中创建调用SCHEME2存储过程与函数的PL/SQL包？

Let's walk through this step by step to get your package working correctly, and clear up your key questions along the way:

1. First: Fix Permissions (Critical!) #

Before SCHEME1 can access any objects in SCHEME2, you need to grant the right privileges. Log in as SCHEME2 and run these commands:

-- Grant execute access to SCHEME2's stored procedure and function
GRANT EXECUTE ON change_pwd TO scheme1;
GRANT EXECUTE ON check_password TO scheme1;

If SCHEME2's change_pwd or check_password relies on tables that SCHEME1 doesn't already have access to, you may also need to grant SELECT/UPDATE permissions on those tables (but usually, stored procedures handle this via definer's rights, so this step is often unnecessary).

2. Rewrite the user_mgt Package to Call SCHEME2's Objects #

Your current package is re-implementing the logic from scratch, but your goal is to delegate to SCHEME2's existing procedures/functions. Here's the corrected version:

Package Specification #

Make sure the parameter signatures match exactly what SCHEME2's objects use (adjust data types/names if SCHEME2's definitions differ):

CREATE OR REPLACE PACKAGE user_mgt AS
  PROCEDURE change_pwd (
    p_username  IN  VARCHAR2,
    p_old_pw    IN  VARCHAR2,
    p_new_pw    IN  VARCHAR2,
    p_success   OUT BOOLEAN
  );

  FUNCTION check_password (
    p_username  IN VARCHAR2,
    p_password  IN VARCHAR2
  ) RETURN BOOLEAN;
END user_mgt;
/

Package Body #

This version directly calls SCHEME2's objects, with optional extra error handling:

CREATE OR REPLACE PACKAGE BODY user_mgt AS
  PROCEDURE change_pwd (
    p_username  IN  VARCHAR2,
    p_old_pw    IN  VARCHAR2,
    p_new_pw    IN  VARCHAR2,
    p_success   OUT BOOLEAN
  ) IS
  BEGIN
    -- Call SCHEME2's change_pwd procedure directly
    scheme2.change_pwd(p_username, p_old_pw, p_new_pw, p_success);
    
    EXCEPTION
      WHEN OTHERS THEN
        p_success := FALSE;
        DBMS_OUTPUT.PUT_LINE('Error calling SCHEME2.change_pwd: ' || SQLERRM);
  END change_pwd;

  FUNCTION check_password (
    p_username  IN VARCHAR2,
    p_password  IN VARCHAR2
  ) RETURN BOOLEAN IS
    v_result BOOLEAN;
  BEGIN
    -- Call SCHEME2's check_password function directly
    v_result := scheme2.check_password(p_username, p_password);
    RETURN v_result;
    
    EXCEPTION
      WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('Error calling SCHEME2.check_password: ' || SQLERRM);
        RETURN FALSE;
  END check_password;
END user_mgt;
/

3. Key Tips for Parameter Alignment #

• Double-check that your package's parameter names, data types, and IN/OUT modes exactly match SCHEME2's change_pwd and check_password. For example, if SCHEME2 uses USERS.USERNAME%TYPE instead of VARCHAR2, adjust your parameters to match that.
• If SCHEME2 uses different parameter names (like p_user instead of p_username), use named notation for clarity: scheme2.change_pwd(p_user => p_username, ...)

4. Do You Need a Separate Connection? #

Nope! Since both schemas live in the same Oracle database, you don't need to set up any new connections. Just use the SCHEME2.object_name syntax once you have the execute permissions we talked about earlier.

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