Re: Simple SQL question

I'm not sure what your question is, exactly, but you should be declaring a variable USERID before you BEGIN. Also, if there is any chance that your SELECT statement might return more than one row, the procedure will choke and die. Finally, if you have unique usernames, you don't need to enter the password to select the userid -- do you really have it set up so that one username can have two userids corresponding to two different passwords? That seems a little odd.

Natalka Roshak

In comp.databases.oracle.server Rookie <Rookie.Programmer_at_gte.net> wrote:
: I would like to use a procedure for a simple select statement, so that it
: can be called by the same name each time anywhere from an application. I
: would like to use something like this:

: CREATE OR REPLACE PROCEDURE GET_USER
: (
: USERNAME IN VARCHAR2,
: USERPASSWORD IN VARCHAR2,
: USERID OUT NUMBER
: )
 

: AS
 

: BEGIN
 
: SELECT TBL_USERS.USER_ID
: INTO USERID
: FROM TBL_USERS
: WHERE TBL_USERS.USER_NAME = USERNAME
: AND TBL_USERS.USER_PASSWORD = USERPASSWORD;
 
: END;
 
: Where each time the procedure get_user gets called from a java application,
: the user id will get returned when the username and the userpassword is
: given as parameters.
 

: Please help. Thanks in advance!

-- 
He stands proud in his uniform
He knows he's made it now
He's been told he'll be a hero
But he hasn't been told how
He receives his plastic key to heaven
Takes his holy vow
Then he turns,
Then he's gone
				--Peter Ulrich, "Taqaharu's Leaving"