js*****@hotmail.com (Jagdip Singh Ajimal) wrote in message news:<c8**************************@posting.google. com>...
I have a new VB 6 project, and I have successfully created a data
environment that connects to my oracle server (they don't make this
easy!!).
In my oracle server, I have the following stored procedure:
CREATE OR REPLACE FUNCTION checkLogin(checkUsername CHAR,
checkPassword CHAR) RETURN BOOLEAN IS
CURSOR filterUsername IS
SELECT * FROM AllEmployees
WHERE allemployees.username = checkUsername;
BEGIN
FOR eachFilterUsername IN filterUsername LOOP
IF (eachFilterUsername.password = checkPassword) THEN
RETURN TRUE;
EXIT;
END IF;
END LOOP;
RETURN FALSE;
END;
/
I know this works as, when I run
SET SERVEROUTPUT ON
BEGIN
IF checkLogin(?Doc1', 'Doc1Pass') THEN
DBMS_OUTPUT.PUT_LINE(?Login ok');
ELSE
DBMS_OUTPUT.PUT_LINE(?Unauthorised login');
END IF;
END;
/
I get the right output.
MY PROBLEM. I have added the data environment to my project, and also
added this stored procedure. When I try to run the following code,
Set dataEnviron = New dataEnv
Set DBconn = dataEnviron.OracleConn
DBconn.Open
Dim checkLoginCmd As New ADODB.Command
Dim returnBoolean As Boolean
returnBoolean = dataEnviron.AJIMALJ0_CHECKLOGIN(username, password)
checkLogin = returnBoolean
I get the following error
Run-time error '-2147467259 (80004005)':
[Oracle][ODBC][Ora]ORA-06550: line 1, cloumn 13:
PLS-00382: expression is of wrong type
ORA-06550: line 1, column 7:
PL/SQL: Statement ignores
I am almost 100% sure this is because of the way vb passing the
variables to oracle, but I am unsure how to fix it. I have checked the
parameters in teh dataenvironment and they all seem right.
Any help good be greatly appriciated
Jagdip Singh Ajimal
You never mention the datatypes you specify for username and password
in your VB code, perhaps that is the issue.
A simpler implementation of your function....could probably be
improved further...
CREATE OR REPLACE FUNCTION checkLogin(p_checkUsername in VARCHAR2,
p_checkPassword in VARCHAR2) RETURN BOOLEAN
IS
L_CNT NUMBER;
BEGIN
SELECT COUNT(*) into L_CNT
FROM AllEmployees
WHERE username = p_checkUsername
AND password = p_checkPassword;
IF L_CNT > 0 THEN
RETURN TRUE;
ELSE
RETURN FALSE;
END IF;
END;
/
