【原创】初始化nclob等大对象类型的数据

        在软件系统上线时，如果系统初始化数据中包含nclob,clob等大对象类型的数据，使用insert语句初始化数据，往往会遇到问题，说你的字符串太长啊，等等。此类的问题往往要使用oracle提供的包DBMS_LOB, 该包提供了对大对象类型的操作。本文讲述初始化nclob字段时遇到的问题，及解决方法。

        lob大对象的介绍可以参照oracle对大对象类型的操作（，我也从别的地方摘抄过来的）。

        系统需要初始化的表中有一个表fs_static_page,主要存储一些静态网页的相关内容，其中有一个字段content,nclob类型的，存储的是网页的body. 往往都有上万个字节，采用如下的方式会出现错误。

Insert into FS_STATIC_PAGE
   (STATIC_PAGE_ID, STATIC_PAGE_CODE, STATIC_PAGE_NAME, ENABLED, CREATED_DATE,
    CREATED_BY, LAST_UPDATED_DATE, LAST_UPDATED_BY, CONTENT)
Values
   (101, '10', 'XXXXXXXXXXX, 1, TO_DATE('01/06/2009 13:51:28', 'MM/DD/YYYY HH24:MI:SS'),
    0, TO_DATE('01/08/2009 17:43:29', 'MM/DD/YYYY HH24:MI:SS'), 0, '<table width="583" border="0" cellspacing="0" cellpadding="0">
                        <tr>
                            <td>
                        中间的文件太多，我就......
                            <td>
                                &nbsp;</td>
                        </tr>
                    </table>')
Error at line 2358
ORA-01704: 字符串文字太长

 

解决方案思路：

1. 将字段content内容进行分割，分割成每个字符串的长度小于4000个字节，获得一个字符串数组SpilitStrings。
2. 定义一个nclob变量directions，并通过select给他指定一个lob locator,相当于一个大对象指针。
3. 采用DBMS_LOB包的方法DBMS_LOB.writeappend方法，将字符串数组SpilitStrings的每一条写入变量directions。

 

具体的代码参照如下：

 

DECLARE 
   directions        NCLOB; 
   more_directions   VARCHAR2 (4000); 
   seq               INTEGER; 
BEGIN 
--VSS $Revision: 1.1 $ 

INSERT INTO [MEMBER].[FS_STATIC_PAGE] ( 
    [STATIC_PAGE_ID], 
    [STATIC_PAGE_CODE], 
    [STATIC_PAGE_NAME], 
    [ENABLED], 
    [CREATED_DATE], 
    [CREATED_BY], 
    [LAST_UPDATED_DATE], 
    [LAST_UPDATED_BY], 
    [CONTENT] 
) 
   INSERT INTO fs_static_page 
  ( 
      static_page_id, 
      static_page_code, 
      static_page_name, 
      enabled, 
      created_date, 
      created_by, 
      last_updated_date, 
      last_updated_by, 
      content 
  ) 
   VALUES ( 
             101, 
             '10', 
             'XXXXXXXXXXX, 
             1, 
             TO_DATE ('01-06-2009 13:51:28.000', 'MM/DD/YYYY HH24:MI:SS'), 
             0, 
             TO_DATE ('01-08-2009 17:43:29.000', 'MM/DD/YYYY HH24:MI:SS'), 
             0, 
             EMPTY_CLOB ()  --指定一个空指针 
          ); 

--给变量directions设定指针   

SELECT content 
   INTO directions 
   FROM fs_static_page 
   WHERE static_page_id = 101; 
   DBMS_LOB.open (directions, DBMS_LOB.lob_readwrite); --打开变量 
   --此处开始循环，知道变量SpilitStrings中的字符串写入变量directions完毕 

  more_directions   := 
                '<table width="583" border="0" cellspacing="0" cellpadding="0"> 
                        <tr> 
                              ..                            

                                <table width="583" border="0" cellspacing="0" cellpadding="0"> 
                              '; 
   DBMS_LOB.writeappend (directions, 
                                    LENGTH (more_directions), 
                                    more_directions 
            );

  --循环结束。

  --关闭变量directions,并提交事务

--Close the LOB, and we are done. 
   DBMS_LOB.close (directions);

   COMMIT; 
END;

 

具体实施方法：

因为FS_STATIC_PAGE表中有很多条记录，手工处理很容易出错，且比较累，因为我比较懒，所以就要想法子让他自动生成这样的上面的语句。使用CodeSmith，这个工具我还是很熟悉的。马上动手，写一个模板出来。运行，OK,获得了上面的oracle语句，拿在toad或者pl/sql里面去执行，数据初始化成功。哈！下面附上CodeSmith模板的内容。另外，要说明一点，因为FS_STATIC_PAGE表中存储的是网页内容，会有&nbsp含有&的字符串，&在执行的过程中，会被认为是一个变量，所以，要将字符串中的&替换成'|| '&'|| '，该替换已经在CodeSmith模板中处理。

 

<%@ CodeTemplate Language="C#" Debug="True" TargetLanguage="T-SQL" ResponseEncoding="UTF-8" Description="生成LOB大对象初始化数据的脚本" %>
<%@ Property Name="SourceTable" Type="SchemaExplorer.TableSchema" Category="Context" Description="Table to get the data from." %>
<%@ Assembly Name="SchemaExplorer" %>
<%@ Assembly Name="CodeSmith.BaseTemplates" %>
<%@ Assembly Name="System.Data" %>
<%@ Import Namespace="SchemaExplorer" %>
<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Text" %>

DECLARE
   directions        NCLOB;
   more_directions   VARCHAR2 (4000);
   seq               INTEGER;
BEGIN
--VSS $Revision: 1.1 $


INSERT INTO <%= SourceTable.Name %> (
    <% for (int i = 0; i < SourceTable.Columns.Count; i++) { %>
    [<%= SourceTable.Columns[i].Name %>]<% if (i < SourceTable.Columns.Count - 1) { %>,<% } %>
    <% } %>
)
    <% for (int i = 0; i < SourceTableData.Rows.Count; i++) { %>
   INSERT INTO fs_static_page
  (
      static_page_id, <% //因时间仓促，此处是根据我的表的字段写死的 %>
      static_page_code,
      static_page_name,
      enabled,
      created_date,
      created_by,
      last_updated_date,
      last_updated_by,
      content
  )
   VALUES (  <% //因时间仓促，此处是根据我的表的字段写死的,有需要的朋友可以改写 %>
             <%= GetTableRowValues(SourceTableData.Rows[i],"static_page_id") %>,
             <%= GetTableRowValues(SourceTableData.Rows[i],"static_page_code") %>,
             <%= GetTableRowValues(SourceTableData.Rows[i],"static_page_name") %>,
             <%= GetTableRowValues(SourceTableData.Rows[i],"enabled") %>,
             <%= GetTableRowValues(SourceTableData.Rows[i],"created_date") %>,
             <%= GetTableRowValues(SourceTableData.Rows[i],"created_by") %>,
             <%= GetTableRowValues(SourceTableData.Rows[i],"last_updated_date") %>,
             <%= GetTableRowValues(SourceTableData.Rows[i],"last_updated_by") %>,
             EMPTY_CLOB ()
          );

   SELECT content
   INTO directions
   FROM fs_static_page
   WHERE static_page_id = <%= GetTableRowValues(SourceTableData.Rows[i],"static_page_id") %>;
   DBMS_LOB.open (directions, DBMS_LOB.lob_readwrite);
        <% 
        System.Collections.Generic.List<string> cc = SpilitNclob(GetTableRowValues(SourceTableData.Rows[i],"content"));
        for (int ii = 0; ii < cc.Count; ii++) { %>
            more_directions   :=
                <% if (cc[ii].StartsWith("'")==false){ %>'<% } %><%= cc[ii] %><% if (cc[ii].EndsWith("'")==false){ %>'<% } %>;
            DBMS_LOB.writeappend (directions,
                                    LENGTH (more_directions),
                                    more_directions
            );
           <% } %>
   --Close the LOB, and we are done.
   DBMS_LOB.close (directions);
    
    <% } %>

   COMMIT;
END;
<script runat="template">
private DataTable _sourceTableData;
//切割后每个字符串的长度
private int _stringLength = 1800;
private DataTable SourceTableData
{
    get
    {
        if (_sourceTableData == null)
        {
            _sourceTableData = SourceTable.GetTableData();
        }
        
        return _sourceTableData;
    }
}



//分割字符串
public System.Collections.Generic.List<string> SpilitNclob(string input)
{
    System.Collections.Generic.List<string> spilitString = new System.Collections.Generic.List<string>() ; 
    
    while (input.Length >_stringLength)
    {
        spilitString.Add(input.Substring(0,_stringLength));
        input= input.Substring(_stringLength);
    }
    spilitString.Add(input);
     return spilitString;
}

public string GetTableRowValues(DataRow row,string columnName)
{
    StringBuilder rowBuilder = new StringBuilder();
    
    
        ColumnSchema column = SourceTable.Columns[columnName];
        
        if (IsNumericType(column))
        {
            if (row[columnName] == DBNull.Value)
            {
                rowBuilder.Append("NULL, ");
            }
            else
            {
                rowBuilder.Append(row[columnName].ToString());
                rowBuilder.Append(", ");
            }
        }
        else
        {
            if (row[columnName] == DBNull.Value)
            {
                rowBuilder.Append("NULL, ");
            }
            else if (row[columnName] is DateTime)
            {
                rowBuilder.Append("TO_DATE ('");
                rowBuilder.Append(((DateTime)row[columnName]).ToString("MM-dd-yyyy HH:mm:ss.fff"));
                rowBuilder.Append("', 'MM/DD/YYYY HH24:MI:SS')");
                rowBuilder.Append(", ");
            }
            else
            {
                rowBuilder.Append("'");
                rowBuilder.Append(PrepareValue(row[columnName].ToString()));
                rowBuilder.Append("'");
                rowBuilder.Append(", ");
            }
        }
    
    //替换oracle特殊字符&为'|| '&'|| '
    rowBuilder.Replace("&","'|| '&'|| '");
    return rowBuilder.ToString().Substring(0, rowBuilder.ToString().Length - 2);
}

public string PrepareValue(string value)
{
    //return value.Replace("'", "''").Replace("\r\n", "' + CHAR(13) + CHAR(10) + '").Replace("\n", "' + CHAR(10) + '");
    return value;
}

public bool IsNumericType(ColumnSchema column)
{
    
    switch (column.NativeType.ToLower())
    {
        
        case "number":
        case "bit":
        case "decimal":
        case "float":
        case "int":
        case "money":
        case "numeric":
        case "real":
        case "smallint":
        case "smallmoney":
        case "tinyint": return true;
        default: return false;
    }
}
</script>