Verification Loop

The idea of the program below is to create a sql "module" (just a .sql file) that will loop and ask for a user id until the user enters the correct one. Here is the code I have thusfar:

set serveroutput on;

accept username prompt 'Enter user name here: ';
accept password prompt 'Enter password here: ';

declare
usernameCONFIRM VARCHAR(30);
passwordCONFIRM VARCHAR(30);
begin
select USERID into usernameCONFIRM from GAMEUSER where USERID = '&username';
select USERPASS into passwordCONFIRM from GAMEUSER where USERPASS = '&password';
exception
when NO_DATA_FOUND then
undefine username;
undefine password;
@login.sql;
end;

The main problem I'm seeing is that the two variables I ask for (username and password) aren't clearing out.

If your sql script is looping over and over again then you need to undefine those 2 variables at the start of the script not the end of the script.

I did that but now I'm getting a new error:

SP2-0042: unknown command "end" - rest of line ignored.
undefine username;
*
ERROR at line 9:
ORA-06550: line 9, column 10:
PLS-00103: Encountered the symbol "USERNAME" when expecting one of the
following:
:= . ( @ % ;
ORA-06550: line 12, column 18:
PLS-00103: Encountered the symbol "ON" when expecting one of the following:
:= . ( @ % ; not null range default character

Could it have to be with how I'm calling the .sql file?

You might want to try


But you might need to reprogram your script in order to use that and it gets kind of complicated. I think you might need to consider to write a store procedure from your code then call the store procedure.

I guess...
This is all so frustrating. Why does SQL have to be such a useless (in this regard) program?

Maybe you actually need to study the fine Oracle manuals and tutorials on the subject and learn how to use it ... and the differences between SQL*PLus and PL/SQL.

In the script you posted you are mixing the two.

Even though I see no purpose to your script and beleive the design of the "GAMEUSER" table is flawed, this would be the correct sequence of statements for your script:




PS: The login.sql script (if present) automatically executes when you start sqlplus.

...
Thank you for your help, but the insults were unecessary.
I'm just starting in this so I think you can cut me a bit of a break. There's no reason to insult someone for not knowing something.

Also, I'm t rying to loop it.. this just runs it through one time.

Sorry you took my suggestion to actually study the languages you intend to use as an 'insult'.

If you had read any of the manuals or tutorials, you would have found out that there is no 'looping' functionality in SQL*PLus, only PL/SQL provides it.

Also, we could help you best if you would state the requirements you need to meet and the design of the tables you will be using.

<_<
I AM trying to learn about it...
I know that SQL can't do a loop, which is why the PL/SQL block exists.

the 'game user' table contains a username and password.
The purpose of this sql file is to evaluable the username and password, making sure that the correct username corresponds to the correct password.

I'm trying to set it up so that if the user fails to give hte proper login information, they have a chance to log in again, hence, the "loop".
However, if it'd be best to make a finite loop, I'm willing to do that as well.

Because you don't know it and it's NOT procedural, you'll have to think by sets of data (if this regard means looping)

That doesn't help me with the problem I'm' having...

The loop you seek would have to be at the SQL*Plus level surrouding the PL/SQL block and therefore cannot be implemented due to the fact stated: there is no 'looping' functionality in SQL*PLus.

Also, in order to execute any script and/or procedure, you already need to be login to the database with a valid username/password.

note that what you're trying to do is usually done at application level (written with any language, php, java ...) which gets a username and password, verifies them against the database and either goes on with the app or sends the user back to the login form, no looping, you are mis describing the desired behavior when you talk about looping.