' - CLOBUpdate - ASP/VBScript function updates CLOB data
' - This ASP function writes a CLOB (character large object datatype) to your Oracle table.
' - You must specify the Oracle table, column name, what content you want to write (a string), where clause, and DSN string.
' - Table: name of Oracle table where your data resides.
' - Column: name of column (with a datatype of CLOB) in your Oracle table where to write your data.
' - String To Write: the actual data to write. CLOBs can be up to 2G, I think VBScript's string size limit is 2M.
' - Where clause: what you would specify in your SQL call to write your data, such as (update customer_table set lots_of_customer_info_columnname='this customer''s entire geneaology is ...' where) "CustomerID='123456'"
' - DSN: data source name. Should be either:
' - 1. application("CustomerDB_DSN") (if you're using global.asa in the root path of your webserver)
' - 2. "DRIVER={Oracle ODBC Driver};DBQ=ora.domain.com;UID=userid;pwd=password"
' - Call using:
' - CLOBUpdate(strTableCU, strCLOBColumnCU, strContentCU, strWhereClauseCU, strConnectionStringCU)
' - Example:
' - CLOBUpdate("customer_table", "lots_of_customer_info_columnname", "this customer's entire geneaology is: "&strMassiveGeneologyString, "CustomerID='"&strCustomerID&"'", application("CustomerDB_DSN"))
' - Note: This subroutine writes only one string (at a time).
sub CLOBUpdate(strTableCU, strCLOBColumnCU, strContentCU, strWhereClauseCU, strConnectionStringCU)
dim conDBOracleGenericWriteCU, strSQLCU
dim intRowsAffectedCU, intCountSQLStringsCU, strSubStringCU
dim objRegexpCU, objSubStringsCU, intCountSubStringsCU
dim objSubStringCU
const intSegmentLengthCU=2000
set conDBOracleGenericWriteCU=server.CreateObject("ADODB.Connection")
if strContentCU="" then
' - if empty string, write empty string
conDBOracleGenericWriteCU.open strConnectionStringCU
conDBOracleGenericWriteCU.BeginTrans
conDBOracleGenericWriteCU.Execute "update " & strTableCU & " set " & strCLOBColumnCU & "='' where " & strWhereClauseCU, intRowsAffectedCU
if intRowsAffectedCU=1 then
conDBOracleGenericWriteCU.CommitTrans
else
conDBOracleGenericWriteCU.RollbackTrans
response.write "CLOBUpdate() error: <b>" & err.description & "</b><br>"
response.write "Table: <b>" & strTableCU & "</b><br>"
response.write "Column: <b>" & strCLOBColumnCU & "</b><br>"
response.write "Where clause: <b>" & strWhereClauseCU & "</b><br>"
response.write "Connection string: <b>" & strConnectionStringCU & "</b><br>"
response.write "Content: <b>" & strContentCU & "</b><br>"
err.clear
end if
conDBOracleGenericWriteCU.close
else
' - write string one segment at a time using Oracle's dbms_lob.write function and short-lived stored procedures
set objRegexpCU=new regexp
objRegexpCU.global=true
objRegexpCU.pattern="((\n|.){1," & intSegmentLengthCU & "})"
set objSubStringsCU=objRegexpCU.execute(strContentCU)
for intCountSubStringsCU=0 to objSubStringsCU.count-1
strSubStringCU=objSubStringsCU.item(intCountSubStringsCU)
if intCountSubStringsCU=0 then
' - write 1st segment
strSQLCU = "update " & strTableCU & " set " & strCLOBColumnCU & "='" & replace(strSubStringCU, "'", "''") & "' where " & strWhereClauseCU
else
' - write subsequent segments
strSQLCU = "declare Destination clob; "
strSQLCU = strSQLCU & "begin "
strSQLCU = strSQLCU & "select " & strCLOBColumnCU & " into Destination from " & strTableCU
strSQLCU = strSQLCU & " where " & strWhereClauseCU & " for update; "
strSQLCU = strSQLCU & "dbms_lob.write(Destination, " & len(strSubStringCU) & ", "
strSQLCU = strSQLCU & "dbms_lob.getlength(Destination)+1, "
strSQLCU = strSQLCU & "'" & replace(strSubStringCU, "'", "''") & "'); "
strSQLCU = strSQLCU & "commit; "
strSQLCU = strSQLCU & "end; "
end if
conDBOracleGenericWriteCU.open strConnectionStringCU
conDBOracleGenericWriteCU.BeginTrans
conDBOracleGenericWriteCU.Execute strSQLCU, intRowsAffectedCU
conDBOracleGenericWriteCU.CommitTrans
conDBOracleGenericWriteCU.close
next
end if
set conDBOracleGenericWriteCU=nothing
end sub
