sqloracleplsqlprocedures

Calling an Oracle procedure within a package from a different schema?

I've created the following package with 3 procedures:

CREATE OR REPLACE PACKAGE PQ_PaqueteIntegrantes
AS 
PROCEDURE INTEG_INSERCIONES(paIdIntegrante IN CreadorTablas.INTEGRANTES.ID_INTEGRANTE%TYPE 
                           ,paNombre IN CreadorTablas.INTEGRANTES.NOMBRE%TYPE
                           ,paApellidoPaterno IN CreadorTablas.INTEGRANTES.APELLIDO_PATERNO%TYPE);

PROCEDURE INTEG_MODIFICACIONES(paIdIntegrante IN OUT CreadorTablas.INTEGRANTES.ID_INTEGRANTE%TYPE 
                              ,paNombre IN OUT CreadorTablas.INTEGRANTES.NOMBRE%TYPE
                         ,paApellidoPaterno IN OUT CreadorTablas.INTEGRANTES.APELLIDO_PATERNO%TYPE);

PROCEDURE INTEG_ELIMINCACIONES( 
               paIdIntegrante    IN OE.EJEMPLO_TRANSAC_CLASE.CUSTOMER_ID%TYPE
                                ,paMjeDescError OUT VARCHAR2
                                ,paCodeError    OUT NUMBER);             
END PQ_PaqueteIntegrantes;

I created those procedures with a user called Admin_proyectos. The first procedures makes "Inserts", the second one "Updates", and the last one "Deletes", all of them working on a table called Integrantes, that table comes from another user called CreadorTablas. My intention is to create another user called Admin, who will have the responsibility to do those things, using the procedures from this package, of course. I've tried doing an PL/SQL block, but it didn't work, neither with an EXEC.

Solution 
  • GRANT EXECUTE ON ADMIN_PROYECTOS.PQ_PaqueteIntegrantes TO Admin

    Then, you can call the procedures in this package with Admin user as

    BEGIN
ADMIN_PROYECTOS.PQ_PaqueteIntegrantes.INTEG_INSERCIONES(paIdIntegrante, paNombre, paNombre);
END;