Cannot call oracle stored procedure and function

Question

Might be too simple question to ask, but I do need help.

I am creating a stored procedure in Oracle 10g, but I cannot call it. I am using SQL Developer to manage the database.

CREATE OR REPLACE
FUNCTION check_login 
  (username IN VARCHAR2, pwd IN VARCHAR2)
  RETURN VARCHAR2
IS
  isUserValid INTEGER;
BEGIN
  SELECT Count(*) INTO isUserValid
  FROM users
  WHERE Username = username AND PASS_WORD = pwd;
  return isUserValid;
END;

I have tried this also:

CREATE OR REPLACE
PROCEDURE check_login 
  (username IN VARCHAR2, pwd IN VARCHAR2, RESULT OUT INTEGER)
IS
  isUserValid INTEGER;
BEGIN
  SELECT Count(*) INTO isUserValid
  FROM users
  WHERE Username = username AND PASS_WORD = pwd;
  RESULT := isUserValid;
END;

Parsing both does not give any error message. I used following syntax to call them:

BEGIN 
  check_login('admin', 'admin'); 
END;

AND

EXECUTE check_login('admin', 'admin');

I get this error message....

PLS-00221: 'CHECK_LOGIN' is not a procedure or is undefined
PL/SQL: Statement ignored

The SELECT statement inside both works fine if run directly.

Am I doing something wrong?

Solution 1

If you want to execute a function you have to collect the return value into a variable.

So you need to define a variable and execute function to return into the variable as below

and run it using the run Script option not the Run Statement option.

variable ret varchar2(20);

execute :ret:=check_login(dd,dd);

select :ret from dual

Or if you do it from plsql

declare  v_ret varchar2(100); 
begin

  v_ret:=check_login(a,b); 
end;

Solution 2

I find the easiest way to call a function is just selecting the function from dual eg-

select check_login('admin', 'admin') from dual;

Note the error message said "No procedure' :).

Comments

[Kumar Kush]

Thanks Joseph! That worked. Now, how to shrink the three lines to one line only so that it can be called from PHP code in one line using oci_execute()?

[Kumar Kush]

The second block you mentioned, works but only displays anonymous block completed. Does not return a value.