Term: REPLACE

Definition:
In Oracle PL/SQL, the term REPLACE appears both as a SQL function and as a keyword in subprogram definitions.

1. As a SQL function

REPLACE is an Oracle function which is used to replace all occurrences of a character or characters with an alternate set of characters. Note that the alternate set of characters is optional. If the alternate set of characters is not provided, Oracle replaces the set of characters with NULL.

Example Syntax:

REPLACE ([string], [string to replace], [replacement string] )

Example Usage:

The SQL queries below demonstrate the use of REPLACE as character function.

SQL> SELECT REPLACE('WWW.PSOUG.ORG','W','X') FROM DUAL;



REPLACE('WWW.

-------------

XXX.PSOUG.ORG



SQL> SELECT REPLACE ('WWW.PSOUG.ORG','WWW','X') FROM DUAL;



REPLACE('WW

-----------

X.PSOUG.ORG

2. As a Keyword in Object Definitions

REPLACE is an optional keyword used in object definitions (DDL) to override the older objet definition with a new one. It retains the access privileges of the object during the definition modification process. If the object is dropped and recreated, however, its privileges are lost.

Example Syntax:

CREATE [OR REPLACE] [PROCEDURE | FUNCTION | TRIGGER | PACKAGE | TYPE ] [NAME]  [IS |AS]

<object body>

END;

Example Usage:

The CREATE statement below creates a procedure P_GET_SAL using the REPLACE option.

CREATE OR REPLACE PROCEDURE P_GET_SAL 

IS

L_SAL NUMBER;

BEGIN

SELECT SALARY

INTO L_SAL

FROM EMPLOYEE

WHERE EMPNO = 100;

END;