Oracle built-in functions: SUBSTR and INSTR

If we want to restore a RMAN backup to a different host and the path in the new host are different from those in the original host, we will have to use the 'set newname for datafile ' statment to change the file name. This test case demonstrated the use of SUBSTR and INSTR function and how to use them to generate the 'set newname for datafile' statement dynamically.

 

denis@TEST10G> @substr_instr.sql
denis@TEST10G> rem substr_instr.sql
denis@TEST10G> rem   ref: http://www.psoug.org/reference/substr_instr.html
denis@TEST10G>
denis@TEST10G> set echo on
denis@TEST10G>
denis@TEST10G> SELECT SUBSTR('Take the first four characters', 1, 4) FIRST_FOUR
  2  FROM dual;

FIRS
----
Take

denis@TEST10G>
denis@TEST10G> SELECT SUBSTR('Take the first four characters', 16, 4) MIDDLE_FOUR
  2  FROM dual;

MIDD
----
four

denis@TEST10G>
denis@TEST10G>
denis@TEST10G> SELECT SUBSTR('Take the first four characters', 16) SIXTEEN_TO_END
  2  FROM dual;

SIXTEEN_TO_END
---------------
four characters

denis@TEST10G>
denis@TEST10G> SELECT SUBSTR('Take the first four characters', -4) FINAL_FOUR
  2  FROM dual;

FINA
----
ters

denis@TEST10G>
denis@TEST10G>
denis@TEST10G> SELECT INSTR('Take the first four characters', 'a', 1, 1) FOUND_1
  2  FROM dual;

   FOUND_1
----------
         2

denis@TEST10G>
denis@TEST10G> SELECT INSTR('Take the first four characters', 'a', 1, 2) FOUND_2
  2  FROM dual;

   FOUND_2
----------
        23

denis@TEST10G>
denis@TEST10G> SELECT INSTR('Take the first four characters', 'four', 1, 1) MCHARS
  2  FROM dual;

    MCHARS
----------
        16

denis@TEST10G>
denis@TEST10G> SELECT INSTR('Take the first four characters', 'a', -1, 1) REV_SRCH
  2  FROM dual;

  REV_SRCH
----------
        25

denis@TEST10G>
denis@TEST10G> SELECT INSTR('Take the first four characters', 'a', -1, 2) REV_TWO
  2  FROM dual;

   REV_TWO
----------
        23

denis@TEST10G>
denis@TEST10G> -- Take up to the character before the first comma
denis@TEST10G> SELECT SUBSTR('abc,def,ghi', 1 ,INSTR('abc,def,ghi', ',', 1, 1)-1)
  2  FROM dual;

SUB
---
abc

denis@TEST10G>
denis@TEST10G> -- List parsing center value
denis@TEST10G> -- Take the value between the commas
denis@TEST10G> SELECT SUBSTR('abc,def,ghi', INSTR('abc,def,ghi',',', 1, 1)+1,
  2  INSTR('abc,def,ghi',',',1,2)-INSTR('abc,def,ghi',',',1,1)-1)
  3  FROM dual;

SUB
---
def

denis@TEST10G>
denis@TEST10G> -- List parsing last value
denis@TEST10G> -- Take the value after the last comma
denis@TEST10G> SELECT SUBSTR('abc,def,ghi', INSTR('abc,def,ghi',',', 1, 2)+1)
  2  FROM dual;

SUB
---
ghi

denis@TEST10G>
denis@TEST10G>
denis@TEST10G> -- test the case that restoring a databsase to a different host and with different path name
denis@TEST10G> -- using RMAN.   Demonstrate how to create 'set new name' statments
denis@TEST10G>
denis@TEST10G> set linesize 400
denis@TEST10G> drop  table t;

Table dropped.

denis@TEST10G> create table t(file_name varchar2(100));

Table created.

denis@TEST10G> insert into t values('/opt/app/wcmdb02/ora01/P3CM1D1/dbf/idx500cP0CMD_7.dbf');

1 row created.

denis@TEST10G> insert into t values('/opt/app/wcmdb02/ora03/P3CM1D1/dbf/usersP0CMD_1.dbf');

1 row created.

denis@TEST10G> commit;

Commit complete.

denis@TEST10G>
denis@TEST10G> select 'set newname for datafile ' || ''''||file_name||'''' ||
  2          lpad('to ', 60-length(file_name)) ||  ''''||
  3          '/opt/app/cm2cmdb' || substr(file_name,instr(file_name,'/',1,4), 6) ||'/P0CMD' ||
  4           substr(file_name, instr(file_name, '/', -1, 2) ) || '''' || ';'
  5  from t;

'SETNEWNAMEFORDATAFILE'||''''||FILE_NAME||''''||LPAD('TO',60-LENGTH(FILE_NAME))||''''||'/OPT/APP/CM2CMDB'||SUBSTR(FILE_NAME,INSTR(FILE_NAME,'/',1,4),6
------------------------------------------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------------------------------------------

set newname for datafile '/opt/app/wcmdb02/ora01/P3CM1D1/dbf/idx500cP0CMD_7.dbf'    to '/opt/app/cm2cmdb/ora01/P0CMD/dbf/idx500cP0CMD_7.dbf';
set newname for datafile '/opt/app/wcmdb02/ora03/P3CM1D1/dbf/usersP0CMD_1.dbf'      to '/opt/app/cm2cmdb/ora03/P0CMD/dbf/usersP0CMD_1.dbf';

denis@TEST10G>