I am getting this strange error message while executing some code in my ORACLE 11G database.
This is my code:
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;
Error messages:
PLS-00306: wrong number or types of arguments in call to 'CUSTOMER_REC'
PLS-00306: wrong number or types of arguments in call to 'CONTACT_TAB'
How may i solve this? Am i doing anything wrong?
The error is happening because your the types CUSTOMER_REC and CONTACT_TAB get a wrong number or types or arguments when they're initialized. That is not the complete error message, there should be line numbers indicating the exact line the error is thrown.
Solution: check the definition of those type and verify the arguments and position. The code you posted uses "positional notation": it relies on the argument position so it assumes that the first parameter is always the same. It is safer to use "named notation". If then order of the arguments in the underlying type changes, the code will not fail. Here is an example to illustrate the error you are seeing:
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:
Note that the error message gives the exact position.
So to come back to your code:
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'
,''
);
Why are they failing ? Because the declaration of the 2 types doesn't match how they're assigned as illustrated above. Since you have not provided the type specification it is not possible to tell you exactly why but the example above should point you in the correct direction.