Home » SQL & PL/SQL » SQL & PL/SQL » help with clob output
help with clob output [message #245223] Fri, 15 June 2007 09:45 Go to next message
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 #247480 is a reply to message #245223] Tue, 26 June 2007 01:29 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
Can't you write in chunks of 32k?

MHE
Re: help with clob output [message #248347 is a reply to message #245223] Thu, 28 June 2007 13:02 Go to previous message
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;
/


Previous Topic: running sql loader from pl/sql
Next Topic: Does this exist in sql
Goto Forum:
  


Current Time: Thu Dec 08 12:25:37 CST 2016

Total time taken to generate the page: 0.09757 seconds