Unable to use REPLACE function in PLSQL

Question

I was wondering what is the appropriate way to call the REPLACE function described here, since I've created the statement below to test it but I'm getting an error:

DECLARE
 templateMessage3 VARCHAR2(50);
BEGIN
 templateMessage3 := 'Dear Mr./Madam FNAME';
  replace(templateMessage3, 'FNAME', 'Lilly');
  DBMS_OUTPUT.PUT_LINE(templateMessage3);
END;
/

Error:

PLS-00221: 'REPLACE' is not a procedure or is undefined

I am using Oracle 11g web interface.

Peter Lang · Accepted Answer · 2014-12-01 12:24:53Z

13

REPLACE is a function, not a procedure, so use the following syntax:

templateMessage3 := replace(templateMessage3, 'FNAME', 'Lilly');

edited Dec 1, 2014 at 12:24

answered Apr 27, 2014 at 16:24

Peter Lang

55.7k29 gold badges153 silver badges165 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

FJT · Accepted Answer · 2014-04-27 16:17:11Z

0

REPLACE is a sql function

So you'd need something like:

SELECT replace(:templateMessage3, 'FNAME', 'Lilly')
INTO templateMessage3
FROM dual;

(I don't have an Oracle install, so can't double check the syntax, but should be enough to get you started)

answered Apr 27, 2014 at 16:17

FJT

2,0831 gold badge16 silver badges14 bronze badges

5 Comments

Bob Jarvis - Слава Україні Over a year ago

@PeterLang: works fine for me, although I'll agree that my SQLFiddle is slightly different - but not in ways that I think make much difference. YMMV. Share and enjoy.

Bob Jarvis - Слава Україні Over a year ago

@FionaT and anyone else who needs an Oracle instance for one-shot playing around - you might want to get familiar with SQLFiddle. When you first bring it up you'll be in MySQL - but if you click on the database name above the schema panel you can change the database to on of a myriad different types - just select Oracle 11g R2 and you'll be in business. There are some limitations, such as not being able to use DBMS_OUTPUT - but there are ways to work around that. It's a handy tool to have around. Share and enjoy.

Peter Lang Over a year ago

@BobJarvis: Sorry if my comment was unclear. This solution works, but the slower (tried it) and more complex switch to SQL is not necessary.

Bob Jarvis - Слава Україні Over a year ago

@PeterLang: agreed that the switch to SQL is not needed. Not sure why people do this - perhaps required by older version of Oracle? But certainly not since 9i.

FJT Over a year ago

Agree with PeterLang that if there's no need for SQL then it's overcomplicating things to use it. Answered the question from memory (haven't used Oracle for a couple of years) as it was Sunday evening and I thought the OP might not get much response!

Collectives™ on Stack Overflow

Unable to use REPLACE function in PLSQL

2 Answers 2

Comments

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related