CODEWITHSUNDEEP
sqlplsqloracle10g
kumar
kumar

Reputation: 89

how to store a select statement return value in a variable

if select statement successfully execute it returns 1 else 0 right not i want to store that return value in a variable so that i can use that variable in further sql 

 set serveroutput on  
declare  
a number;  
begin  
  a:=select instr('&email','@') as email_in from dual;  
  if(a)  
  then  
    dbms_output.put_line('Valid Email');  
  else  
    dbms_output.put_line('Please Enter a valid Email');  
  end if;  
end;

here what i am trying is if user entered email contain @ then the select query executed successfully and return position of @ in that string, other wise it will give 0
but it is giving me an error can any one please help me out...
thank you in Advance..

Upvotes: 0

Views: 668

Answers (2)

vishnu sable
vishnu sable

Reputation: 358

simply write function to validate email it will return 0 if doesn't have @ and position of @ if it is there.

create or replace function check_email(ip_emailid varchar2)
  return number
is
begin
 return instr(ip_emailid,'@');
end;

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269563

I think you want to express this as:

select (case when instr('&email', '@') > 0 then 1 else 0 end)
into a
from dual;  
if (a = 1) then  
  dbms_output.put_line('Valid Email');  
else  
  dbms_output.put_line('Please Enter a valid Email');  
end if;

You don't need the select for this purpose, but I'm leaving it in. I Or, just do:

if ('&email' like '%@%') then  
  dbms_output.put_line('Valid Email');  
else  
  dbms_output.put_line('Please Enter a valid Email');  
end if;

Note: a validity check for an email should involve much more than the at-sign.

Upvotes: 1

Related Questions