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 3 11092
"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 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;
/ 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. This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
by: kristoff plasun |
last post by:
I have a problem with a C++ DCOM application that
prints Crystal Reports with data from Oracle.
The SQL query is relatively complex but when
the report is printed from the Crystal Reports...
|
by: Sezhman |
last post by:
I am using Oracle Database, Can anyone tell me, how to connect to the oracle
database and run the stored procedure in oracle.
I am facing some problem in this. please guide me..
--
Sezhman
|
by: robin via SQLMonster.com |
last post by:
I've tried several different way to execute a oracle stored
procedure from a DTS package but to no avail.
I have a Linked Server setup which does bring back Oracle tables from the
server when I...
|
by: totierne |
last post by:
comp.databases.ms-access,
I want to know how to use Oracle views with session variables in
Access.
The parameterised views in access, are migrated to views with per
session
variables.
The...
|
by: Tom |
last post by:
Looking for some help with stored procedure call issues. Conceptually, I
need to pass a data structure as the sole parameter to the Oracle stored
procedure. Sounds simple enough....but how?
...
|
by: jehugaleahsa |
last post by:
Hello:
I am working with Oracle .NET Stored Procedures. I would like to know
how to return the results of a SELECT statement. I have tried
returning a OracleRefCursor and a DataTable, but...
|
by: Jagdip Singh Ajimal |
last post by:
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...
|
by: Gloops |
last post by:
Hello everybody,
Is anyone able to give me some indications about how to develop an
Access interface for an Oracle database ?
I dispose of Access 2003 (11.6566.8107) SP2, Oracle 9i 9.2.0.1.0...
|
by: rajkumarbathula |
last post by:
Hi
I was struck up with big problem of executing a oracle stored procedure in C#.
Inputs 1:I am having 1 oracle stored procedure eg. myProc() and this procedure takes 1 INOUT parameter. of type...
|
by: emmanuelkatto |
last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud.
Please let me know.
Thanks!
Emmanuel
|
by: nemocccc |
last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
|
by: Sonnysonu |
last post by:
This is the data of csv file
1 2 3
1 2 3
1 2 3
1 2 3
2 3
2 3
3
the lengths should be different i have to store the data by column-wise with in the specific length.
suppose the i have to...
|
by: Hystou |
last post by:
There are some requirements for setting up RAID:
1. The motherboard and BIOS support RAID configuration.
2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing,...
| |