获取"PLS-00306: wrong number or types of arguments in call to"甲骨文 11G

Question

我得到这个奇怪的错误信息，而在我的ORACLE 11G数据库执行一些代码。

这是我的代码:

SET serveroutput ON;      
DECLARE
l_xxe_customer_rec       CUSTOMER_REC;
l_xxe_site_tab           SITE_TAB_TABLE; 
l_xxe_site_rec           SITE_TAB;
l_xxe_contact_tab        CONTACT_TAB_TABLE;
l_xxe_contact_rec        CONTACT_TAB;
l_xxe_relate_tab         RELATE_TAB_TABLE;
l_xxe_relate_rec         RELATE_TAB;

x_cust_account_id        NUMBER;
x_party_id                   NUMBER;
x_contact_party_id        NUMBER;
x_return_status            VARCHAR2 ( 2000 ) ;
x_msg_count               NUMBER;
x_msg_data                VARCHAR2 ( 2000 ) ;

BEGIN
-- --------------------------------------------------------------------------------------
-- HEADER
-- --------------------------------------------------------------------------------------
l_xxe_customer_rec := CUSTOMER_REC ('785710'
,'I'
,'RANDOM'
,'N'
);

-- --------------------------------------------------------------------------------------
-- Site 
-- --------------------------------------------------------------------------------------
l_xxe_site_tab :=  SITE_TAB_TABLE();
l_xxe_site_rec := SITE_TAB('0712892'                                     
,'01-0712892-01'                             
,'Y'                                         
);


l_xxe_site_tab.extend;
l_xxe_site_tab(l_xxe_site_tab.count):= l_xxe_site_rec;

-- --------------------------------------------------------------------------------------
-- Contact
-- --------------------------------------------------------------------------------------
l_xxe_contact_tab :=  CONTACT_TAB_TABLE();
l_xxe_contact_rec :=  CONTACT_TAB('0712892'                                  
,''                                      
);


l_xxe_contact_tab.extend;
l_xxe_contact_tab(l_xxe_contact_tab.count):= l_xxe_contact_rec;

l_xxe_relate_tab :=  RELATE_TAB_TABLE();


dbms_output.put_line ( 'CALLING API');
INT_IN_PK.main (p_customer_rec      => l_xxe_customer_rec                                       
,p_site_rec              => l_xxe_site_tab                                
,p_contact_rec         => l_xxe_contact_tab                                
,p_relate_rec           => l_xxe_relate_tab                                   
,x_party_id              => x_party_id 
,x_cust_account_id   => x_cust_account_id
,x_return_status       => x_return_status        
,x_msg_count          => x_msg_count           
,x_msg_data            => x_msg_data
);
dbms_output.put_line ( 'x_return_status = '||SUBSTR ( x_return_status, 1, 255 ) ) ;
dbms_output.put_line ( 'x_msg_count = '||TO_CHAR ( x_msg_count ) ) ;
dbms_output.put_line ( 'x_party_id = '||TO_CHAR ( x_party_id ) ) ;
dbms_output.put_line ( 'x_cust_account_id = '||TO_CHAR ( x_cust_account_id ) ) ;
dbms_output.put_line ( 'x_contact_party_id = '||TO_CHAR ( x_contact_party_id ) ) ;
dbms_output.put_line ( 'x_msg_data = '|| SUBSTR ( x_msg_data, 1, 255 ) ) ;
IF x_return_status != 'S' THEN
FOR I      IN 1..x_msg_count
LOOP
dbms_output.put_line ( I||'.'||SUBSTR ( FND_MSG_PUB.Get ( p_encoded=> FND_API.G_FALSE ), 1, 255 ) ) ;
END LOOP;
END IF;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE ( 'Error: '||SQLERRM ) ;
END;

错误消息:

PLS-00306:呼叫中参数类型或号码错误"CUSTOMER_REC">

PLS-00306:调用'CONTACT_TAB'时参数的数量或类型错误

我该如何解决这个问题?我做错什么了吗?

Answer 1

错误正在发生，因为您的类型CUSTOMER_REC和CONTACT_TAB在初始化时获得错误的数字或类型或参数。这不是完整的错误消息，应该有行号指示抛出错误的确切行。解决方法:检查这些类型的定义，验证参数和位置。您发布的代码使用"位置符号":它依赖于参数位置，因此它假设第一个参数总是相同的。使用"命名符号"更安全。如果基础类型中参数的顺序发生变化，代码也不会失败。下面是一个示例来说明您看到的错误:

DECLARE
TYPE cust_rec IS RECORD (
customer_name VARCHAR2(100),
salary        NUMBER
);
l_cust cust_rec;
BEGIN
-- assign value using positional notation. This works but relies on "customer_name" being first argument.
l_cust := cust_rec('Koen',100); 
-- assign value using named notation. This is advised. Code is more solid
l_cust := cust_rec(customer_name => 'Koen',salary =>100);
-- assign wrong type of value to "salary". Fails with PLS-00306: wrong number or types of arguments ...
l_cust := cust_rec('Koen',SYSDATE);
-- use named notation for a non-existing argument. Fails with PLS-00306: wrong number or types of arguments ...
l_cust := cust_rec(last_name => 'Koen');
END;
/
Error report -
ORA-06550: line 13, column 13:
PLS-00306: wrong number or types of arguments in call to 'CUST_REC'
ORA-06550: line 13, column 3:
PL/SQL: Statement ignored
ORA-06550: line 15, column 13:
PLS-00306: wrong number or types of arguments in call to 'CUST_REC'
ORA-06550: line 15, column 3:
PL/SQL: Statement ignored
06550. 00000 -  "line %s, column %s:n%s"
*Cause:    Usually a PL/SQL compilation error.
*Action:

请注意，错误信息给出了确切的位置。

回到你的代码:

l_xxe_site_tab :=  SITE_TAB_TABLE();
-- line below is failing
l_xxe_site_rec := SITE_TAB('0712892'                                     
,'01-0712892-01'                             
,'Y'                                         
);


l_xxe_site_tab.extend;
l_xxe_site_tab(l_xxe_site_tab.count):= l_xxe_site_rec;

-- --------------------------------------------------------------------------------------
-- Contact
-- --------------------------------------------------------------------------------------
l_xxe_contact_tab :=  CONTACT_TAB_TABLE();
-- line below is failing
l_xxe_contact_rec :=  CONTACT_TAB('0712892'                                  
,''                                      
);

为什么他们会失败?因为如上所示，这两个类型的声明与它们的赋值方式不匹配。由于您没有提供类型规范，因此不可能确切地告诉您原因，但上面的示例应该为您指明正确的方向。