Click here to Skip to main content
15,867,330 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
Im trying to write a procedure that updates the domain of an email (example: converting Email123@gmail.com into Email123@hotmail.com when i pass 2 strings gmail.com and hotmail.com). I must have a cursor in my procedure
create of replace procedure pr_update_email
(Old_Email Varchar2, New_Email Varchar2)
    As
    V_OldDomain Varchar2(50);
      Cursor C_Domains IS Select Email_Address
                From Customer
                where Email_Address Like '%@'||Old_Email;       
          Begin
          Open C_Domains;
          Fetch C_Domains INTO V_OldDomain; 
          While C_Domains %Found loop
                    Update Customer
                    Set Email_Address = regexp_replace(Email_Address, '^(.*@.*)'||Old_Email||'\1'||New_Email)
                    WHERE Email_Address LIKE V_OldDomain;   
          Fetch C_Domains into New_Email;
          End Loop;
  Close C_Domains;
End pr_update_email;
/
Show Errors;


getting errors: 19/11 PL/SQL: SQL Statement ignored

19/32 PLS-00403: expression'NEW_EMAIL' cannot be used as an INTO-target of a SELECT/FETCH statement

What I have tried:

i don't know what to do.
i'm really stuck.
Posted
Updated 20-Feb-20 2:58am

The error message seems to be quite clear...

More details you'll find here: PLS-00001 to PLS-01919[^]

PLS-00403: expression 'string' cannot be used as an INTO-target of a SELECT/FETCH statement

    Cause: A FETCH statement was unable to assign a value to an assignment target in its INTO list because the target is not a legally formed and declared variable. For example, the following assignment is illegal because 'Jones' is a character string, not a variable: FETCH my_cur INTO 'Jones';

    Action: Check the spelling and declaration of the assignment target. Make sure that the rules for forming variable names are followed.
 
Share this answer
 
Quote:
SQL
Fetch C_Domains INTO V_OldDomain;
...
Fetch C_Domains into New_Email;
It looks to me like your second Fetch is targeting the wrong variable.

I'd also question why you claim you need a cursor, when you could accomplish this with a single UPDATE statement:
SQL
UPDATE Customer
SET Email_Address = regexp_replace(Email_Address, '^(.*@.*)'||Old_Email||'\1'||New_Email)
WHERE Email_Address Like '%@'||Old_Email;
 
Share this answer
 
Comments
Maciej Los 20-Feb-20 12:44pm    
Correct final note!

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900