PLS-00306 during Compilation on a Call to Remote Proc with a Pl/sql Record Parameter (Doc ID 1346129.1)

Last updated on JULY 05, 2017

Applies to:

PL/SQL - Version: 10.2.0.5 to 11.2.0.2 - Release: 10.2 to 11.2
Information in this document applies to any platform.

Symptoms

  Trying to compile a local procedure which has a call to remote procedure with a pl/sql record type IN OUT parameter fails with PLS-306.

prompt Enter alias for front end d/b:
accept front
prompt Enter alias for back end d/b:
accept back

conn system/ref4all@&front
drop user TEST_APP_PPI2 cascade;
drop user TEST_APP_PPI3 cascade;
grant dba to TEST_APP_PPI2 identified by APP_PPI2;

conn system/ref4all@&back
drop user TEST_APP_PPI2 cascade;
drop user TEST_APP_PPI3 cascade;
grant dba to TEST_APP_PPI3 identified by APP_PPI3;

conn TEST_APP_PPI3/APP_PPI3@&back;
create database link backtofront connect to TEST_APP_PPI2 identified by APP_PPI2 using '&front';
create database link fronttoback connect to TEST_APP_PPI3 identified by APP_PPI3 using '&back';

conn TEST_APP_PPI2/APP_PPI2@&front;
create database link fronttoback connect to TEST_APP_PPI3 identified by APP_PPI3 using '&back';
create database link backtofront connect to TEST_APP_PPI2 identified by APP_PPI2 using '&front';

CREATE OR REPLACE package pkg_browse_t2_PDTEST as
type pract_list_crit_rec is record( doesntMatter varchar2(100));
procedure display_pract_list( p_user_name in NUMBER);
end pkg_browse_t2_PDTEST;
/
prompt Create body will fail as pkg_browse_t3_PDTEST does not exist.

CREATE OR REPLACE package body pkg_browse_t2_PDTEST as
procedure display_pract_list( p_user_name in NUMBER)
as
v_pract_list_crit_rec pract_list_crit_rec;
begin
pkg_browse_t3_PDTEST.subProg(v_pract_list_crit_rec);
END;
end pkg_browse_t2_PDTEST;
/
show errors

conn TEST_APP_PPI3/APP_PPI3@&back

CREATE SYNONYM PKG_BROWSE_T2_PDTEST FOR TEST_APP_PPI2.PKG_BROWSE_T2_PDTEST@backtofront;

CREATE OR REPLACE package pkg_browse_t3_PDTEST as
procedure subProg(inOutPLSQLTableVar in out PKG_BROWSE_T2_PDTEST.pract_list_crit_rec);
end pkg_browse_t3_PDTEST;
/

CREATE OR REPLACE package body pkg_browse_t3_PDTEST as
procedure subProg(inOutPLSQLTableVar in out pkg_browse_t2_PDTEST.pract_list_crit_rec)
as
begin
NULL;
end;
end pkg_browse_t3_PDTEST;
/
show errors

conn TEST_APP_PPI2/APP_PPI2@&front;

CREATE SYNONYM PKG_BROWSE_T3_PDTEST FOR TEST_APP_PPI3.PKG_BROWSE_T3_PDTEST@fronttoback;

alter package PKG_BROWSE_T2_PDTEST compile;
show errors package body PKG_BROWSE_T2_PDTEST

SQL> @trytest
Enter alias for front end d/b:
REF1021U_CELCAIX3.US.ORACLE.COM
Enter alias for back end d/b:
REF1022U_CELCAIX3.US.ORACLE.COM
Connected.
............
....
PLS-00306: wrong number or types of arguments in call to 'SUBPROG'

Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms