VB Oracle stored procedure problem

From: Jagdip Singh Ajimal <jsa1981_at_hotmail.com>
Date: 16 Feb 2004 07:40:26 -0800
Message-ID: <c84eb1b0.0402160740.4fec6175@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 Received on Mon Feb 16 2004 - 09:40:26 CST