错误：将数组传递给另一个过程时"Wrong number or types or arguments"

Question

我想将数组作为参数传递给另一个过程。。两者都在同一包装中。。在下面的场景中，将传递演示数组。。获取错误："PLS-00306显示的参数数量或类型错误"。。

CREATE or REPLACE package WSH_Delivery_Detail_Shipment is
type Result IS VARRAY(8) OF INTEGER;
p_result Result:=Result();
PROCEDURE CreateShipment(p_delivery_detail_interface_id IN WSH_DEL_DETAILS_INTERFACE.DELIVERY_DETAIL_INTERFACE_ID%TYPE,p_status OUT String);
PROCEDURE CreateShipmentLines(p_result IN RESULT);
END WSH_Delivery_Detail_Shipment;
/

CREATE OR REPLACE package body WSH_Delivery_Detail_Shipment as
PROCEDURE CreateShipment(p_delivery_detail_interface_id IN WSH_DEL_DETAILS_INTERFACE.DELIVERY_DETAIL_INTERFACE_ID%TYPE,p_status OUT String)
IS
CURSOR wddi_cur IS SELECT * FROM WSH_DEL_DETAILS_INTERFACE WHERE DELIVERY_DETAIL_INTERFACE_ID=p_delivery_detail_interface_id;
wddi_record WSH_DEL_DETAILS_INTERFACE%ROWTYPE;
type Result IS VARRAY(8) OF INTEGER;
p_result Result:=Result(1,1,1,1,1,1,1,1);
BEGIN
OPEN wddi_cur;
LOOP
.. Some few select queries>>>>
WSH_Delivery_Detail_Shipment.CreateShipmentLines(p_result); // Calling procedure and passing array

END LOOP;
CLOSE wddi_cur;
END CreateShipment;
procedure CreateShipmentLines(p_result IN RESULT)
is
BEGIN
....Some select queries
END CreateShipmentLines;
END WSH_Delivery_Detail_Shipment;
/

Answer 1

您声明了两个名为Result的类型。CreateShipmentLines期望第一个，但您正在通过第二个。

删除第二个声明(CreateShipment中以"type Result is ..."开头的行)应该可以解决此问题。

Answer 2

来自文档：

包规范中定义的集合类型与定义相同的本地或独立集合类型不兼容。

尽管在您的情况下，这两个声明都在包中，但第二个声明是CreateShipment过程定义的本地声明，因此仍然与包规范中的声明不兼容。尽管它们在您看来是一样的，但在Oracle编译器看来，它们是不同的类型。

因此，正如@WilliamRobertson所说，您只需要更改过程即可使用规范中声明的类型：

CREATE OR REPLACE package body WSH_Delivery_Detail_Shipment as
PROCEDURE CreateShipment(p_delivery_detail_interface_id IN WSH_DEL_DETAILS_INTERFACE.DELIVERY_DETAIL_INTERFACE_ID%TYPE,p_status OUT String)
IS
CURSOR wddi_cur IS SELECT * FROM WSH_DEL_DETAILS_INTERFACE WHERE DELIVERY_DETAIL_INTERFACE_ID=p_delivery_detail_interface_id;
wddi_record WSH_DEL_DETAILS_INTERFACE%ROWTYPE;
-- type Result IS VARRAY(8) OF INTEGER; -- remove this
p_result Result:=Result(1,1,1,1,1,1,1,1); -- now uses type from specification
BEGIN
...

作为一个单独的问题，包规范中的p_result Result:=Result();也与该过程定义中声明的p_result变量完全分离。从您所展示的内容来看，全局p_result从未使用过，这使它变得多余；但它也会使您的包有状态，这可能不是您想要的，这可能会导致以后出现不必要的"ORA-04068:现有包状态已被丢弃"错误。因此，您可能希望从规范中删除该全局变量声明。(当然，如果你确实使用了全局变量，并且你的包需要有状态，那么忽略这一部分…)