Procedure Execution of another schema
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 4 of 4

Thread: Procedure Execution of another schema

  1. #1
    Join Date
    May 2001
    Location
    London
    Posts
    149
    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

  2. #2
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    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?

  3. #3
    Join Date
    May 2001
    Location
    London
    Posts
    149
    Originally posted by pando
    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

  4. #4
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    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.
    Jeff Hunter
    marist89@yahoo.com
    http://marist89.blogspot.com/
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width