| Stored Procedure problem [message #38445] |
Thu, 18 April 2002 02:17  |
Chaitanya Sravanth
Messages: 10
Registered: April 2002
|
Junior Member |
|
|
CREATE OR REPLACE PROCEDURE SP_VALIDATE_LIB(
ErrorStatus OUT Number,
VUsername IN MUSERS.USER_NAME%TYPE,
VPassword IN MUSERS.USER_PASSWORD%TYPE,
Resultset OUT TYPES.CURSORTYPE)
AS
RCURSOR TYPES.CURSORTYPE;
LPASSWORD MUSERS.USER_PASSWORD%TYPE;
LSTATUS MUSERS.USER_STATUS%TYPE;
EXCEP EXCEPTION;
BEGIN
-- validate password here.
SELECT USER_PASSWORD INTO LPASSWORD FROM MUSERS WHERE UPPER(USER_NAME)=UPPER(VUsername);
IF LPASSWORD = VPassword THEN
-- Check whether User is inactive or active
SELECT USER_STATUS INTO LSTATUS FROM MUSERS
WHERE UPPER(USER_NAME)=upper(VUsername);
-- status Y means User is active
IF LSTATUS='Y' THEN
OPEN RCURSOR FOR select user_id,user_name from musers WHERE
upper(user_name)=upper(VUsername);
ErrorStatus:=0;
Resultset := RCURSOR;
ELSE
ErrorStatus := 2002;
END IF;
ELSE
ErrorStatus := 2001;
END IF;
EXCEPTION
WHEN NO_DATA_FOUND THEN
ErrorStatus := 1015;
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE(SQLCODE);
DBMS_OUTPUT.PUT_LINE(SQLERRM);
ErrorStatus:=3001;
END;
when executing this procedure it is giving the following problem -
SQL> exec SP_VALIDATE_LIB(:r,'pavani','seenu',:s);
begin SP_VALIDATE_LIB(:r,'pavani','seenu',:s); end;
*
ERROR at line 1:
ORA-03113: end-of-file on communication channel
I'm listing the table structure for reference -
CREATE TABLE MUSERS (
USER_ID NUMBER (3) PRIMARY KEY,
USER_NAME VARCHAR2 (20) NOT NULL,
USER_PASSWORD VARCHAR2 (20) NOT NULL,
USER_MAIL_ID VARCHAR2 (50) NOT NULL,
USER_TYPE CHAR (1) CHECK (USER_TYPE IN ('U','C','L')),
USER_STATUS CHAR (1) CHECK (USER_STATUS IN ('Y','N'))
);
I need the solution very urgently.
Thanks in advance.
|
|
|
|
|
|
| Re: Stored Procedure problem [message #39116 is a reply to message #38445] |
Tue, 18 June 2002 00:48  |
Manoj Krishna K
Messages: 1
Registered: June 2002
|
Junior Member |
|
|
I had also encountered the same problem while executing the stored procedures with out or inout parameters. The solution i found was,
declare r char(2);
s char(2);
begin
r := 'Y';
s := 'S';
SP_VALIDATE_LIB(:r,'pavani','seenu',:s);
end;
Please try this way.
I think this will help you solve ur problem.
With regards,
Manoj
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
