Executing a Function in Oracle

Hi,

I have created a Package Body and Package Spec for a function to select a username and a password from a table and return the username.

The code i have created is this:-
CREATE OR REPLACE PACKAGE BODY USER_LOGIN
AS

FUNCTION user_select (USERNAME_IN VARCHAR2,
PASSWORD_IN VARCHAR2)
RETURN VARCHAR2 IS
USERNAME_OUT VARCHAR2(12);
BEGIN
SELECT USERNAME
INTO USERNAME_OUT
FROM USERS
WHERE USERNAME = USERNAME_IN
AND PASSWORD = PASSWORD_IN;
RETURN USERNAME_OUT;
END user_select;

END USER_LOGIN;
/

The package body and spec compiles successfully but i am having trouble when i execute this function. I am trying to do this :-

VARIABLE RETVAL VARCHAR2(12)

EXEC User_login.user_select('HELLO','HELLO',:RETVAL );

but i am getting the following error

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

Can anyone please help me?

Yours is a function, not a procedure, so call it as:

Do i not need to worry about the return parameter when using the
SELECT USER_LOGIN.user_select('PIPPO', 'PLUTO') FROM dual; statement?

Also is the same code to be used in Oracle 10G?

Thanks!

I've tested in in 10g Express Edition , about the parameter ... say with me: this is a function, not a stored procedure.

declare
result varchar2(10);
BEGIN
result:= USER_LOGIN.user_select('hello','hello1:'); DBMS_OUTPUT.PUT_LINE(result);
END;
/

declare
result varchar2(10);
BEGIN
result:= USER_LOGIN.user_select('hello','hello1:'); DBMS_OUTPUT.PUT_LINE(result);
END;
/

or

VARIABLE result varchar2(10)
EXEC :result := USER_LOGIN.user_select('hello','hello1:');

print result