ORA-06502: PL/SQL: numeric or value error

[me_lucky]

Hi All,

I have a procedure which uses "utl_http" to send PDF file as E-mail which is generated on Oracle Reports server.

the procrdure is creted successfully. I have scheduled a job to execute this procedure on perticular time.
When i run the job 1st time it fails with error and 2nd time it executes successfully. It ha;ppens for every new login for the first time.

ORA-06502: PL/SQL: numeric or value error
ORA-06512: at "SYS.OWA_UTIL", line 356
ORA-06512: at "SYS.HTP", line 1368
ORA-06512: at "SYS.HTP", line 1443
ORA-06512: at "SYS.HTP", line 1735
ORA-06512: at "AUTO_REP", line 28
ORA-06512: at line 2


Code:

v_blob blob;
v_url varchar2(2000);
v_pieces utl_http.html_pieces;
v_buffer raw(2000);
v_buffer_size integer := 2000;
begin
for v_rec in auto_cur loop
v_url := 'http://hostname/reports/rwservlet?'||v_rec.report_script ||'&desname='||v_rec.to_email_id||'&cc='||v_rec.cc_email_id||'&bcc='||v_rec.bcc_email_id;
htp.p('content-disposition: filename='||v_rec.report_nm);
owa_util.http_header_close;

utl_http.set_transfer_timeout(3600);
v_pieces := utl_http.request_pieces(v_url,64000);

v_blob := empty_blob();
dbms_lob.createtemporary(v_blob,true);

for i in 1 .. v_pieces.count loop
v_buffer := utl_raw.cast_to_raw(v_pieces(i));
v_buffer_size := utl_raw.length(v_buffer);
if v_buffer_size > 0 then
dbms_lob.writeappend(v_blob, v_buffer_size, v_buffer);
end if;
end loop;
wpg_docload.download_file(v_blob);
end loop;
end;

----
Pl help me resolving the issue.

Thanks & Regards,
Lucky.

[Tim...]

Hi.

I can't run the code to test it and there is nothing visually wrong with it. My suggestions are:

1) Add some tracing to the code and display the value of all parameters and variables so you can see what is going on.

2) If you want me to actually try and debug the code, post a working example so I can actually run it.

Cheers

Tim...

[me_lucky]

Hi Tim,

Thanks for the quick reply.

1) I dont see any issue and also there is not much login in this. and more over am able to run this explicitly with out any issue, problem is only when running through Oracle Job and that to 1st time it fails second time it runs fine.

2) I can provide you the complete code, but it run through oracle application server, to generate PDF file we are using oracle reports server. (v_url := 'http://hostname/reports/rwservlet?'||v_rec.report_script ||'&desname='||v_rec.to_email_id||'&cc='||v_rec.cc_email_id||'&bcc='||v_rec.bcc_email_id;)

Let me know if can provide the code and oracle report.

Thanks & Regards,
Lucky

[Tim...]

Hi.

So you just want the result of a report loaded into a BLOB, so you can do something with it. I would focus on making this stable first. You can do this using a HTTPURITYPE.

l_blob := HTTPURITYPE.createuri('URL to your report').getblob();

See the examples here:

http://www.oracle-base.com/articles/mis ... ttpuritype

Cheers

Tim...

[me_lucky]

Hi Tim,

thanks again for the reply.

I'l try as said and update you with the result.

Thanks,
Lucky

[Tim...]

[me_lucky]

Hi Tim,

I tried as said, but am not able to do.


The URL formed will be like "http://hostname/reports/rwservlet?report=/drive/report.rep&userid=test_usr/test_usr@test&desformat=pdf&destype=mail&cd_g=CIN&USER_ID_P=SERVER&from=fromme@gmail.com&to=toyou@gmail.com";

and when am executing the procedure to load the pdf file in table, it is updating like
"http://hostname/reports/rwservlet?report=/drive/report.rep=test_usr/test_usr@test=pdf=mail=CIN=SERVER=fromme@gmail.com=toyou@gmail@gmail.com"

It is ignoring the "&" symbol, pl tell me how to ignore the & symbol while inserting the data.

Thanks & Regards,
Lucky

[Tim...]

Hi.

Have you traced the URL you are passing in to make sure the & is there before you call the report?

You can concatenate in the CHR(38) function to replace the &.

Cheers

Tim...