jsa1981_at_hotmail.com (Jagdip Singh Ajimal) wrote in message news:<c84eb1b0.0402160740.4fec6175_at_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
