获取“ PLS-00306：呼叫”中的参数数字错误或类型Oracle 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'                                  
                                      ,''                                      
                                    );

为什么他们失败了？因为这两种类型的声明与上面说明的分配方式不符。由于您尚未提供类型规范，因此无法确切地告诉您原因，但是上面的示例应该指向正确的方向。