Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Error in calling a packaged procedure on another schema with alias name

SRI RAMMar 11 2012 — edited Mar 11 2012
hi all,

i am calling a packaged procedure which has collection type as in parameter which resides in remote database by using synonyms.for which i am creating a synonym in my current schema.

Below package in remote database:
CREATE OR REPLACE PACKAGE BODY test_hlr
AS
PROCEDURE raj_test (
pi_username IN VARCHAR2,
pi_serial_no IN arr_numb, --user defined collection type
po_error_code OUT NUMBER
)
AS
BEGIN
FOR i IN pi_serial_no.FIRST .. pi_serial_no.LAST
LOOP
INSERT INTO hlr_raj
(user_name, serial_no
)
VALUES (pi_username, pi_serial_no(i)
);
END LOOP;
END;
END;



i am creating synonym for package in my current database and calling that synonym package as shown below

create synonym ram_test for ram_test@dblink

CREATE OR REPLACE PACKAGE BODY test_bnr
AS

PROCEDURE ram_test (
pi_username1 IN VARCHAR2,
pi_serial_no1 IN arr_numb, --user defined collection type
po_error_code OUT NUMBER
)
AS

BEGIN
test_hlr.raj_test (pi_username => pi_username1,
pi_serial_no => pi_serial_no1,
po_error_code => po_error_code
);
END;
END;

while compiling above package am getting below error
PLS-00306: wrong number or types of arguments in call to 'RAJ_TEST


if i remove collection type and use primitive data types then am not getting any error.

please give any suggestions, regarding the same.

Regards,
Sri Ram.
This post has been answered by Solomon Yakobson on Mar 11 2012
Jump to Answer
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Apr 8 2012
Added on Mar 11 2012
2 comments
397 views