LONG RAW to CLOB conversion [to_lob]

最新推荐文章于 2024-06-04 23:55:51 发布

youcunzai

最新推荐文章于 2024-06-04 23:55:51 发布

阅读量1.9k

点赞数 1

文章标签： integer table insert thread oracle reference

Forum Home » Database » SQL and PL/SQL

Thread: LONG RAW to CLOB conversion --- LONG RAW exceeding 32K SIZE for some col.

Welcome, Guest
Sign In / Register
Guest Settings
Search
FAQ

Reply to this Thread

Search Forum

Back to Thread List

Replies: 12 - Pages: 1 - Last Post: 2008-12-31 上午6:58 Last Post By: user2244480

Shilpa

Posts: 21
Registered: 09/16/05

LONG RAW to CLOB conversion --- LONG RAW exceeding 32K SIZE for some col.
Posted: 2005-10-18 上午1:41

Hi,

My source data contains a char data in LONG RAW column. I have to convert it to Char(CLOB). The problem is that i can not access the LONG RAW data for some columns for which the size exceeding above 32k. I have wriiten the function using DBMS_SQL package to retrieve only 32K LONG RAW data & then i am converting it to char(CLOB). But as the LONG RAW size is exceeding for some columns, i want to retrieve the data upto 64K insted of 32 K.

I am using the SP to retieve the data from LONGRAW.
Is it possible to retrive whole data if instead of LONG RAW, the column data type is BLOB

Its very urgent

Thx in advance!

Shilpa

michielk

Posts: 81
Registered: 04/28/00

Re: LONG RAW to CLOB conversion --- LONG RAW exceeding 32K SIZE for some c
Posted: 2005-10-18 上午1:49

in response to: Shilpa

Hi

Did you take a look at askTom's implementation LONG2CLOB (use this to search on http://asktom.oracle.com/ ) ? I found it very usefull, not sure if it's capable of 32K+.

Furthermore, if you have 9i+ maybe you can use:

select TO_LOB(long_col) from table

hope this helps,
Michiel

Jim J.

Posts: 462
Registered: 06/10/05

Re: LONG RAW to CLOB conversion --- LONG RAW exceeding 32K SIZE for some c
Posted: 2005-10-18 上午6:38

in response to: michielk

Here's the link referred to in the previous post:
http://asktom.oracle.com/pls/ask/f?p=4950:8:::::F4950_P8_DISPLAYID:1037232794454

Shilpa

Posts: 21
Registered: 09/16/05

Re: LONG RAW to CLOB conversion --- LONG RAW exceeding 32K SIZE for some col.
Posted: 2005-10-26 上午5:13

in response to: Shilpa

The link specified code for LONG to CLOB conversion ...not LONG RAW to CLOB.
My sorce data contains the pure text data but it is stored in LONG RAW datatype.
I need to UPDATE the target table column whose data type is CLOB. But the problem is i am unable to fetch those source rows whose LONG RAW size is exceeding 32k size.

The current scenario
Source col : LONG RAW target Col : CLOB

I am first converting the LONG RAW data into CLOB using SP. Then this CLOB data gets appended to TARGET CLOB in the same SP. But this works upto 32K
LONG RAW data. If i need to convert the LONG RAW data above 32K size
then currently i am truncating the LONG RAW data if it exceeds 32k using function.

I need to convert whole LONG RAW data( atleast upto 64k) to CLOB(CHAR).
Cud any one guide me the way to solve this?

Shilpa

Kamal Kishore

Posts: 7,353
Registered: 09/19/99

Re: LONG RAW to CLOB conversion --- LONG RAW exceeding 32K SIZE for some col.
Posted: 2005-10-26 上午5:34

in response to: Shilpa

what you can try is to convert the long raw into a BLOB and then drive your conversion from this BLOB to the final destination CLOB. You can use a temp table to temporarily convert the long raw to a BLOB to be able to use DBMS_LOB package on it to get the contents. Others can suggest better alternatives.

SQL> create table long_raw(lng long raw) ; /* This is your current data table */



Table created.



SQL> create global temporary table temp_data(lob BLOB) ; /* temp table to convert long raw to BLOB */



Table created.



SQL> insert into long_raw values (utl_raw.cast_to_raw('This is pure text')) ; /* insert some data to the original table */



1 row created.



SQL> insert into temp_data select to_lob(lng) from long_raw ; /* transfer the data into the temp table converting it to BLOB */



1 row created.



SQL> /* now use temp_data table to qwuery your data and use DBMS_LOB to get the contents */

1122

Posts: 189
Registered: 03/16/00

Re: LONG RAW to CLOB conversion --- LONG RAW exceeding 32K SIZE for some c
Posted: 2005-10-26 上午6:58

in response to: Shilpa

I would go with something very similar to what Kamal suggested.

1. Insert LONG RAW into temp table with BLOB using TO_LOB.

2. Convert BLOB to CLOB using this function.
http://forums.oracle.com/forums/thread.jspa?forumID=75&messageID=559749&threadID=204993&q=#559749

Shilpa

Posts: 21
Registered: 09/16/05

Re: LONG RAW to CLOB conversion --- LONG RAW exceeding 32K SIZE for some col.
Posted: 2005-11-16 上午6:14

in response to: Shilpa

But this handles only 32K of data. Is there any way to split up the long raw into two? So that in first i read 32k chunk data & in the second another 32k

Kamal Kishore

Posts: 7,353
Registered: 09/19/99

Re: LONG RAW to CLOB conversion --- LONG RAW exceeding 32K SIZE for some col.
Posted: 2005-11-16 上午8:40

in response to: Shilpa

But this handles only 32K of data.

Which part of the process outlined in above responses are you referring to?

The TO_LOB should convert the entire RAW or LONG RAW. The other conversion function utilizes DBMS_LOB pacakge so should be able to handle the entire LOB.

Are you still having issues with data > 32K with this approach?

Shilpa

Posts: 21
Registered: 09/16/05

Re: LONG RAW to CLOB conversion --- LONG RAW exceeding 32K SIZE for some col.
Posted: 2005-11-17 上午3:33

in response to: Shilpa

Kamal,
My yesterdays reply was in reference to user user112. I can not apply ur approach. I have to handle it through SP as i am calling it through informatica(ETL tool) and also my source & target data base are different. I dont have access link to connect between two databases. I load the Source LONG RAW data in temporary target table using informatica. Then appending the target table with this data by first converting it to char using SP. Using Sp i am able to convert only 32k of long raw data to char. But what to do with the remaining chunk?. I have only two source rows which excceding this capacity. My target DB table column datatype is CLOB.

Target column has some description up to 1000 char. I have to append it with some remark field( 500 char) and then with 64k of converted long raw data based one some condition .

I cannot use direct insert statement

Can u suggest me a way to do this?
Even if i am able to split the long raw in two chunks that will also do for me.

Thanks in advance!

Shilpa

user472968

Posts: 1
Registered: 12/12/05

Re: LONG RAW to CLOB conversion --- LONG RAW exceeding 32K SIZE for some col.
Posted: 2005-12-12 上午11:56

in response to: Shilpa

Can u give me an idea about the sp to convert blob to clob

user590520

Posts: 1
Registered: 08/17/07

Re: LONG RAW to CLOB conversion --- LONG RAW exceeding 32K SIZE for some col.
Posted: 2007-8-17 上午8:01

in response to: Shilpa

A Example, the field CONTENTHTML is LOW RAW.

CREATE OR REPLACE PROCEDURE Replace_Text_From_BLOB (
numReg IN INTEGER,
replaceStr IN VARCHAR2,
replaceWith IN VARCHAR2) IS

vBuffer VARCHAR2 (32767);
l_amount BINARY_INTEGER := 32767;
l_pos PLS_INTEGER := 1;
l_clob_len PLS_INTEGER;
newClob CLOB := EMPTY_CLOB;
srcClob CLOB;
vBuffer1 VARCHAR2 (32767);
cont PLS_INTEGER := 1;

pos PLS_INTEGER := 1;
buffer LONG RAW( 32767 );

CURSOR c_tempBlob IS
SELECT CONTENTCODE, NEWSCODE, CONTENTTYPECODE,
CONTENTCREATIONDATE, LOBCOL, CONTENTRSSURL,
CONTENTPAGENUMBER
FROM ADMCMS.TEMP_BLOB ORDER BY CONTENTCODE;

BEGIN

DELETE ADMCMS.TEMP_T_CONTENT;

DELETE ADMCMS.TEMP_BLOB;

INSERT INTO TEMP_BLOB SELECT CONTENTCODE, NEWSCODE, CONTENTTYPECODE,CONTENTCREATIONDATE,TO_LOB( CONTENTHTML) LOBCOL ,CONTENTRSSURL,
CONTENTPAGENUMBER FROM T_CONTENT;

FOR tmp IN c_tempBlob LOOP

-- initalize the new clob
newClob:=null;
dbms_lob.createtemporary(newClob,TRUE);

l_clob_len := dbms_lob.getlength(tmp.LOBCOL);
vBuffer1 :=UTL_RAW.CAST_TO_VARCHAR2(DBMS_LOB.SUBSTR(tmp.LOBCOL, l_clob_len, 1));

srcClob := TO_CLOB(vBuffer1);
l_pos := 1;
--Recorre el texto y lo modifica
WHILE l_pos < l_clob_len
LOOP
dbms_lob.read(srcClob, l_amount, l_pos, vBuffer);

IF vBuffer IS NOT NULL THEN
-- reemplaza el texto
vBuffer := replace(vBuffer, replaceStr, replaceWith);
-- escribe al new clob
dbms_lob.writeappend(newClob, LENGTH(vBuffer), vBuffer);
END IF;
l_pos := l_pos + l_amount;
END LOOP;

buffer := UTL_RAW.CAST_TO_RAW (newClob);

INSERT INTO ADMCMS.TEMP_T_CONTENT (
CONTENTCODE, NEWSCODE, CONTENTTYPECODE,
CONTENTCREATIONDATE, CONTENTHTML, CONTENTRSSURL,
CONTENTPAGENUMBER)
VALUES (tmp.CONTENTCODE ,tmp.NEWSCODE ,tmp.CONTENTTYPECODE ,tmp.CONTENTCREATIONDATE
, buffer ,tmp.CONTENTRSSURL ,tmp.CONTENTPAGENUMBER );

COMMIT;

cont := cont + 1;

IF cont> numReg THEN
EXIT;
END IF;

END LOOP;

EXCEPTION
WHEN NO_DATA_FOUND THEN
NULL;
WHEN OTHERS THEN
-- Consider logging the error and then re-raise
RAISE;
END Replace_Text_From_BLOB;
/

Chaitanya.S.S.K

Posts: 1,252
Registered: 12/28/05

Re: LONG RAW to CLOB conversion --- LONG RAW exceeding 32K SIZE for some c
Posted: 2007-8-17 上午8:04

in response to: user590520

It was Nov 17, 2005 when the OP last replied and you are posting a reply now?

user2244480

Posts: 1
Registered: 12/31/08

Re: LONG RAW to CLOB conversion --- LONG RAW exceeding 32K SIZE for some c
Posted: 2008-12-31 上午6:58

in response to: Chaitanya.S.S.K

This is useful to me, so what the post is much later, he just worked on it recently, n'est c pas?

Pages: 1

Back to Thread List

Legend
	Guru : 2500 - 1000000 pts
	Expert : 1000 - 2499 pts
	Pro : 500 - 999 pts
	Journeyman : 200 - 499 pts
	Newbie : 0 - 199 pts
	Oracle ACE Director
	Oracle ACE Member
	Oracle Employee ACE
	Helpful Answer (5 pts)
	Correct Answer (10 pts)

youcunzai

关注

1
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
LONG RAW to CLOB conversion [to_lob]

http://forums.oracle.com/forums/thread.jspa?threadID=334220 [to_lob] Forum Home » Database » SQL and PL/SQL Thread: LONG RAW to CLOB conversion ---
复制链接

扫一扫