471,075 Members | 1,276 Online
Bytes | Software Development & Data Engineering Community
Post +

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 471,075 software developers and data experts.

Using the same type in 2 instance

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
Jul 19 '05 #1
3 4555
Jan
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;


kl***********@netscape.net (Steph) wrote in message news:<e0**************************@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

Jul 19 '05 #2
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
Jul 19 '05 #3
kl***********@netscape.net (Steph) wrote in message news:<e0**************************@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


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 --
Jul 19 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

6 posts views Thread by crook | last post: by
1 post views Thread by batista | last post: by
15 posts views Thread by shuisheng | last post: by
3 posts views Thread by Steph | last post: by
reply views Thread by leo001 | last post: by

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.