Procedure Execution of another schema

Printable View

03-28-2002, 11:23 AM
pareshg

Hi,

We have 2 users.
One is TABS which is owner of the application schema.
which has all the tables,procedures, packages.
And we have another user SAM , who is normal User.
When we connect to SAM and try to execute the procedure
which is owned by TABS. it is giving error.
We have checked all the privileges of SAM. We also given
execute privilage on that procedure TO SAM. But we are not able to findout the problem.This problem is for all procedures,
packages of TABS schema.
---------------------------------------------------------------
SQL> SHOW USER
USER is "TABS"
SQL>
SQL> CREATE PROCEDURE TESTPROC
2 IS
3 BEGIN
4 DBMS_OUTPUT.PUT_LINE('PROCEDURE TESTPROC IS EXECUTED');
5 END;
6 /

Procedure created.

SQL> EXECUTE TESTPROC;

PL/SQL procedure successfully completed.

SQL> GRANT EXECUTE ON TESTPROC TO PARESH;

Grant succeeded.

SQL> CONNECT SAM/SAM@TAB1D
Connected.
SQL>
SQL> EXECUTE TABS.TESTPROC;
BEGIN TABS.TESTPROC; END;

*
ERROR at line 1:
ORA-06550: line 1, column 12:
PLS-00302: component 'TESTPROC' must be declared
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored

------------------------------------------------------------------------------

Regards

Paresh
03-28-2002, 11:27 AM
pando

Quote:

Originally posted by pareshg

SQL> GRANT EXECUTE ON TESTPROC TO PARESH;

Grant succeeded.

SQL> CONNECT SAM/SAM@TAB1D
Connected.

you sure this is right? granting to paresh and you want sam to be able to exec this proc?
03-28-2002, 11:31 AM
pareshg

Quote:

Originally posted by pando

Quote:

Originally posted by pareshg

SQL> CONNECT SAM/SAM@TAB1D
Connected.

you sure this is right? granting to paresh and you want sam to be able to exec this proc?

Actually

SQL> GRANT EXECUTE ON TESTPROC TO SAM;

Grant succeeded.

I have granted tp SAM. It was copy - paste mistake.

Pando pls tell us the solution. It is very urgent.

Paresh
03-28-2002, 11:48 AM
marist89

Code:

SQL> connect a/a Connected. SQL> create or replace procedure foo is 2 begin 3 dbms_output.put_line('bar'); 4 end; 5 / Procedure created. SQL> set serveroutput on SQL> exec foo bar PL/SQL procedure successfully completed. SQL> grant execute on foo to b; Grant succeeded. SQL> connect b/b Connected. SQL> set serveroutput on SQL> exec a.foo bar PL/SQL procedure successfully completed.