CODEWITHSUNDEEP
oracle-databasestored-proceduresplsql
Azza
Azza

Reputation: 41

exception is not raised in stored procedure

when the following code is running exception statements are not rising automatically by the database server it's Happen when I enter wrong userid value or when the value is null , I don't want to use RAISE LOGIN_DENIED; explicitly in my code , so what do you think? am I missing something?

CREATE OR REPLACE PROCEDURE user_auth(
    userid IN st_az.st_name%type ,
    pass OUT st_az.st_pass%type ,
    message OUT varchar2 ,
    err_msg OUT varchar2  ) IS
BEGIN 
    message:= 'login is done successfully';
    err_msg:= 'Login Denied .. Please Try Again!';
    SELECT st_pass INTO  pass FROM st_az WHERE st_name = userid ;
    dbms_output.put_line(message);
EXCEPTION  
     WHEN LOGIN_DENIED  THEN 
     dbms_output.put_line(err_msg); 
END user_auth;

Upvotes: 1

Views: 164

Answers (2)

Ashish Patil
Ashish Patil

Reputation: 4624

Your original code of the question needs some modification. The actual parameter that corresponds to an OUT formal parameter must be a variable; it cannot be a constant or an expression.

Now, if you don't want to use LOGIN_DENIED then you can either try using NO_DATA_FOUND or simple generalized OTHERS in exception block. The code may be like this--

CREATE OR REPLACE
PROCEDURE user_auth(
    userid IN st_az.st_name%type ,
    pass OUT st_az.st_pass%type ,
    MESSAGE OUT VARCHAR2 ,
    err_msg OUT VARCHAR2 )
AS
  message1     VARCHAR2(50);
  err_message1 VARCHAR2(50);
BEGIN
  message1    := 'login is done successfully';

  err_message1:= 'Login Denied .. Please Try Again!';

  SELECT st_pass INTO pass FROM st_az WHERE st_name = userid ;

  MESSAGE:= message1;

  dbms_output.put_line(MESSAGE);

EXCEPTION
WHEN OTHERS THEN
  err_msg:= err_message1;

  dbms_output.put_line(err_msg);
END user_auth;

Now testing above code:--

create table st_az(st_name varchar2(10),st_pass varchar2(10));

insert into st_az values ('aa','aa');
insert into st_az values ('bb','bb');
commit;

Creating anonymous block and call procedure:-

DECLARE
  MESSAGE VARCHAR2(50);
  err_msg VARCHAR2(50);
  pass    VARCHAR2(10);
BEGIN
  user_auth('cc',pass,MESSAGE,err_msg);
END;

Upvotes: 1

Tatiana
Tatiana

Reputation: 1499

If you want to find info in st_az table and raise an error when there is no such row, you need NO_DATA_FOUND exception

CREATE OR REPLACE PROCEDURE user_auth(
    userid IN st_az.st_name%type ,
    pass OUT st_az.st_pass%type ,
    message OUT varchar2 ,
    err_msg OUT varchar2  ) IS
BEGIN 
    message:= 'login is done successfully';
    err_msg:= 'Login Denied .. Please Try Again!';
    SELECT st_pass INTO  pass FROM st_az WHERE st_name = userid ;
    dbms_output.put_line(message);
EXCEPTION  
     WHEN NO_DATA_FOUND THEN 
     dbms_output.put_line(err_msg); 
END user_auth;

Upvotes: 3

Related Questions