Home » SQL & PL/SQL » SQL & PL/SQL » PLS-00306: wrong number or types of arguments in call to 'GET_DNAME_USING_ID' (10g)
PLS-00306: wrong number or types of arguments in call to 'GET_DNAME_USING_ID' [message #399907] Fri, 24 April 2009 08:09 Go to next message
bhawana
Messages: 3
Registered: April 2009
Junior Member
Hi all,

M trying to get the related value from two table using function but getting error , please help me out here is the similar example ,.....

M trying to get the dname from dept table where emp.deptno=dept.deptno


CREATE OR REPLACE function GET_DNAME_USING_ID (
nID IN NUMBER,
outVAR OUT VARCHAR2)
RETURN VARCHAR2 IS
RETURN_VAR VARCHAR2(100);
BEGIN
SELECT Dname INTO outVAR FROM dept WHERE deptno = nID;
RETURN_VAR := 'Success';
RETURN (RETURN_VAR);
END;

Function created.

while executing its throwing an error..

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

please help me out...
Re: PLS-00306: wrong number or types of arguments in call to 'GET_DNAME_USING_ID' [message #399910 is a reply to message #399907] Fri, 24 April 2009 08:18 Go to previous messageGo to next message
joy_division
Messages: 4644
Registered: February 2005
Location: East Coast USA
Senior Member
1. I do not see any call to the function.
2. Why is there an OUT parameter in the function. You might want to look up functions in the manual.
3. You have no exception handling.
Re: PLS-00306: wrong number or types of arguments in call to 'GET_DNAME_USING_ID' [message #399912 is a reply to message #399907] Fri, 24 April 2009 08:22 Go to previous messageGo to next message
rajesh.chamarthi
Messages: 11
Registered: November 2006
Junior Member
Hi,

CREATE OR REPLACE function GET_DNAME_USING_ID (
nID IN NUMBER,
outVAR OUT VARCHAR2) ---This is not required..

All you need is

CREATE OR REPLACE function GET_DNAME_USING_ID (
nID IN NUMBER)
RETURN VARCHAR2 IS
RETURN_VAR VARCHAR2(100);
BEGIN
SELECT Dname INTO outVAR FROM dept WHERE deptno = nID;
RETURN_VAR := 'Success';
RETURN (RETURN_VAR);
END;

Thanks,
Rajesh.



Re: PLS-00306: wrong number or types of arguments in call to 'GET_DNAME_USING_ID' [message #399913 is a reply to message #399912] Fri, 24 April 2009 08:24 Go to previous messageGo to next message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
@bhawana and rajesh.chamarthi

Please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter) and use code tags.
Use the "Preview Message" button to verify.

Regards
Michel
Re: PLS-00306: wrong number or types of arguments in call to 'GET_DNAME_USING_ID' [message #399916 is a reply to message #399913] Fri, 24 April 2009 08:29 Go to previous messageGo to next message
bhawana
Messages: 3
Registered: April 2009
Junior Member
@ Rajesh ..

I need outvar coz m calling this funcation in inforamatica...

moreover i tried to excute your script its throwing error..

thanks,

Re: PLS-00306: wrong number or types of arguments in call to 'GET_DNAME_USING_ID' [message #399917 is a reply to message #399916] Fri, 24 April 2009 08:33 Go to previous messageGo to next message
joy_division
Messages: 4644
Registered: February 2005
Location: East Coast USA
Senior Member
It's time to look up FUNCTIONS in the manual and learn how to spell correctly.
Re: PLS-00306: wrong number or types of arguments in call to 'GET_DNAME_USING_ID' [message #399918 is a reply to message #399907] Fri, 24 April 2009 08:33 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
>i tried to excute your script its throwing error..
ERROR? What Error? I do not see any error.

You need to help us by following the Posting Guidelines as stated below.
http://www.orafaq.com/forum/t/88153/0/
Please, please, please Read & Follow Posting Guidelines above.
Go to the section labeled "Practice" & do as directed.
Re: PLS-00306: wrong number or types of arguments in call to 'GET_DNAME_USING_ID' [message #399919 is a reply to message #399916] Fri, 24 April 2009 08:34 Go to previous messageGo to next message
rajesh.chamarthi
Messages: 11
Registered: November 2006
Junior Member
ops$rchamarthi> create table dept(
2 dept_id number,
3 d_name varchar2(20));

Table created.

ops$rchamarthi> insert into dept values(10,'sales');

1 row created.

ops$rchamarthi> insert into dept values(20,'finance');

1 row created.

ops$rchamarthi> insert into dept values(30,'operations');

1 row created.

ops$rchamarthi> commit;

Commit complete.


create or replace function get_dname_for_id(
i_dept_id in dept.dept_id%type)
return varchar2 is
l_dname dept.d_name%type;
begin
select d_name into l_dname from dept where dept_id = i_dept_id;
return l_dname;
end;

ops$rchamarthi> /

Function created.

ops$rchamarthi> select get_dname_for_id (10) "Dept Name" from dual;

Dept Name
----------------
sales



Re: PLS-00306: wrong number or types of arguments in call to 'GET_DNAME_USING_ID' [message #399921 is a reply to message #399919] Fri, 24 April 2009 08:36 Go to previous messageGo to next message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Michel Cadot wrote on Fri, 24 April 2009 15:24
@...rajesh.chamarthi

Please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter) and use code tags.
Use the "Preview Message" button to verify.

Regards
Michel

Re: PLS-00306: wrong number or types of arguments in call to 'GET_DNAME_USING_ID' [message #399922 is a reply to message #399919] Fri, 24 April 2009 08:38 Go to previous messageGo to next message
joy_division
Messages: 4644
Registered: February 2005
Location: East Coast USA
Senior Member
Poor coding. What happens when you do

select get_dname_for_id (99) "Dept Name" from dual;
Re: PLS-00306: wrong number or types of arguments in call to 'GET_DNAME_USING_ID' [message #399923 is a reply to message #399907] Fri, 24 April 2009 08:39 Go to previous messageGo to next message
cookiemonster
Messages: 12422
Registered: September 2008
Location: Rainy Manchester
Senior Member
It's erroring because rajesh forget to declare outvar.

But I have to ask why are you returning 'sucess'?
Why don't you just return dname?

As for your original error - the code you are using to call the function is wrong. For us to tell you what's wrong with that code you'll have to show us what it is.

[EDIT] - waaaay too slow.

[Updated on: Fri, 24 April 2009 08:40]

Report message to a moderator

Re: PLS-00306: wrong number or types of arguments in call to 'GET_DNAME_USING_ID' [message #399924 is a reply to message #399919] Fri, 24 April 2009 08:42 Go to previous messageGo to next message
rajesh.chamarthi
Messages: 11
Registered: November 2006
Junior Member
The error might be in the way you are calling it.
If you need the output in Outvar, I think You need to create a procedure instead of a function.

Create or replace Procedure get_dname for_id
( i_dept_id in dept.dept_id%name,
o_dname out dept.dname%type ) IS
begin
select dname
into o_dname
from dept
where dept_id = i_dept_id;
End;

and then you could make a call this way..

---your code--

get_dname_for_id( l_dept_id,l_dname)

--and you can dbms_output l_dname to see if you are getting the result.






Re: PLS-00306: wrong number or types of arguments in call to 'GET_DNAME_USING_ID' [message #399925 is a reply to message #399921] Fri, 24 April 2009 08:47 Go to previous message
rajesh.chamarthi
Messages: 11
Registered: November 2006
Junior Member
HI MIchael,

I am unable to use the sql formatter .I get an error on page when I use that.
I will use code tags from now on.

Thanks,
Rajesh.
Previous Topic: ORA-12899: value too large for column string (actual: string, maximum: string)
Next Topic: data to be inactive
Goto Forum:
  


Current Time: Sun Dec 11 08:19:37 CST 2016

Total time taken to generate the page: 0.09738 seconds