plsql - the update query is not working in oracle sql developer , what is the error?

Question

DECLARE
   @A varchar2(20);
BEGIN
     @A := 'abc555';

UPDATE TEST Set LINK = Replace(LINK,Substring(LINK,1,CHARINDEX('ESSAIS-CGMP',LINK)-1),@A);

END;

Answer 1

You are using Oracle not SQL server.

SUBSTRING and CHARINDEX are not valid Oracle functions; you want SUBSTR and INSTR instead.

And, @ is not a valid first character for a non-quoted identifier, just use A rather than @A.

DECLARE
  A varchar2(20);
BEGIN
  A := 'abc555';

  UPDATE TEST
  Set LINK = Replace(LINK,Substr(LINK,1,INSTR(link,'ESSAIS-CGMP')-1),A);
END;
/

Then, for the sample data:

CREATE TABLE test ( link VARCHAR2(50) );

INSERT INTO test ( link ) VALUES ( 'hereESSAIS-CGMP' );

The result is:

SELECT * FROM test;

| LINK               |
| :----------------- |
| abc555ESSAIS-CGMP |

db<>fiddle here