| Stored Proc problem [message #36442] |
Wed, 28 November 2001 02:42  |
Elav
Messages: 7
Registered: October 2001
|
Junior Member |
|
|
Hi there,
I want to write a Stored Procedure which will return me more than one row at a time. I wrote it in MS SQL and it works fine. But it gives me error when tried in Oracle. Here is the query.
CREATE OR REPLACE PROCEDURE PROC_UserLogin (userID varchar2)
AS
BEGIN
SELECT USERID = A.dup_user_ID,
USERNAME = A.dup_user_name,
USERTYPE = A.dup_user_type,
LOGINTIME = B.dat_event_dt,
LOGOUTTIME = C.dat_event_dt
FROM des_user_profile A, des_gen_audit_trail B,
des_gen_audit_trail C
WHERE A.dup_user_id like userID and
B.dat_event_type = 'LOGIN' and
B.dat_event_status = 'PASS' and
B.dat_user_ID = A.dup_user_ID and
C.dat_event_type = 'LOGOUT' and
C.dat_event_status = 'PASS' and
C.dat_user_ID = B.dat_user_ID;
END PROC_UserLogin;
I got the error "PLS-00428: an INTO clause is expected in this SELECT statement". I know it'll return me one row. But i want to return more than one row at a time.
Need your help in this.
Rgds
Elav
----------------------------------------------------------------------
|
|
|
|
| Re: Stored Proc problem [message #36443 is a reply to message #36442] |
Wed, 28 November 2001 03:01  |
tinel
Messages: 42
Registered: November 2001
|
Member |
|
|
in oracle goes like this:
CREATE OR REPLACE PROCEDURE PROC_UserLogin (userID VARCHAR2)
AS
/*declaration of
USERID,USERNAME, USERTYPE, LOGINTIME, LOGOUTTIME
*/
BEGIN
SELECT A.dup_user_ID, A.dup_user_name, A.dup_user_type, B.dat_event_dt, C.dat_event_dt
INTO USERID,USERNAME, USERTYPE, LOGINTIME, LOGOUTTIME
FROM des_user_profile A, des_gen_audit_trail B, des_gen_audit_trail C
WHERE A.dup_user_id LIKE userID AND
B.dat_event_type = 'LOGIN' AND
B.dat_event_status = 'PASS' AND
B.dat_user_ID = A.dup_user_ID AND
C.dat_event_type = 'LOGOUT' AND
C.dat_event_status = 'PASS' AND
C.dat_user_ID = B.dat_user_ID;
END PROC_UserLogin;
you will have to declare USERID,USERNAME, USERTYPE, LOGINTIME, LOGOUTTIME
----------------------------------------------------------------------
|
|
|
|
| Re: Stored Proc problem [message #36446 is a reply to message #36442] |
Wed, 28 November 2001 03:24 |
Suresh Vemulapalli
Messages: 624
Registered: August 2000
|
Senior Member |
|
|
if you want to return more than one row from procedure, you have to use either ref cursors or pl/sql tables(nested tables,varrays etc) .In pl/sql you must fetch value into variable or record type using sql statement.
----------------------------------------------------------------------
|
|
|
|
| Re: Stored Proc problem [message #38521 is a reply to message #36442] |
Wed, 24 April 2002 11:47 |
Satyajeet S
Messages: 1
Registered: April 2002
|
Junior Member |
|
|
I am not sure whether this works. tried it in a similar problem. Below is the error that it gives me.
SQL> show errors;
Errors for PROCEDURE RAISESALARY:
LINE/COL ERROR
-------- -----------------------------------------------------------------
21/3 PLS-00103: Encountered the symbol "INTO" when expecting one of
the following:
. ( , * @ % & - + / mod rem <an identifier>
<a double-quoted delimited-identifier> an exponent (**) as
from ||
24/30 PLS-00103: Encountered the symbol ")" when expecting one of the
following:
. ( * @ % & - + ; / for mod rem an exponent (**) and or group
having intersect minus order start union where connect ||
Please Advise!!!
Thanks
Satyajeet
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
