Quick Search:
CODE
Oracle PL/SQL Code Library
JOBS
Find Or Post Oracle Jobs
FORUM
Oracle Discussion & Chat
 The Oracle PL/SQL REPLACE Keyword      [Return To Index] Jump to:  
  Looking for the original pages? (formerly called "Morgan's Library") You can find them here.

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;



Related Links:

Related Code Snippets:
 
Home      :      Code Library      :      Sponsors      :      Privacy      :      Terms of Use      :      Contact Us 189 users online    © 2009 psoug.org
PSOUG LOGIN
Username: 
Password: 
Forgot your password?