How can I call a procedure in a different package in a different schema from another

Question

I want to call a procedure declared in one schema in one package different from the currently running schema and package

There is another question almost related, but my difference is that the called procedure is in another schema and package, not just another package.

declare 
  l_hub_msg varchar(1000);
  l_query varchar(1000);
begin
  -- Call a procedure in the same schema. Problem is not here 
  l_query := 'pkg_hub.loadFlatTable' ;
  execute immediate 'l_query' into l_hub_msg;

  if l_hub_msg is not null and length(l_hub_portfolio_msg) > 0 then


    -- The following line gives me an PLS-00201: identifier 'PKG_EMAIL_PAGE.SENDMSG' must be declared
    PKG_EMAIL_PAGE.SENDMSG('Message', 'FromUser@mycompany.com','ToUser@mycompany.com','ToUser@mycompany.com'
                           'Content',
                           'someuser@mycompany.com');
  end if;
end;

Think of this procedure as being run under schema SCHEMA1 and PKG_EMAIL_PAGE is a package existing under schema SCHEMA2.

Using SQL Developer menu options, supposedly I granted EXECUTE access to PKG_EMAIL_PAGE.SENDMSG to SCHEMA2. That is, it said it was successful.

I could not get this to work though:

GRANT EXECUTE ON PKG_EMAIL_PAGE.SENDMSG to SC_REPORT_NEW;

because it said

QL Error: ORA-04042: procedure, function, package, or package body does not exist
04042. 00000 -  "procedure, function, package, or package body does not exist"
*Cause:    Attempt to access a procedure, function, package, or package body
           that does not exist.
*Action:   Make sure the name is correct.

Thank you for reading; Woodsman

score 0 · Accepted Answer · answered Aug 13 '19 at 05:51

GRANTs are not given for single methods in packages but for the whole package, and they are not granted to another package but to another user.

schema2 needs to grant:

GRANT EXECUTE ON pkg_email_page TO schema1

schema1 can then call this procedure but needs to specify the schema in the call:

BEGIN
  schema2.pkg_email_page.sendmsg( ... );
END;

How can I call a procedure in a different package in a different schema from another

1 Answers1