can anyone give me a straight answer about clob datatype

[borisch]

I've been tring to post same question for a 3rd time and can not get even one
helpfull answer. Please if you do not know do not answer and do not give me
answers like "see this link or that"

I am trying to run SQL statement to update one field with a datatype "clob"
and SQL give me an error "ORA-01704: string literal too long ".
The len of the string I am trying to save is 8336 chr. It could be longer or
shorter depending on a webpage.
If I take only 4000 chr left(XHTML,4000) then everything is fine. I thought
that CLOB take 4GB of text. What am I doing wrong? Please help.

I have a table like this:
CREATE TABLE SMKT.INT_SESSIONLOG
(
LOGID VARCHAR2(50) NOT NULL,
SESSIONID VARCHAR2(50) NOT NULL,
DATECREATED VARCHAR2(50) NOT NULL,
PAGEXML CLOB DEFAULT empty_clob() NULL,
XHTML CLOB DEFAULT empty_clob() NULL,
USERID VARCHAR2(50) NOT NULL,
)

and

update statement like this:

Update INT_SESSIONLOG SET XHTML = '" & mXHTML.xml "' WHERE LogID = '" &
msLogID & "'"

I am trying to run this SQL from VB. For now sql statement inside VB code.
I will create a stored procedure and put it inside a package but not today.
How can I insert/update/select value from clob field.
Most of the time data will be more then 32 K.


Thanks a lot.

 

[spenglerr]

ok,

third time? I read it the second time and was absent the past 7 days, so keep patient.

first you have to open the lob for Read/Write action
then you wite or writeappend to it
then you close it and that's it.

Example 1 (Function) ------------------------------------

-- Local function to get the inner text of an xml-tag as an clob
FUNCTION getXMLElementAsClob (
aName VARCHAR2
,aXMLDoc xmldom.DOMDocument)
RETURN CLOB
IS
nodeList xmldom.DOMNodeList;
node xmldom.DOMNode;
varClob CLOB;
BEGIN
DBMS_LOB.CREATETEMPORARY(varClob, TRUE, DBMS_LOB.SESSION);
DBMS_LOB.OPEN(varClob, DBMS_LOB.lob_readwrite);
nodeList := xpath.selectNodes(aXMLDoc,aName);
IF xmldom.isNull(nodeList)
THEN
DBMS_LOB.CLOSE(varClob);
RETURN varClob;
END IF;
node := xmldom.item(nodeList,0);
IF xmldom.isNull(node)
THEN
DBMS_LOB.CLOSE(varClob);
RETURN varClob;
END IF;
node := xmldom.getFirstChild(node);
IF xmldom.isNull(node)
THEN
DBMS_LOB.CLOSE(varClob);
RETURN varClob;
END IF;
xmldom.writeToClob(node,varClob);
DBMS_LOB.CLOSE(varClob);
RETURN varClob;
END getXMLElementAsClob;
/

Example 2 -----------------------------------------------

/*
|| Procedure for uploading XML-Files
*/
CREATE OR REPLACE PROCEDURE insertXMLFile (
dir VARCHAR2
,file VARCHAR2
,name VARCHAR2 := NULL )
IS
theBFile BFILE;
theClob CLOB;
theDocName VARCHAR2(200) := NVL(name,file);

BEGIN
-- (1) insert a new row into xml_documents with an empty CLOB and
-- (2) Retrieve the empty CLOB into a variable
INSERT INTO xml_bh_imports (
id
,docname
,xmldoc )
VALUES (
seq_xml_bh_imports.NextVal
,theDocName
,empty_clob()
)
RETURNING xmldoc INTO theClob;

-- (3) Get a BFile handle to the external file
theBFile := BFileName(dir,file);

-- (4) Open the file
DBMS_LOB.fileOpen(theBFile);

-- (5) Copy the contents of the BFile into the empty CLOB
DBMS_LOB.loadFromFile (
dest_lob => theCLOB
,src_lob => theBFile
,amount => DBMS_LOB.GETLENGTH(theBFile) );

DBMS_LOB.fileClose(theBFile);
END insertXMLFile;
/

Example 3 (search replace from metalink with a nice overloading feature built in by us): ---------------------

CREATE OR REPLACE PACKAGE repl_str AS

FUNCTION search_replace (
dest_lob CLOB
,search_str VARCHAR2
,replace_str VARCHAR2)
RETURN CLOB;

FUNCTION search_replace (
dest_lob VARCHAR2
,search_str VARCHAR2
,replace_str VARCHAR2)
RETURN VARCHAR2;

END;
/


CREATE OR REPLACE PACKAGE BODY repl_str AS

FUNCTION search_replace (
dest_lob CLOB
,search_str VARCHAR2
,replace_str VARCHAR2)
RETURN CLOB
IS
temp_clob CLOB;
new_clob CLOB;
end_offset INTEGER := 1;
start_offset INTEGER := 1;
occurence NUMBER := 1;
replace_str_len NUMBER := LENGTH(replace_str);
temp_clob_len NUMBER := 0;
dest_lob_len NUMBER := 0;
BEGIN
IF DBMS_LOB.ISOPEN(dest_lob) = 0 THEN
NULL;
END IF;
DBMS_LOB.CREATETEMPORARY(temp_clob,TRUE,DBMS_LOB.SESSION);
LOOP
end_offset := DBMS_LOB.INSTR(dest_lob,search_str,1,occurence);
IF end_offset = 0 THEN
temp_clob_len := DBMS_LOB.GETLENGTH(temp_clob);
dest_lob_len := DBMS_LOB.GETLENGTH(dest_lob) - start_offset + 1;
IF dest_lob_len > 0 THEN
DBMS_LOB.COPY(temp_clob,dest_lob,dest_lob_len,temp_clob_len+1,start_offset);
END IF;
EXIT;
END IF;
temp_clob_len := DBMS_LOB.GETLENGTH(temp_clob);
IF (end_offset - start_offset) > 0 THEN
DBMS_LOB.COPY(temp_clob,dest_lob,(end_offset - start_offset),temp_clob_len+1,start_offset);
END IF;
start_offset := end_offset + LENGTH(search_str);
occurence := occurence + 1;
IF replace_str IS NOT NULL THEN
DBMS_LOB.WRITEAPPEND(temp_clob,replace_str_len,replace_str);
END IF;
END LOOP;
RETURN temp_clob;
END search_replace;


FUNCTION search_replace (
dest_lob VARCHAR2
,search_str VARCHAR2
,replace_str VARCHAR2)
RETURN VARCHAR2
IS
tmp_str VARCHAR2(4000);
BEGIN
SELECT REPLACE(dest_lob,search_str,replace_str) INTO tmp_str FROM dual;
RETURN tmp_str;
EXCEPTION
WHEN OTHERS
THEN
RETURN dest_lob;
END search_replace;

END;
/

 

[borisch]

thanks a lot