Some mistake in calling one procedure from another

Question

Due to a requirement I have to call one stored procedure from another.

The problem seems to be somewhere around the output parameter out_result_size, which I am using. When I test main_func it works fine but when I test synonym_proc it says

synonym_proc is invalid

I have to, ultimately, call synonym_proc from Java using JPA's @NamedNativeQuery

CREATE OR REPLACE PROCEDURE synonym_proc (
    result_cursor      OUT SYS_REFCURSOR,
    in_cp_id           IN NUMBER,
    in_cp_name         IN VARCHAR2 := NULL,
    in_country_name    IN VARCHAR2 := NULL,
    in_industry_name   IN VARCHAR2 := NULL,
    in_max_result_size IN NUMBER
) AS
    out_result_size NUMBER;
BEGIN
   result_cursor := someSchema.somePackage.main_func(in_cp_id,
              in_cp_name,
              in_country_name,
              in_industry_name,
              in_max_result_size,
              out_result_size);
END;

UPDATE: Apologies for not mentioning the changes in the question which I didn't notice earlier. The main_func is a function (not procedure), which returns a cursor and resides inside some package which is in some schema. When I compile I get the following compilation errors:

Error: PLS-00201: identifier 'SOMESCHEMA.SOMEPACKAGE' must be declared.. statement ignored.

Update 2

SomeSchema definition

CREATE OR REPLACE PACKAGE someSchema."SomePackage"
is
...
function mainFunc
(
    in_cp_id                in gem.tcp_real_profile_main_approved.cp%type
    , in_cp_name            in gem.tcp_real_profile_main_approved.name%type
    , in_country_name       in gem.tcp_real_profile_main_approved.country_name%type
    , in_industry_name      in gem.tcp_real_profile_main_approved.industry_name%type
    , in_max_result_size    in number
    , out_result_size       out number
)
return search_result_type_cursor;

ADDED

type search_result_type_cursor
 is ref cursor
 return search_type;

And yes the datatypes are correct since I described the function (main_func) and cross verified the datatypes.

Answer 1

Based on the comments, particularly that you can describe and test the procedure in the other schema, it looks like this may be a permission issue. If your privileges on the objects in the other schema are granted via a role then you will be able to execute the package procedure directly, but roles aren't recognised inside named blocks. If this is the issue then you will have to get execute permission on someSchema.somePackage granted directly to the user that is creating the stored procedure.

To demonstrate the issue, I can create a package in my SCOTT schema and grant execute on it to a role, and grant that role to a user - both of which I've created for the test. As SYS:

create role scott_tmp_role;
grant connect to someuser identified by <password>;
grant scott_tmp_role to someuser;
grant create procedure to someuser;

As SCOTT:

create package p42 as
    procedure proc;
end p42;
/

Package created.

create package body p42 as
    procedure proc is
    begin
        null;
    end proc;
end p42;
/

Package body created.

grant execute on p42 to scott_tmp_role;

Grant succeeded.

As my new SOMEUSER, verify the roles I have:

select * from session_roles;

ROLE
------------------------------
CONNECT
SCOTT_TMP_ROLE

I can execute the procedure in an anonymous block:

begin
    scott.p42.proc;
end;
/

PL/SQL procedure successfully completed.

... but not in a named block:

create or replace procedure sp42 as
begin
    scott.p42.proc;
end;
/

Warning: Procedure created with compilation errors.

show errors

Errors for PROCEDURE SP42:

LINE/COL ERROR
-------- -----------------------------------------------------------------
3/2      PL/SQL: Statement ignored
3/2      PLS-00201: identifier 'SCOTT.P42' must be declared

If SCOTT grants the permission directly to my new user:

grant execute on p42 to someuser;

... then the named block now works:

create or replace procedure sp42 as
begin
    scott.p42.proc;
end;
/

Procedure created.

If you want to see where you might be getting the execute permission from, you can run a query like this, although there may be a hierarchy of roles to pick through:

select grantee, privilege from all_tab_privs
where table_schema = 'SomeSchema'
and table_name = 'SomePackage';

Answer 2

You can't specify the size of parameters for a procedure or function. Try removing the sizes, as well as moving the commit as Vincent suggested.

CREATE OR REPLACE PROCEDURE synonym_proc(result_cursor      OUT SYS_REFCURSOR,
                                         in_cp_id           IN NUMBER,
                                         in_cp_name         IN VARCHAR2,
                                         in_country_name    IN VARCHAR2,
                                         in_industry_name   IN VARCHAR2,
                                         in_max_result_size IN NUMBER) AS
  out_result_size NUMBER;
BEGIN
  main_proc(result_cursor,
            in_cp_id,
            in_cp_name,
            in_country_name,
            in_industry_name,
            in_max_result_size,
            out_result_size);
  COMMIT;
END;

You should be getting an error like PLS-00103: Encountered the symbol "(" when expecting one of the following: ...

Answer 3

Syntax for Oracle SP is

CREATE OR REPLACE PROCEDURE procedure_name(--parameter list) AS
--Local Variables
BEGIN
--Body
END;

Now you have made a mistake that instead of putting COMMIT before END you have placed it after END. It should be like this

COMMIT;
END;