Last post Dec 16, 2013 11:18 AM by brandrui
Dec 16, 2013 11:18 AM|brandrui|LINK
I'm working on intranet pages which use Oracle as the db. In one of my vendor supplied tables, the column is a CLOB, however, I need to present the material in a user-friendly format. If I'm using DbVis or Oracle's SQL Developer, the query works beautifully.
SELECT m.cdts, m.msg_num, UPPER(dbms_lob.substr(m.msg_text,4000,1)), m.cterm, m.cpers, d.destination, p.lname
FROM destinations d, messa m, persl p
AND m.cdts BETWEEN :sdate AND :fdate
AND m.msg_text NOT LIKE 'Ackn%'
ORDER BY m.cdts DESC
However, when I insert it into my WebPage in WebMatrix, I return a ORA-06502 error: PL/SQL: numeric or value error: character string buffer too small. The dbms_lob.substr is the only portion of the query where I have a buffer. (In PL/SQL it can be up to 32000 characters, but in regular SQL it's only 4000) So I have a feeling this is where the problem is. However, I don't know how to solve it either. I'm running into brick walls trying to understand it. So anyone out there have an idea where to lead me to find the answer?
The code in my WebPage is:
var qYr = DateTime.Now.Year;
var qMon = DateTime.Now.Month;
var sDay = DateTime.Now.AddDays(-1).Day;
var fDay = DateTime.Now.Day;
var sHr = DateTime.Now.AddHours(-1).Hour;
var fHr = DateTime.Now.Hour;
var sDate = qYr.ToString() + qMon.ToString() + sDay.ToString() + sHr.ToString();
var fDate = qYr.ToString() + qMon.ToString() + fDay.ToString() + fHr.ToString();
var qry = "SELECT m.cdts AS cdts, m.msg_num AS msgNum, UPPER(dbms_lob.substr(m.msg_text,32000,1)) AS msgTxt, m.cterm AS cterm, m.cpers AS cpers, d.destination AS destination, p.lname AS lastName FROM destinations d, messa m, persl p WHERE m.msg_num=d.item_number AND m.cdts BETWEEN'" + sDate + "' AND '" + fDate + "' AND m.msg_text NOT LIKE 'Ackn%' AND m.cpers=p.empid ORDER BY m.cdts DESC";
var rows = db.Query(qry);
The connection string et al. are not a problem so I didn't include those in here.