Re: Using the same type in 2 instance

From: Jan <janik_at_pobox.sk>
Date: 12 Jul 2004 23:01:57 -0700
Message-ID: <81511301.0407122201.44a473d7_at_posting.google.com>


If you want to call a procedure on DB_2 from DB_1, try this:

  1. Create types in each database, say in DB_1 - my_type_1, in DB_2 - my_type_2
  2. In DB_2, you will have a procedure P_2

CREATE OR REPLACE PROCEDURE P_2 (p_out OUT my_type_2)

   IS

BEGIN
... some processing

END P_2;


3) then in DB_1, you will have a procedure

CREATE OR REPLACE PROCEDURE P_1 (p_out OUT my_type_1)

   IS

   l_out_2 DB_2_at_my_type_2;

BEGIN   p_2_at_DB_2(l_out_2); -- calling the procedure on DB_2 with the parameter

  • referenced to my_type_2_at_DB_2
    • if your types e.g. associative arrays, then loops through it, you
    • should add also checks if l_out_2 is not empty

  FOR i IN l_out_2.FIRST .. l_out_2.LAST LOOP     p_out(i):=l_out_2(i);
  END LOOP; END P_2; kleinstephane_at_netscape.net (Steph) wrote in message news:<e033c553.0407120857.36b6a447_at_posting.google.com>...
> Hi,
> On my first oracle instance, I have a global type 'productarray'
> (CREATE OR REPLACE TYPE productarray AS TABLE OF VARCHAR(30)), I have
> a package procedure having myArray OUT productarray as parameter.
> No problem to manage this OUT parameter from a function or procedure
> in the same instance, even if schemas are different.
> But if I try to use this package procedure from procedure in another
> instance, I have the error :
> PLS-00306 : wrong number or types of arguments in call of
> 'myfunction'.
>
> the strict same global type has been created in both instances and the
> package 1 is visible in instance 2 via a synonym, user 2 has execute
> right on package 1.
>
> Anyone has an idea about what is wrong ?
>
> Thanks a lot.
>
> Stephane
Received on Tue Jul 13 2004 - 08:01:57 CEST

Original text of this message