| help with clob output [message #245223] |
Fri, 15 June 2007 09:45  |
marks20101
Messages: 74
Registered: May 2005
|
Member |
|
|
This module places three clobs into one file. Clobs will always be the same. My problem is the vxml clob which is 85kb is to large. Ora error;
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error
ORA-06512: at "SCOTT.EXTRACT_FILE3", line 58
ORA-06512: at line 1
How can I process the vxml clob to output correctly? This does work with a test vxml of 5kb. But the sizes will be around 80 to 85kb.
CREATE OR REPLACE PROCEDURE extract_file3(p_month IN varchar2, p_day IN varchar2) IS
vclobtop CLOB;
vclobbot CLOB;
vxml CLOB;
l_output utl_file.file_type;
BEGIN
-- define output directory
l_output := utl_file.fopen('IO', p_month||'_'||p_day||'.wme','w', 32760);
-- select clob into variables
SELECT manual_cl
INTO vclobtop
FROM catalog
WHERE item_id = 1;
SELECT manual_cl
INTO vclobbot
FROM catalog
WHERE item_id = 2;
SELECT manual_cl
INTO vxml
FROM catalog
WHERE item_id = 3;
--output to .wme file
utl_file.put_line(l_output,vclobtop);
utl_file.put_line(l_output,vxml);
utl_file.put_line(l_output,vclobbot);
utl_file.fflush(l_output);
utl_file.fclose(l_output);
END;
/
Also, I know that a CLOB is for text and BLOB is for Binary, but I'm not sure on the size attributes. I just found this, "a CLOB can be much larger than 32k but a UTL_FILE line can only be 32k. Unless you're writing binary output." So I see why it failed, but not sure how to resolve using the UTL_FILE.put_raw statement.
Thanks,
Mark S.
[Updated on: Fri, 15 June 2007 10:47] Report message to a moderator |
|
|
|
|
|
| Re: help with clob output [message #248347 is a reply to message #245223] |
Thu, 28 June 2007 13:02  |
marks20101
Messages: 74
Registered: May 2005
|
Member |
|
|
New code added to attempt writing in chunks is in bold and the line that is giving me the error is in bold and Italic.
Yes, that was my next move, but I keep getting the following error:
ORA-06502: PL/SQL: numeric or value error: hex to raw conversion error
vclobtop CLOB;
vclobbot CLOB;
vxml CLOB;
vstart NUMBER := 1;
bytelen NUMBER := 32000;
lenxml NUMBER;
my_vr RAW(32000);
x NUMBER;
l_output utl_file.file_type;
BEGIN
-- define output directory
l_output := utl_file.fopen('IO', p_month||'_'||p_day||'.wme','w', 32760);
vstart := 1;
bytelen := 32000;
-- get length of clob
SELECT dbms_lob.getlength(manual_cl)
INTO lenxml
FROM catalog
WHERE item_id = 3;
-- save clob length
x := lenxml;
-- select clob into variables
SELECT manual_cl
INTO vclobtop
FROM catalog
WHERE item_id = 1;
SELECT manual_cl
INTO vclobbot
FROM catalog
WHERE item_id = 2;
SELECT manual_cl
INTO vxml
FROM catalog
WHERE item_id = 3;
utl_file.put_line(l_output,vclobtop);
utl_file.put_line(l_output,vclobbot);
vstart := 1;
WHILE vstart < lenxml AND bytelen > 0
LOOP
dbms_lob.read(vxml,bytelen,vstart,my_vr);
utl_file.put_line(l_output,my_vr);
utl_file.fflush(l_output);
-- set the start position for the next cut
vstart := vstart + bytelen;
-- set the end position if less than 32000 bytes
x := x - vstart;
IF x < 32000 then
bytelen := x;
END IF;
END LOOP;
utl_file.fclose(l_output);
END;
/
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
