Re: Request PL/SQL Help

> I am trying to parse a string that contains an email address of a certain domain, say "abc.com". The string could contain many
> addresses, and in any order. I have written a PL/SQL program to do it, but it's not working quite right. Can someone spot my
> error? Here is the PL/SQL, and some test data.
>
> CREATE OR REPLACE FUNCTION getourmail( email_in IN TEST.EMAIL_ADDR%TYPE,domain_in IN VARCHAR2)
> RETURN VARCHAR2
> IS
> end_of_address NUMBER := 0;
> beg_of_address NUMBER := 0;
> string VARCHAR2(1000) := email_in;
> BEGIN
> end_of_address := INSTR(email_in,domain_in,1) + LENGTH(domain_in) - 1;
> string := substr(email_in,1,end_of_address);
> beg_of_address := INSTR(string,',',-end_of_address,1);
> RETURN SUBSTR(string,beg_of_address,end_of_address - beg_of_address);
> EXCEPTION
> WHEN NO_DATA_FOUND THEN RETURN NULL;
> END;
>
> create table test
> (
> email_addr varchar2(80)
> );
>
> insert into test values
> ('jim_at_abc.com,jim_at_pqr.com');
>
> insert into test values
> ( 'jim_at_pqr.com,jim_at_abc.com');
>
> insert into test values
> ( 'jim_at_pqr.com,jim_at_abc.com,jim_at_tuv.com');
>
> commit;
>
> select email_addr,getourmail(email_addr,'abc.com') from test

This is a stored procedure ... not a function. Change your create statement. This may not be the only problem but it is the most obvious.

In the future please post the error message. It will make life much easier for anyone trying to help you.

Daniel A. Morgan Received on Wed Jan 24 2001 - 00:00:04 CST