call a procedure from another schema

Tharangi · #1 (**permalink**) 01-24-12, 23:38

i have two different schemas called test1 and test2.In test1 there has a package called pkg1.Within that package there is a procedure called proc1.I want to call proc1 procedure from the test2 package proc2.I used visual studio 2008.how can do this?what is the statement to do this?

anacedent · #2 (**permalink**) 01-24-12, 23:50

test1.pkg1.proc1;

Littlefoot · #3 (**permalink**) 01-25-12, 01:11

Owner should first grant EXECUTE privilege on that procedure to another user.

Here's an Oracle example (I don't use Visual Studio 2008); I hope you'll get the idea.

First, connected as user SCOTT, I'll create a package that contains a procedure and - at the end - grant privileges to another user (MIKE):

Code:

SQL> show user
USER is "SCOTT"
SQL>
SQL> create or replace package pkg1 as
  2    procedure proc1;
  3  end;
  4  /

Package created.

SQL> create or replace package body pkg1 as
  2    procedure proc1 as
  3      begin
  4        dbms_output.put_line('Hello');
  5       end;
  6  end;
  7  /

Package body created.

SQL> grant execute on pkg1 to mike;

Grant succeeded.

Now, connect as MIKE and execute that procedure:

Code:

SQL> connect mike/lion@ora10
Connected.

SQL> exec scott.pkg1.proc1;
Hello

PL/SQL procedure successfully completed.

SQL>

Internet Services

Web Development

Digital Marketing

Consumer Tech