This forum is currently locked. You can't register or post questions at this time. (read more)

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

All posts relating to Oracle PL/SQL development.

Moderator: Tim...

me_lucky
Senior Member
Posts: 183
Joined: Tue Jun 02, 2009 11:40 am

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

Postby me_lucky » Thu Nov 15, 2012 9:18 am

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...
Site Admin
Posts: 18437
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK
Contact:

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

Postby Tim... » Thu Nov 15, 2012 9:38 am

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...
Tim...
Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: http://www.oracle-base.com
My blog: http://www.oracle-base.com/blog

me_lucky
Senior Member
Posts: 183
Joined: Tue Jun 02, 2009 11:40 am

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

Postby me_lucky » Thu Nov 15, 2012 10:21 am

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...
Site Admin
Posts: 18437
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK
Contact:

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

Postby Tim... » Thu Nov 15, 2012 11:30 am

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...
Tim...
Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: http://www.oracle-base.com
My blog: http://www.oracle-base.com/blog

me_lucky
Senior Member
Posts: 183
Joined: Tue Jun 02, 2009 11:40 am

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

Postby me_lucky » Thu Nov 15, 2012 12:49 pm

Hi Tim,

thanks again for the reply.

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

Thanks,
Lucky :-)

Tim...
Site Admin
Posts: 18437
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK
Contact:

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

Postby Tim... » Thu Nov 15, 2012 2:51 pm

:)
Tim...
Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: http://www.oracle-base.com
My blog: http://www.oracle-base.com/blog

me_lucky
Senior Member
Posts: 183
Joined: Tue Jun 02, 2009 11:40 am

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

Postby me_lucky » Fri Nov 16, 2012 10:17 am

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...
Site Admin
Posts: 18437
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK
Contact:

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

Postby Tim... » Fri Nov 16, 2012 10:30 am

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...
Tim...
Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: http://www.oracle-base.com
My blog: http://www.oracle-base.com/blog


Return to “Oracle SQL and PL/SQL Development”

Who is online

Users browsing this forum: No registered users and 7 guests

cron