' - CLOBRead - ASP/VBScript function reads CLOB data
' - This ASP function reads a CLOB (character large object field) from your Oracle table.
' - You must specify the Oracle table, column name, 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 your data resides.
' - Where clause: what you would specify in your SQL call to get your data, such as (select lots_of_customer_info_columnname from customer_table 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:
' - strReturnString=CLOBRead(strTable, strCLOBColumn, strWhereClause, strDSN)
' - Example:
' - strBigOracleString=CLOBRead("customer_table", "lots_of_customer_info_columnname", "CustomerID='"&strCustomerID&"'", application("CustomerDB_DSN"))
' - Note: This function returns only one string (at a time).
function CLOBRead(strTableCR, strCLOBColumnCR, strWhereClauseCR, strConnectionStringCR)
on error resume next
dim conDBOracleGenericReadCR, strStringReturnedCR
dim intCLOBLengthCR, intStartCR, strSQLCR
const intSegmentLengthCR=4000
strStringReturnedCR=""
set conDBOracleGenericReadCR=server.CreateObject("ADODB.Recordset")
strSQLCR="select dbms_lob.getlength(" & strCLOBColumnCR & ") as CLOBLength from " & strTableCR & " where " & strWhereClauseCR & ";"
conDBOracleGenericReadCR.open strSQLCR, strConnectionStringCR
if err.number<>0 then
response.write "CLOBRead() error: Error in SQL call<br>"
response.write "Table: " & strTableCR
response.write "Column: <b>" & strCLOBColumnCR & "</b><br>"
response.write "Where clause: <b>" & strWhereClauseCR & "</b><br>"
response.write "Connection string: <b>" & strConnectionStringCR & "</b><br>"
response.write "SQL statement generated: <b>" & strSQLCR & "</b><br>"
err.clear
else
intCLOBLengthCR=conDBOracleGenericReadCR("CLOBLength")
conDBOracleGenericReadCR.close
intStartCR=1
if intCLOBLengthCR>0 then
do
conDBOracleGenericReadCR.open "select dbms_lob.substr(" & strCLOBColumnCR & ", " & intSegmentLengthCR & ", " & intStartCR & ") as CLOBSegment from " & strTableCR & " where " & strWhereClauseCR, strConnectionStringCR
strStringReturnedCR=strStringReturnedCR & conDBOracleGenericReadCR("CLOBSegment")
conDBOracleGenericReadCR.close
intStartCR=intStartCR+intSegmentLengthCR
loop until intStartCR=>intCLOBLengthCR
end if
end if
set conDBOracleGenericReadCR=nothing
CLOBRead=strStringReturnedCR
end function
