Using the same type in 2 instance

**Jan** · Jun 27 '08, 06:33 PM

Re: Using the same type in 2 instance

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@my_type_2;

BEGIN

p_2@DB_2(l_out_ 2); -- calling the procedure on DB_2 with the parameter
-- referenced to my_type_2@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@n etscape.net (Steph) wrote in message news:<e033c553. 0407120857.36b6 a447@posting.go ogle.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

**Steph** · Jun 27 '08, 06:33 PM

Re: Using the same type in 2 instance

I did a mistake : I have the same error when I try to call this
package function since another schema of the same instance.
So my problem remains

**Mark D Powell** · Jun 27 '08, 06:33 PM

Re: Using the same type in 2 instance

kleinstephane@n etscape.net (Steph) wrote in message news:<e033c553. 0407120857.36b6 a447@posting.go ogle.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

Steph, I do not think you want to create the same type in both
instances. Doing so would not guarentee to Oracle that the two
objects matched. I believe what you want to do is reference the same
type definition from both instances. You normally define the base
type in a package specification and then reference this, %type, when
you allocate and pass the array. Try it that way.

If this change does not eliminate your problem then when you repost
please include the actual Oracle error message and matching code
pieces to increase the odds someone will be able to spot the problem.

HTH -- Mark D Powell --

Using the same type in 2 instance

Using the same type in 2 instance

Comment

Comment

Comment