ORACLE SQL: Basic help required in script

Shell Programming and Scripting
1

Hi,

I am having the following codes which generates 24 files:

 declare
 begin
 for i in 0..23 loop
 EXECUTE IMMEDIATE 'create tablespace TSCGFCDR' ||to_char(sysdate,'DD')||'_'||to_char(i,'fm00')||
 to_char(i+1,'fm00')||to_char(sysdate,'MONYY')||' DATAFILE '||
  '''/ORADATA7/CRESTELDATA/TSCGFCDR'||to_char(sysdate,'DD')||'_'||to_char(i,'fm00')||
 to_char(i+1,'fm00')||to_char(sysdate,'MONYY')||'.dbf''
 SIZE 1M AUTOEXTEND ON NEXT 5M MAXSIZE UNLIMITED EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO';
 end loop;
 end;
 /

TSCGFCDR10_0001OCT12.dbf  TSCGFCDR10_0405OCT12.dbf  TSCGFCDR10_0809OCT12.dbf  TSCGFCDR10_1213OCT12.dbf  TSCGFCDR10_1617OCT12.dbf  TSCGFCDR10_2021OCT12.dbf
TSCGFCDR10_0102OCT12.dbf  TSCGFCDR10_0506OCT12.dbf  TSCGFCDR10_0910OCT12.dbf  TSCGFCDR10_1314OCT12.dbf  TSCGFCDR10_1718OCT12.dbf  TSCGFCDR10_2122OCT12.dbf
TSCGFCDR10_0203OCT12.dbf  TSCGFCDR10_0607OCT12.dbf  TSCGFCDR10_1011OCT12.dbf  TSCGFCDR10_1415OCT12.dbf  TSCGFCDR10_1819OCT12.dbf  TSCGFCDR10_2223OCT12.dbf
TSCGFCDR10_0304OCT12.dbf  TSCGFCDR10_0708OCT12.dbf  TSCGFCDR10_1112OCT12.dbf  TSCGFCDR10_1516OCT12.dbf  TSCGFCDR10_1920OCT12.dbf  TSCGFCDR10_2324OCT12.dbf
�

Last file name i want instead of TSCGFCDR10_2324OCT12 i want it to be TSCGFCDR10_2300OCT12.

Can somebody pls help me to modify this. This is not stirking me as how to do

---------- Post updated at 09:41 AM ---------- Previous update was at 08:51 AM ----------

any help please....have to implement this script!!!!:o

2

The modulo function should do the trick (untested):

declare
 begin
 for i in 0..23 loop
 EXECUTE IMMEDIATE 'create tablespace TSCGFCDR' ||to_char(sysdate,'DD')||'_'||to_char(i,'fm00')||
 to_char(i+1,'fm00')||to_char(sysdate,'MONYY')||' DATAFILE '||
  '''/ORADATA7/CRESTELDATA/TSCGFCDR'||to_char(sysdate,'DD')||'_'||to_char(i,'fm00')||
 to_char(mod(i+1,24),'fm00')||to_char(sysdate,'MONYY')||'.dbf''
 SIZE 1M AUTOEXTEND ON NEXT 5M MAXSIZE UNLIMITED EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO';
 end loop;
 end;
 /
3

Can you please explain how mod fun will work

4

mod(a,b) returns the remainder of a devided by b. The Oracle documentation explains it quite simple and Wikipedia gives you deeper insight.

5

if you want change the file manually.... Try like ..

sed 's/TSCGFCDR10_2324OCT12/TSCGFCDR10_2300OCT12/g' test.txt