Cannot call oracle stored procedure and function
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?
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;
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' :).
精彩评论