Home » SQL & PL/SQL » SQL & PL/SQL » Converting Blob to Clob
Converting Blob to Clob [message #272056] Wed, 03 October 2007 20:45 Go to next message
amereddy
Messages: 11
Registered: May 2006
Junior Member
Hi! I am converting a BLOB to CLOB and calling http_post by passing the clob variable. it is throwing numeric value error.

l_data clob;
l_blob blob;
dest_offset NUMBER := 1;
src_offset NUMBER := 1;
amount INTEGER := dbms_lob.lobmaxsize;
blob_csid NUMBER := dbms_lob.default_csid;
lang_ctx INTEGER := dbms_lob.default_lang_ctx;
warning INTEGER;


DBMS_LOB.CREATETEMPORARY(lob_loc=>l_clob, cache=>TRUE, dur=>dbms_lob.SESSION);
DBMS_LOB.CONVERTTOCLOB(configXml,l_blob,amount,dest_offset,src_offset,blob_csid,lang_ctx,warning);

http_post( p_url_in => l_url, p_data_in => l_data);

I selected a xml file that is stored as a BLOB into l_blob.
The xml file is close to 4 mb.

there is no conversion error. I am using http_post passing the clob, I keep getting
"ORA-06502: PL/SQL: numeric or value error"

I tried to get the length of the clob variable. It is not even close to the size of the xml file. Not sure what the issue is.

Can anyone suggest ?

Regards
Re: Converting Blob to Clob [message #272058 is a reply to message #272056] Wed, 03 October 2007 21:26 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
OK, I guess I need to be educated.
>http_post( p_url_in => l_url, p_data_in => l_data);
Please provide a URL showing where http_post is valid within a PL/SQL procedure.

By the way, where is "l_url" defined in your post?
Re: Converting Blob to Clob [message #272335 is a reply to message #272058] Thu, 04 October 2007 11:07 Go to previous messageGo to next message
amereddy
Messages: 11
Registered: May 2006
Junior Member
I was trying to use the http_post package that I found on the web.
http://awads.net/wp/2005/11/30/http-post-from-inside-oracle/

Since it takes CLOB as input, I was trying to convert. After converting the BLOB into CLOB, I printed the CLOB variable, it only had half the xml that the BLOB contained.

Thanks
Re: Converting Blob to Clob [message #272472 is a reply to message #272335] Fri, 05 October 2007 03:20 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
There's something funny about your code.
You're sticking the converted BLOB into a CLOB called ConfigXML, but this is neither defined, nor passed to http_post.

Would you by any chance have posted a hand crafted subset of your actual code, introducing a few errors along the way, and possibly hiding the actual cause of the problem?

Can you produce a small piece of code that produces your problem, and then cut and paste that here from SQL*Plus.

Failing that, add some code after the CONVERTOCLOB to check the size and contents of the new CLOB.
Re: Converting Blob to Clob [message #272557 is a reply to message #272472] Fri, 05 October 2007 09:18 Go to previous messageGo to next message
amereddy
Messages: 11
Registered: May 2006
Junior Member
Thanks for the response. Here is what I am calling the http_post.

http://awads.net/wp/2005/11/30/http-post-from-inside-oracle/


Regards


declare
l_url varchar2 (200) := 'http://www.example.com/post.cfm';
l_data clob;
l_blob blob;
l_query varchar2(32767);
clob_len binary_integer := 32767;

dest_offset NUMBER := 1;
src_offset NUMBER := 1;
amount INTEGER := dbms_lob.lobmaxsize;
blob_csid NUMBER := dbms_lob.default_csid;
lang_ctx INTEGER := dbms_lob.default_lang_ctx;
warning INTEGER;
begin
SELECT fl.file_data
INTO l_blob
from fnd_attached_documents fad,
fnd_documents_tl fdt,
fnd_documents fd,
fnd_lobs fl,
aso_quote_headers_all aqha
where aqha.quote_header_id = 322
and aqha.quote_header_id = fad.pk1_value
and fad.entity_name = 'ASO_QUOTE_HEADERS_ALL'
and fad.document_id = fdt.document_id
and fdt.LANGUAGE = 'US'
and fdt.document_id = fd.document_id
and fdt.media_id = fl.file_id;

dbms_output.put_line('blob length is : ' || to_char( DBMS_LOB.getlength(l_blob)));
DBMS_LOB.CREATETEMPORARY(lob_loc=>l_data, cache=>TRUE, dur=>dbms_lob.SESSION);
DBMS_LOB.CONVERTTOCLOB(l_data,l_blob,amount,dest_offset,src_offset,blob_csid,lang_ctx,warning);

--dbms_lob.read(l_data, clob_len,1, l_query);
--dbms_output.put_line(l_query);

util_pkg.http_post( p_url_in => l_url, p_data_in => l_data );
end;
Re: Converting Blob to Clob [message #272559 is a reply to message #272557] Fri, 05 October 2007 09:35 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Ok.
Now, can you post the entire error stack, so we can see what module and at what line the error happens.
Re: Converting Blob to Clob [message #272561 is a reply to message #272559] Fri, 05 October 2007 09:42 Go to previous messageGo to next message
amereddy
Messages: 11
Registered: May 2006
Junior Member
Here is the error stack that I got.
Regards


Error starting at line 1 in command:
declare
l_url varchar2 (200) := 'http://www.example.com/post.cfm';
l_data clob;
l_blob blob;
l_query varchar2(32767);
clob_len binary_integer := 32767;

dest_offset NUMBER := 1;
src_offset NUMBER := 1;
amount INTEGER := dbms_lob.lobmaxsize;
blob_csid NUMBER := dbms_lob.default_csid;
lang_ctx INTEGER := dbms_lob.default_lang_ctx;
warning INTEGER;
begin
SELECT fl.file_data
INTO l_blob
from fnd_attached_documents fad,
fnd_documents_tl fdt,
fnd_documents fd,
fnd_lobs fl,
aso_quote_headers_all aqha
where aqha.quote_header_id = 322
and aqha.quote_header_id = fad.pk1_value
and fad.entity_name = 'ASO_QUOTE_HEADERS_ALL'
and fad.document_id = fdt.document_id
and fdt.LANGUAGE = 'US'
and fdt.document_id = fd.document_id
and fdt.media_id = fl.file_id;

dbms_output.put_line('blob length is : ' || to_char( DBMS_LOB.getlength(l_blob)));
DBMS_LOB.CREATETEMPORARY(lob_loc=>l_data, cache=>TRUE, dur=>dbms_lob.SESSION);
DBMS_LOB.CONVERTTOCLOB(l_data,l_blob,amount,dest_offset,src_offset,blob_csid,lang_ctx,warning);

--dbms_lob.read(l_data, clob_len,1, l_query);
--dbms_output.put_line(l_query);

util_pkg.http_post( p_url_in => l_url, p_data_in => l_data );
end;
Error report:
ORA-06502: PL/SQL: numeric or value error
ORA-06512: at "UTIL_PKG", line 116
ORA-06512: at line 37
06502. 00000 - "PL/SQL: numeric or value error%s"
*Cause:
*Action:
Re: Converting Blob to Clob [message #272563 is a reply to message #272056] Fri, 05 October 2007 09:46 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
It is too bad for us that you do NOT use <code tags>, SQL*Plus & CUT and PASTE, so we'd know what is at line 116 and line 37.

Simply put, there is a bug in util_pkg.http_post that you need to fix.
Re: Converting Blob to Clob [message #272565 is a reply to message #272561] Fri, 05 October 2007 09:54 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Hmmm.

The UTL_PKG you pointed us to doesn't get up to line 116 - the package body has only got 104 lines, so I guess you've made some changes to that.

Care to post the relevant area of UTL_PKG, because it looks like that's where the error is.
Re: Converting Blob to Clob [message #272572 is a reply to message #272565] Fri, 05 October 2007 10:10 Go to previous messageGo to next message
amereddy
Messages: 11
Registered: May 2006
Junior Member
My apoligies. I added few lines of code for debug. The error is in the exception section. Line 116 is the dbms_output line.

exception
when others then
dbms_output.put_line (sqlerrm);
raise;

If I use a simple query like

l_data clob;

l_query := '
select object_name, created, status
from all_objects
where rownum <=2
';
l_data := dbms_xmlgen.getxml(l_query);
and call http_post is does not error out.

util_pkg.http_post(
p_url_in => l_url, p_data_in => l_data);


Not sure why converting a blob to clob and using it as input is causing it to fail.

Regards

Re: Converting Blob to Clob [message #272573 is a reply to message #272056] Fri, 05 October 2007 10:13 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
It is too bad for us that you do NOT use <code tags>, SQL*Plus & CUT and PASTE, so we'd know what is at line 116 and line 37.

Simply put, there is a bug in util_pkg.http_post that you need to fix.
Re: Converting Blob to Clob [message #272601 is a reply to message #272572] Fri, 05 October 2007 12:49 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
amereddy wrote on Fri, 05 October 2007 17:10
My apoligies. I added few lines of code for debug. The error is in the exception section. Line 116 is the dbms_output line.

exception
when others then
dbms_output.put_line (sqlerrm);
raise;



Get rid of when others!!!
Now you hide the place the error occurs. The error will LOOK to come from the line that does the raise.

If you are on 9i, then dbms_output.put_line only accepts a string with limited length.

[Updated on: Fri, 05 October 2007 12:51]

Report message to a moderator

Re: Converting Blob to Clob [message #272617 is a reply to message #272601] Fri, 05 October 2007 13:40 Go to previous messageGo to next message
amereddy
Messages: 11
Registered: May 2006
Junior Member
We are using Oracle 10g database.I commented out. It is coming from "utl_http.write_text(l_http_req, p_data_in);"
The syntax looks correct. p_data_in is CLOB. I even hardcoded content-length but it still errored out.

l_http_req := Utl_Http.begin_request (url => p_url_in, method => 'POST');

-- Set the HTTP request headers
utl_http.set_header(l_http_req, 'User-Agent', 'Mozilla/4.0');
utl_http.set_header(l_http_req, 'content-type', p_data_type);
utl_http.set_header(l_http_req, 'content-length', length
(p_data_in));

-- Write the data to the body of the HTTP request
utl_http.write_text(l_http_req, p_data_in);
Re: Converting Blob to Clob [message #272619 is a reply to message #272056] Fri, 05 October 2007 13:44 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
>but it still errored out.
My car errored out.
Please tell me how to fix my car.

Since you are unwilling to provide what has been repeatedly requested, now You're On Your Own (YOYO)!
Re: Converting Blob to Clob [message #272622 is a reply to message #272619] Fri, 05 October 2007 13:49 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
moving it to a more appropriate place..
Re: Converting Blob to Clob [message #272870 is a reply to message #272617] Mon, 08 October 2007 01:47 Go to previous message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
I think we're all in for a bit of an anticlimax here.

From the documentation:
Syntax

UTL_HTTP.WRITE_TEXT(
   r     IN OUT NOCOPY REQ,
   data  IN            VARCHAR2 CHARACTER SET ANY_CS);


WRITE_TEXT takes a varchar2 as a parameter, so it will accept anything up to 32767 bytes.
You will be passing in more than that amount, which will (I strongly suspect) be what is giving you the error.

all you need to do is
a) Read the documentation before you use bits of Oracle you haven't used before
b) Loop through the length of your Clob, and write it out to the UTL_HTTP package in 32767 byte chunks.
Previous Topic: Top - N queries in oracle
Next Topic: How to fetch % symbol using like operator
Goto Forum:
  


Current Time: Thu Dec 08 16:43:33 CST 2016

Total time taken to generate the page: 0.16431 seconds