Re: Calling PL/SQL package (that returns a table) from a package

Berend.Brinkhuis_at_Eva-Tone.com (Berend) wrote in message news:<3a9d48b7.0209250453.1a947a47_at_posting.google.com>...
> I am trying to call a PL/SQL package (that returns pl/sql tables) from
> a pl/sql package. When I try to compile the body it errors with
> "PLS-00306: wrong number or types of arguments in call to
> 'CALLED_PKG'".
>
> The reason I am using pl/sql tables is to pass customized record sets
> to a VB program. This works. I can call the package correctly from
> VB but not from another PL/SQ package.
>
> here is the pacakges i created to test. Calling a package without a
> pl/sql table works.
>
> CREATE OR REPLACE PACKAGE eva_test_calling_pkg
> IS
> TYPE tbl_cust_order_id IS TABLE OF sysadm.customer_order.id%TYPE
> INDEX BY BINARY_INTEGER;
>
>
> PROCEDURE call_table_pkg (
> p_cust_order_id OUT tbl_cust_order_id
> );
>
> PROCEDURE call_varchar2_pkg (
> p_cust_order_id out varchar2
> );
> END eva_test_calling_pkg;
> /
> CREATE OR REPLACE PACKAGE BODY eva_test_calling_pkg
> AS
> PROCEDURE call_table_pkg (p_cust_order_id OUT tbl_cust_order_id)
> IS
>
> pp_cust_order_id tbl_cust_order_id;
> BEGIN
> null;
> eva_test_called_pkg.called_pkg(pp_cust_order_id );
> END call_table_pkg;
>
> PROCEDURE call_varchar2_pkg (p_cust_order_id OUT VARCHAR2)
> IS
> v_cust_order_id sysadm.cust_order_line.cust_order_id%TYPE;
> BEGIN
> eva_test_called_pkg.called_varchar2_pkg(p_cust_order_id);
> END call_varchar2_pkg;
> END eva_test_calling_pkg;
> /
>
> CREATE OR REPLACE PACKAGE eva_test_called_pkg
> IS
> TYPE tbl_cust_order_id IS TABLE OF sysadm.customer_order.id%TYPE
> INDEX BY BINARY_INTEGER;
>
>
> PROCEDURE called_pkg (
> p_cust_order_id OUT tbl_cust_order_id
> );
> PROCEDURE called_varchar2_pkg (
> p_cust_order_id OUT varchar2
> );
> END eva_test_called_pkg;
> /
> CREATE OR REPLACE PACKAGE BODY eva_test_called_pkg
> AS
> PROCEDURE called_pkg (p_cust_order_id OUT tbl_cust_order_id)
> IS
> TYPE tbl_act_cost_ratio IS TABLE OF NUMBER
> INDEX BY BINARY_INTEGER;
>
> v_cust_order_id sysadm.cust_order_line.cust_order_id%TYPE;
> p1_act_cost_ratio tbl_act_cost_ratio;
> BEGIN
> p_cust_order_id (1) := 10;
> p_cust_order_id (2) := 11;
> END called_pkg;
>
> PROCEDURE called_varchar2_pkg (p_cust_order_id OUT VARCHAR2)
> IS
> v_cust_order_id VARCHAR (30);
> BEGIN
> p_cust_order_id := 'OK';
> END called_varchar2_pkg;
> END eva_test_called_pkg;
> /
>
>
>
> I get the following error when I compile:
>
> PLS-00306: wrong number or types of arguments in call to 'CALLED_PKG'

Hi

You define identical TYPEs in bouth your packages, but they are not the SAME. In the caling package; remove the TYPE declaration, and refere to the called package TYPE:

p_cust_order_id OUT tbl_cust_order_id should be replaced with
p_cust_order_id OUT
eva_test_called_pkg.tbl_cust_order_id

Regards Odd Morten Received on Thu Sep 26 2002 - 19:14:08 CEST