Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 21043
  • Last Modified:

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

I,
I have written this simple SP to pass one value to the SP and return three values.
The procedure is as below:

create or replace procedure get_count_of_dnis1
(
dnis_in_out in out varchar,
scr_out out varchar,
no_cnt out varchar)

As
begin
select count(*) into no_cnt from routing where dnis1 like dnis_in_out or dnis2 like dnis_in_out;
select menu_script into scr_out from routing where dnis1 like dnis_in_out or dnis2 like dnis_in_out;
dbms_output.put_line(scr_out);
dbms_output.put_line(no_cnt);
end;
/
the procedure compiles OK but when I try to run the procedure like execute get_count_of_dnis1(0870321321);

I get the following error.

ORA-06550: line 1, column 7:
PLS-00306: wrong number or types of arguments in call to 'GET_COUNT_OF_DNIS1'
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored

I am using the parameter passed in to SP in a query in the body of procedure. Any ideas why this is happening ?
0
tabrezgm
Asked:
tabrezgm
  • 2
1 Solution
 
Arthur_WoodCommented:
since the SP has 3 OUT arguments, then this line:

execute get_count_of_dnis1(0870321321);


is clearly in error, EXACTLY as the error massage indicates.  You need to supply THREE variables as the arguments of the line:

execute get_count_of_dnis1(Var1, Var2, var3);

here var1, var2 and var3 will RECIEVE the output values from the stored procedure.

AW
0
 
Arthur_WoodCommented:
sorry, misread the SP declaration.  YOu need to supply the variables to hold the two vales that are declared as PURE OUT paramaters:

execute get_count_of_dnis1('0870321321', Var2, Var3);

assuming that '0870321321' is the correct value for dnis_in_out.

0
 
tabrezgmAuthor Commented:
AW,
I tried your recommendation but here is the error message I get

SQL> exec get_count_of_dnis1('0870321321',scr_out,no_cnt);
BEGIN get_count_of_dnis1('0870321321',scr_out,no_cnt); END;

                                      *
ERROR at line 1:
ORA-06550: line 1, column 39:
PLS-00201: identifier 'SCR_OUT' must be declared
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored

Any ideas ???
0
 
bhaskersrCommented:
If the procedure is having the Out parameter u can't run from the sql prompt.

For this what u have to do is

u have to declare the two values. ie.

declare
scr_out varchar2(20);
no_cnt varhcar2(20);
begin
get_count_of_gnis1('0870321321',scr_out,no_cnt);
dbms_output.put_line(scr_out);
dbms_output.put_line(no_cnt);
end;
/

excute this block u will get the ansower.

0

Featured Post

[Webinar On Demand] Database Backup and Recovery

Does your company store data on premises, off site, in the cloud, or a combination of these? If you answered “yes”, you need a data backup recovery plan that fits each and every platform. Watch now as as Percona teaches us how to build agile data backup recovery plan.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now