Thursday, April 17, 2014

Simple Example with REPLACE

The REPLACE function allows you to change a string of characters to another string of characters and can accept three parameters:
 
(1)    Input column name.
(2)    Old string value.
(3)    New string value.
 
You can see what I mean in the example below, which I tested on Oracle 11.2:
 
SQL> create table directory_name
  2  (location varchar2(30))
  3  /
 
Table created.
 
SQL> insert into directory_name
  2  values('/batch/prod/dir1')
  3  /
 
1 row created.
 
SQL> insert into directory_name
  2  values('/batch/prod/dir2')
  3  /
 
1 row created.
 
SQL> select location from directory_name
  2  /
 
LOCATION
------------------------------
/batch/prod/dir1
/batch/prod/dir2
 
SQL> update directory_name
  2  set location = replace(location,'prod','test')
  3  /
 
2 rows updated.
 
SQL> select location from directory_name
  2  /
 
LOCATION
------------------------------
/batch/test/dir1
/batch/test/dir2
 
SQL>

ORA-01749

This was tested on an Oracle 11.2 database. You cannot GRANT or REVOKE object privileges to or from yourself:
 
SQL> show user
USER is "ANDREW"
SQL> grant select on blah to john
  2  /
 
Grant succeeded.
 
SQL> grant select on blah to andrew
  2  /
grant select on blah to andrew
                        *
ERROR at line 1:
ORA-01749: you may not GRANT/REVOKE privileges to/from
yourself
 
SQL> revoke select on blah from john
  2  /
 
Revoke succeeded.
 
SQL> revoke select on blah from andrew
  2  /
revoke select on blah from andrew
                           *
ERROR at line 1:
ORA-01749: you may not GRANT/REVOKE privileges to/from
yourself
 
SQL>