BLOB to CLOB?

[ChrisHunt]

I'm trying to use Forms & webutil to upload a text (XML) file into a CLOB variable on the database.

I use webutil_file_transfer.client_to_DB to transfer the file into a BLOB column on a table, this seems to work OK.

I then retrieve this BLOB from the database and try to make it into a CLOB. There's a procedure called DBMS_LOB.CONVERTTOCLOB that looks like it ought to do the job, but it isn't documented anywhere. It's got a number of cryptic parameters that I can't figure out. Does anyone know how to use it?

-- Chris Hunt

http://www.extracon.com

http://www.mcgonagall-online.org.uk

http://www.napitalia.org.uk

http://www.leicesteryha.org.uk

 

[sem]

Are you working with 10g?

Regards, Dima

 

[ChrisHunt]

No, I'm on 9i.

I've worked around it by writing my own procedure (see below) but the built-in Oracle one ought to be faster if I only knew how to use it...
[tt]
PROCEDURE blob_to_clob (plobClob IN OUT NOCOPY CLOB,
plobBlob IN BLOB,
pblnStripCR IN BOOLEAN := FALSE) IS
lnumPos NUMBER := 1;
lnumLength NUMBER;
lstrChunk VARCHAR2(32767);
cnumBiteSize CONSTANT NUMBER := 32767;
BEGIN
lnumLength := DBMS_LOB.GETLENGTH(plobBlob);

WHILE lnumPos <= lnumLength LOOP
lstrChunk := UTL_RAW.CAST_TO_VARCHAR2(
DBMS_LOB.SUBSTR(plobBlob,cnumBiteSize,lnumPos)
);

IF pblnStripCR THEN
lstrChunk := REPLACE(lstrChunk,CHR(13),'');
END IF;

clob_pkg.append(plobClob,lstrChunk);
lnumPos := lnumPos + cnumBiteSize;
END LOOP;
END;
[/tt]
(The additional &quot;StripCR&quot; functionality is for changing Windows CRLFs to Unix LFs)

-- Chris Hunt

http://www.extracon.com

http://www.mcgonagall-online.org.uk

http://www.napitalia.org.uk

http://www.leicesteryha.org.uk

 

[sem]

I can't find any sign of CONVERTTOCLOB on 9.2 database.

Regards, Dima

 

[ChrisHunt]

Look in the package spec for SYS.DBMS_LOB, you'll see
[tt]
PROCEDURE convertToClob(dest_lob IN OUT NOCOPY CLOB CHARACTER SET ANY_CS,
src_blob IN BLOB,
amount IN INTEGER,
dest_offset IN OUT INTEGER,
src_offset IN OUT INTEGER,
blob_csid IN NUMBER,
lang_context IN OUT INTEGER,
warning OUT INTEGER);
[/tt]

-- Chris Hunt

http://www.extracon.com

http://www.mcgonagall-online.org.uk

http://www.napitalia.org.uk

http://www.leicesteryha.org.uk