用分析函数实现自动生成sqlloader定长数据装载控制文件的一个语句

本文中的内容在《2010-05-07 22:08 Automatic SQL*LOADER(以定长方式为例) ，原文地址：http://hi.baidu.com/nsj820/item/b346e517416d43011994ec5c》也能实现，但这里主要想体现的是oracle分析函数的强大。

WITH FIX_LENGTH AS
 (SELECT A.TABLE_NAME,
         A.COLUMN_ID,
         A.COLUMN_NAME,
         A.DATA_LENGTH,
         SUM(P_OFFSET) OVER(PARTITION BY A.TABLE_NAME ORDER BY A.COLUMN_ID) P_START,
         SUM(P_OFFSET) OVER(PARTITION BY A.TABLE_NAME ORDER BY A.COLUMN_ID) + DATA_LENGTH - 1 P_END
    FROM (SELECT A.TABLE_NAME,
                 A.COLUMN_ID,
                 A.COLUMN_NAME,
                 A.DATA_LENGTH,
                 LAG(A.DATA_LENGTH, 1, 1) OVER(PARTITION BY A.TABLE_NAME ORDER BY A.COLUMN_ID) P_OFFSET,
                 SUM(A.DATA_LENGTH) OVER(PARTITION BY A.TABLE_NAME ORDER BY A.COLUMN_ID) P_CONTINUOUS_SUMMATION
            FROM USER_TAB_COLUMNS A) A)
S