Home » SQL & PL/SQL » SQL & PL/SQL » Exiting an SQL Application (oracle 10g)
Exiting an SQL Application [message #317340] Wed, 30 April 2008 14:00 Go to next message
jack_chessire
Messages: 5
Registered: April 2008
Junior Member
Okay, here's the problem:
I'm trying to set it up so that, upon the execution of a PL/SQL statement, the .sql file I created will be exited.

so far, the file takes two inputs (username and password) and runs them through a PL/SQL procedure. I'd like it so that if there is an errior and they enter the wrong username and password, a message is returned (dbms_output.put_line("sorry. error"), but also exit the sql file instead of just returning to it and running as normal.

Is there a way to accomplish this?
Re: Exiting an SQL Application [message #317343 is a reply to message #317340] Wed, 30 April 2008 14:13 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
1/ whenever sqlerror exit
2/ raise_application_error instead of dbms_output

Regards
Michel
Re: Exiting an SQL Application [message #317345 is a reply to message #317343] Wed, 30 April 2008 14:39 Go to previous messageGo to next message
jack_chessire
Messages: 5
Registered: April 2008
Junior Member
Wow...
I didn't think it'd be that easy!
Thank you very much for the reply!
Now, do I just put the file name at the end or do I have to do anything special? I looked up an example fo this and the syntax for the example said SQL.SQLCODE...
Therefore, if my SQL file I want to exit is named TESTDATA, would I have to put:

whenever sqlerror exit SQL.TESTDATA


?
Re: Exiting an SQL Application [message #317348 is a reply to message #317345] Wed, 30 April 2008 15:00 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
My post answered your question.
You change the question, the solution is different.
So post the whole and actual problem.

Regards
Michel
Re: Exiting an SQL Application [message #317349 is a reply to message #317348] Wed, 30 April 2008 15:47 Go to previous messageGo to next message
jack_chessire
Messages: 5
Registered: April 2008
Junior Member
All right...

I have a file called test_data.sql

In the file is the following:
accept userName prompt 'Enter Username:';
accept passWord prompt 'Enter Password:';
variable loginResult varchar2(10);
exec :loginResult := LOGIN_AND_UPDATE_FUNCTIONS.LOGIN_EVALUATION('&userName', '&passWord');
!cls
prompt Welcome;


The second file is test_sql.sql

In the file is the following:
create or replace package LOGIN_AND_UPDATE_FUNCTIONS
as
function LOGIN_EVALUATION(userName in VARCHAR2, passWord in VARCHAR2) return VARCHAR2;
end LOGIN_AND_UPDATE_FUNCTIONS;
/

create or replace package body LOGIN_AND_UPDATE_FUNCTIONS
as
function LOGIN_EVALUATION(userName in VARCHAR2, passWord in VARCHAR2)
return VARCHAR2
is userConstant VARCHAR(10);
usernameCONFIRM VARCHAR(30);
passwordCONFIRM VARCHAR(30);
begin
select USERID, USERPASS
into usernameCONFIRM, passwordCONFIRM
from GAMEUSER
where USERID = userName
and USERPASS = passWord;
userConstant := usernameCONFIRM;
return (userConstant);
exception
when NO_DATA_FOUND then 
raise_application_error(-20001, 'User username/password not found');
whenever sqlerror exit test_data.sql;
end LOGIN_EVALUATION;
end LOGIN_AND_UPDATE_FUNCTIONS;
/


The idea of the login function is to evaluate the inputed username and password, and if there is an error, it will return an error message and exit test_data.sql.

[Updated on: Wed, 30 April 2008 17:24]

Report message to a moderator

Re: Exiting an SQL Application [message #317366 is a reply to message #317340] Wed, 30 April 2008 18:24 Go to previous messageGo to next message
jack_chessire
Messages: 5
Registered: April 2008
Junior Member
My mistake; I have it working now. Thanks for the help again.
Re: Exiting an SQL Application [message #317394 is a reply to message #317366] Thu, 01 May 2008 00:42 Go to previous message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You should post the solution for others.
The principle is that "whenever sqlerror exit" is a SQL*Plus command and must be in test_data.sql script and not in PL/SQL procedure.

Regards
Michel
Previous Topic: deleting 10 million records form a table
Next Topic: Can not see data in GTT although in same session
Goto Forum:
  


Current Time: Sat Dec 10 10:57:40 CST 2016

Total time taken to generate the page: 0.04476 seconds