用 Sql*Plus 创建批量文件改名的脚本

干脆，用 Sql*Plus 来帮助创建脚本。先把对照文件上传到测试机器上，做个外部表先：

SQL> CREATE DIRECTORY forrename AS '/u01/app';

Directory created.

SQL> GRANT READ,WRITE ON DIRECTORY forrename TO SYSTEM;

Grant succeeded.

SQL> 

SQL> CREATE  TABLE forrename 
  2   (oldcode VARCHAR2(10),
  3   newcode VARCHAR2(12))
  4   ORGANIZATION EXTERNAL
  5   (TYPE oracle_loader
  6    DEFAULT DIRECTORY testdir
  7    ACCESS PARAMETERS(RECORDS DELIMITED BY NEWLINE
  8    FIELDS TERMINATED BY ',')
  9   LOCATION('code.txt'));

Table created.
创建完，验证一下．然后考虑拼写脚本：
SQL> set heading off 
SQL> set echo off 
SQL> set feedback off 
SQL> spool /u01/app/myrename.sh
SQL> SELECT 'mv ' || oldcode || '_2.png' OLD,
  2         ' DIR/' || SUBSTR (newcode, 1, 10) || '_2.png' NEW
  3    FROM forrename;



得到的脚本内容如下：
mv 9030502700_2.png  DIR/4009030530_2.png
mv 9030500100_2.png  DIR/4009030504_2.png
mv 9030500200_2.png  DIR/4009030505_2.png
mv 9030500300_2.png  DIR/4009030506_2.png
mv 9030503800_2.png  DIR/4009030541_2.png
mv 9030503400_2.png  DIR/4009030537_2.png
mv 9030503500_2.png  DIR/4009030538_2.png

然后创建其他对应后缀的改名脚本,_1.png,_3.png......

最后修改一下执行文件权限，执行．

关于外部表：三步教会你掌握oracle外表(external table) by Rudolf Lu 

http://www.cnoug.org/viewthread.php?tid=2466