VB Oracle stored procedure problem

[Jagdip Singh Ajimal]

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

[Jim Kennedy]

"Jagdip Singh Ajimal" <js*****@hotmail.com> wrote in message
news:c8**************************@posting.google.c om...

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

I usually use oo4o and have no problems and it is easy to use. (also better
performance) Try not having it return a Boolean. Have it return 'T' or 'F'
or 1 and 0. That will probably work.
Jim

[Dave]

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;
/

[Jagdip Singh Ajimal]

da******@hotmail.com (Dave) wrote in message news:<82**************************@posting.google. com>...

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;
/

Thank you both for your help. I changed the return value in the oracle
PL/SQL from TRUE FALSE to 'T' and 'F'. Now I get T and F returned
correctly, so I can atleast continuing programming.

Jagdip Singh Ajimal

------------------
In this cruel world, isn't it satisfying that at least us Computer
Scientists keep are humanity and try to help each other out.