Home » SQL & PL/SQL » SQL & PL/SQL » Wrong arguments error message
Wrong arguments error message [message #318414] Tue, 06 May 2008 16:14 Go to next message
shree_z
Messages: 75
Registered: February 2008
Member
Hi all ,

I am tryng to execute the following procedure from sql plus. I am getting the error

Quote:
PLS-00306: wrong number or types of arguments in call to 'SHIP_CONFIRM'


here is the procedure I am executing.

create or replace procedure SHIP_CONFIRM(
x_return_status OUT VARCHAR2,
x_msg_count OUT NUMBER,
x_msg_data OUT VARCHAR2) IS
-- Standard Parameters.
p_api_version_number NUMBER;
init_msg_list VARCHAR2(30);
x_msg_details VARCHAR2(3000);
x_msg_summary VARCHAR2(3000);
p_validation_level NUMBER;
commit VARCHAR2(30);
-- Parameters for WSH_DELIVERIES_PUB.CREATE_UPDATE_DELIVERY
action_code VARCHAR2(15);
delivery_id NUMBER;
delivery_info WSH_DELIVERIES_PUB.Delivery_Pub_Rec_Type;
return_status VARCHAR2(14);
msg_count NUMBER;
msg_data VARCHAR2(45);
name VARCHAR2(30);
-- Parameters for WSH_DELIVERY_DETAILS_PUB.Detail_to_Delivery
p_delivery_id NUMBER;
delivery_name VARCHAR2(30);
p_TabOfDelDets WSH_DELIVERY_DETAILS_PUB.id_tab_type;
p_action VARCHAR2(30);
-- Parameters for WSH_DELIVERIES_PUB.Delivery_Action.
p_action_code VARCHAR2(15);
--p_delivery_id NUMBER;
p_delivery_name VARCHAR2(30);
p_asg_trip_id NUMBER;
p_asg_trip_name VARCHAR2(30);
p_asg_pickup_stop_id NUMBER;
p_asg_pickup_loc_id NUMBER;
p_asg_pickup_stop_seq NUMBER;
p_asg_pickup_loc_code VARCHAR2(30);
p_asg_pickup_arr_date DATE;
p_asg_pickup_dep_date DATE;
p_asg_dropoff_stop_id NUMBER;
p_asg_dropoff_loc_id NUMBER;
p_asg_dropoff_stop_seq NUMBER;
p_asg_dropoff_loc_code VARCHAR2(30);
p_asg_dropoff_arr_date DATE;
p_asg_dropoff_dep_date DATE;
p_sc_action_flag VARCHAR2(10);
p_sc_intransit_flag VARCHAR2(10);
p_sc_close_trip_flag VARCHAR2(10);
p_sc_create_bol_flag VARCHAR2(10);
p_sc_stage_del_flag VARCHAR2(10);
p_sc_trip_ship_method VARCHAR2(30);
p_sc_actual_dep_date VARCHAR2(30);
p_sc_report_set_id NUMBER;
p_sc_report_set_name VARCHAR2(60);
p_sc_defer_interface_flag VARCHAR2(10);
p_sc_send_945_flag VARCHAR2(10);
p_sc_rule_id NUMBER;
p_sc_rule_name VARCHAR2(10);
p_wv_override_flag VARCHAR2(10);
x_trip_id VARCHAR2(30);
x_trip_name VARCHAR2(30);
/*Handle exceptions*/
fail_api EXCEPTION;
BEGIN
-- Initialize return status
x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
-- Values for WSH_DELIVERIES_PUB.CREATE_UPDATE_DELIVERY
-- Create a new delivery for the following
delivery_info.initial_pickup_location_id := 160;
delivery_info.ultimate_dropoff_location_id := 135;
delivery_info.gross_weight := 10;
delivery_info.ship_method_code := 'UPS';
p_action_code := 'CREATE';
-- Call to WSH_DELIVERIES_PUB.CREATE_UPDATE_DELIVERY
WSH_DELIVERIES_PUB.CREATE_UPDATE_DELIVERY(
p_api_version_number => 1.0,
p_init_msg_list => init_msg_list,
x_return_status => return_status,
x_msg_count => msg_count,
x_msg_data => msg_data,
p_action_code => p_action_code,
p_delivery_info => delivery_info,
p_delivery_name => delivery_name,
x_delivery_id => delivery_id,
x_name => name );
if (x_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS) then
raise fail_api;
end if;
p_delivery_id := delivery_id;
--pub_freight_costs.delivery_id := delivery_id;
-- Values for WSH_DELIVERY_DETAILS_PUB.Detail_to_Delivery
-- Call Detail_to_Delivery with an action code of ASSIGN to assign details to a
-- delivery.
p_TabOfDelDets(1) := 001;
p_TabOfDelDets(2) := 002;
p_action := 'ASSIGN';
-- Call to WSH_DELIVERY_DETAILS_PUB.Detail_to_Delivery.
WSH_DELIVERY_DETAILS_PUB.Detail_to_Delivery(
p_api_version => 1.0,
p_init_msg_list => init_msg_list,
p_commit => commit,
p_validation_level => p_validation_level,
x_return_status => return_status,
x_msg_count => msg_count,
x_msg_data => msg_data,
p_TabOfDelDets => p_TabOfDelDets,
p_action => p_action,
p_delivery_id => p_delivery_id,
p_delivery_name => delivery_name);
if (x_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS) then
raise fail_api;
end if;
-- Values for Ship Confirming the delivery.
p_action_code := 'CONFIRM'; -- The action code for ship confirm
p_delivery_id := 3145741; -- The delivery that needs to be confirmed
p_delivery_name := '123'; -- The delivery name,
p_sc_action_flag := 'S'; -- Ship entered quantity.
p_sc_close_trip_flag := 'Y'; -- Close the trip after ship confirm
p_sc_trip_ship_method := 'UPS'; -- The ship method code
-- Call to WSH_DELIVERIES_PUB.Delivery_Action.
WSH_DELIVERIES_PUB.Delivery_Action(
p_api_version_number => 1.0,
p_init_msg_list => fnd_api.g_true,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_action_code => 'CONFIRM',
p_delivery_id => 3145741,
p_delivery_name => '123',
p_asg_trip_id => p_asg_trip_id,
p_asg_trip_name => p_asg_trip_name,
p_asg_pickup_stop_id => p_asg_pickup_stop_id,
p_asg_pickup_loc_id => p_asg_pickup_loc_id,
p_asg_pickup_stop_seq => p_asg_pickup_stop_seq,
p_asg_pickup_loc_code => p_asg_pickup_loc_code,
p_asg_pickup_arr_date => p_asg_pickup_arr_date,
p_asg_pickup_dep_date => p_asg_pickup_dep_date,
p_asg_dropoff_stop_id => p_asg_dropoff_stop_id,
p_asg_dropoff_loc_id => p_asg_dropoff_loc_id,
p_asg_dropoff_stop_seq => p_asg_dropoff_stop_seq,
p_asg_dropoff_loc_code => p_asg_dropoff_loc_code,
p_asg_dropoff_arr_date => p_asg_dropoff_arr_date,
p_asg_dropoff_dep_date => p_asg_dropoff_dep_date,
p_sc_action_flag => p_sc_action_flag,
p_sc_intransit_flag => p_sc_intransit_flag,
p_sc_close_trip_flag => p_sc_close_trip_flag,
p_sc_create_bol_flag => p_sc_create_bol_flag,
p_sc_stage_del_flag => p_sc_stage_del_flag,
p_sc_trip_ship_method => p_sc_trip_ship_method,
p_sc_actual_dep_date => p_sc_actual_dep_date,
p_sc_report_set_id => p_sc_report_set_id,
p_sc_report_set_name => p_sc_report_set_name,
p_sc_defer_interface_flag => p_sc_defer_interface_flag,
p_sc_send_945_flag => p_sc_send_945_flag,
p_sc_rule_id => p_sc_rule_id,
p_sc_rule_name => p_sc_rule_name, 
p_wv_override_flag => p_wv_override_flag,
x_trip_id => x_trip_id,
x_trip_name => x_trip_name
);

if (x_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS) then
raise fail_api;
end if;

exception
when fail_api then
WSH_UTIL_CORE.get_messages('Y', x_msg_summary, x_msg_details, x_msg_count);
if x_msg_count > 1 then
x_msg_data := x_msg_summary || x_msg_details;
else
x_msg_data := x_msg_summary;
end if;
END SHIP_CONFIRM;




Could anyone help me find out what argument I should add or change in the main procedure, to make this to work?

Thanks in advance

[Updated on: Tue, 06 May 2008 16:16]

Report message to a moderator

Re: Wrong arguments error message [message #318418 is a reply to message #318414] Tue, 06 May 2008 16:27 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
You need to show what you did EXACTLY which generated the error.

It is a bad, BAD, BAD implementation to use KEYWORDS as PL/SQL variables.

>commit VARCHAR2(30);
>name VARCHAR2(30);
Re: Wrong arguments error message [message #318421 is a reply to message #318418] Tue, 06 May 2008 17:02 Go to previous messageGo to next message
shree_z
Messages: 75
Registered: February 2008
Member
Hi,

Thanks for the advice .

I ran the following code in sql plus to execute the procedure ship_confirm.

exec ship_confirm


this is what generated the error message.

Re: Wrong arguments error message [message #318423 is a reply to message #318414] Tue, 06 May 2008 17:10 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
the procedure has 3 arguments
>create or replace procedure SHIP_CONFIRM(
>x_return_status OUT VARCHAR2,
>x_msg_count OUT NUMBER,
>x_msg_data OUT VARCHAR2) IS
& you supplied 0 arguments so Oracle complains.

Either supply required arguments when invoking procedure,
or remove the arguments from the procedure altogether.

Simply put the number of arguments supplied when invoking the procedure must match the number of arguments when the procedure was created.
Re: Wrong arguments error message [message #318447 is a reply to message #318423] Tue, 06 May 2008 20:32 Go to previous messageGo to next message
shree_z
Messages: 75
Registered: February 2008
Member
Hi,

Thanks for the reply, I got my major error corrected.

Now I invoked the procedure as below

 declare
 x_return_status varchar2(50);
 x_msg_count NUMBER;
 x_msg_data varchar2(50);
 begin
 ship_confirm(x_return_status,x_msg_count,x_msg_data);
 end;


This time I got the below error.

Quote:
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error
ORA-06512: at "APPS.SHIP_CONFIRM", line 167
ORA-06510: PL/SQL: unhandled user-defined exception
ORA-06512: at line 6


But, I have already declared fail_api exception in the procedure. Then what is this error pointing to?

Thanks

Re: Wrong arguments error message [message #318450 is a reply to message #318414] Tue, 06 May 2008 21:04 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
http://www.orafaq.com/forum/t/88153/0/
Please read & FOLLOW posting guidelines as stated in URL above

>ORA-06502: PL/SQL: numeric or value error
>ORA-06512: at "APPS.SHIP_CONFIRM", line 167

I refuse to guess which line is #167 & since you have not followed Posting Guidelines, You're On Your Own (YOYO)!
Re: Wrong arguments error message [message #318451 is a reply to message #318450] Tue, 06 May 2008 21:25 Go to previous messageGo to next message
shree_z
Messages: 75
Registered: February 2008
Member
I am sorry for the inconvenience caused. I went through the forum guidelines. I shall try my best to follow the rules here. I am sorry if I had been impolite to you in any of my above posts.

Coming to the procedure , here is the output from Sql Plus
  1  declare
  2  x_return_status varchar2(50);
  3  x_msg_count NUMBER;
  4  x_msg_data varchar2(50);
  5  begin
  6  ship_confirm(x_return_status,x_msg_count,x_msg_data);
  7* end;
SQL> /
declare
*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error
ORA-06512: at "APPS.SHIP_CONFIRM", line 167
ORA-06510: PL/SQL: unhandled user-defined exception
ORA-06512: at line 6


line 167 in the procedure ship_confirm refers to
>WSH_UTIL_CORE.get_messages('Y', x_msg_summary, x_msg_details, x_msg_count);

Thanks again.


Re: Wrong arguments error message [message #318493 is a reply to message #318451] Wed, 07 May 2008 00:54 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
It is likely that one of your variables x_msg_summary, x_msg_details is too small. Try VARCHAR2(32500) instead of VARCHAR2(3000).

Regards
Michel
Re: Wrong arguments error message [message #318707 is a reply to message #318493] Wed, 07 May 2008 12:43 Go to previous messageGo to next message
shree_z
Messages: 75
Registered: February 2008
Member
Hi Michel,

Thanks for the reply. I tried what you suggested, but still didnt work..

Its still showing the same error.

Thanks
Re: Wrong arguments error message [message #318709 is a reply to message #318414] Wed, 07 May 2008 12:52 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
from SQL*Plus do:
SQL> DESC WSH_UTIL_CORE.get_messages
& post results back here
Re: Wrong arguments error message [message #318716 is a reply to message #318709] Wed, 07 May 2008 13:25 Go to previous messageGo to next message
shree_z
Messages: 75
Registered: February 2008
Member
Thanks,

SQL> DESC WSH_UTIL_CORE.get_messages
ERROR:
ORA-04043: object WSH_UTIL_CORE.get_messages does not exist


So this was the cause of the error.

But when I query for the same wsh_util_core.get_messages in TOAD, I am getting the description. I have logged into dev environment with the same userid and password on both Sql plus and TOAD.

Thanks
Re: Wrong arguments error message [message #318717 is a reply to message #318716] Wed, 07 May 2008 13:29 Go to previous messageGo to next message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
Quote:
WSH_UTIL_CORE

I believe this should be name of the package. You should describe on the package level and not on the package.procedure/function level.
SQL> desc dbms_output.put_line;
ERROR:
ORA-04043: object dbms_output.put_line does not exist

SQL> desc dbms_output;
PROCEDURE DISABLE
PROCEDURE ENABLE
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 BUFFER_SIZE                    NUMBER(38)              IN     DEFAULT

Regards

Raj
Re: Wrong arguments error message [message #318718 is a reply to message #318717] Wed, 07 May 2008 13:36 Go to previous messageGo to next message
shree_z
Messages: 75
Registered: February 2008
Member
Hi Raj,

Thanks for the reply. Yes you were correct.

So how can I make the above program work?. How can I use the exception in the above program?

Thanks

[Updated on: Wed, 07 May 2008 13:37]

Report message to a moderator

Re: Wrong arguments error message [message #318955 is a reply to message #318718] Thu, 08 May 2008 09:16 Go to previous messageGo to next message
shree_z
Messages: 75
Registered: February 2008
Member
Hi all,

Thanks for all your support. The procedure is now completing successfully.

I just removed the user defined exception and used a simpler method just to output the variables x_msd_summary etc using dbms_output.put_line.

So now it is working perfect.

Thank you all once again.

[Updated on: Thu, 08 May 2008 09:18]

Report message to a moderator

Re: Wrong arguments error message [message #318957 is a reply to message #318955] Thu, 08 May 2008 09:17 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
How did you make this? What was the error in the end?

Regards
Michel
Re: Wrong arguments error message [message #318968 is a reply to message #318957] Thu, 08 May 2008 09:45 Go to previous message
shree_z
Messages: 75
Registered: February 2008
Member
Hi,

I just removed the exception and put the following in its place

for i in 1.. x_msg_count loop
      wsh_util_core.Get_Messages( 
                       fnd_api.g_false
                     ,x_summary
                      ,x_details
                      ,x_count
                    );
     dbms_output.put_line('Summary ' || x_summary  );
     dbms_output.put_line('Details ' || x_details  );
     dbms_output.put_line('Count ' || x_count );
     end loop;


and then I got the error mesage saying that "buffer size is too small"
So, then as you suggested, i just increased the variable sizes of x_summary and x_details.
That worked. And the procedure was completed successfully.
There was some problem with the user defined exception 'fail_api'. So I removed it.

Thanks

[Updated on: Thu, 08 May 2008 09:46]

Report message to a moderator

Previous Topic: an odd problem about unique key when inserting into table
Next Topic: How to Convert Db2 procedures to Sql procedures
Goto Forum:
  


Current Time: Thu Dec 08 19:54:04 CST 2016

Total time taken to generate the page: 0.09680 seconds